f



Is this possible? please help (MS access query of sql database)

I work for an organisation that uses a bespoke document imaging system, the
database of which is an MS sql server.
We have MS Access and already use it for some querying of the database.
The database comprises a large number of distinct cases, which are
differentiated by case reference numbers, in one field (table?) of the
database. Each of these cases may have many documents associated with it,
denoted by the reference number, and these documents may be "new", "pending"
or "complete" shown in another data field.
We need to know how many cases have work outstanding on them.
Our problem is that our bespoke software will only count the number of
documents of each status, and not the cases.

Is it possible to design an MS Access query which will count the number of
different reference numbers which have any "new" documents associated, but
wont count each case more than once?

I am reasonably computer-savvy, I just don't know Access or SQL..
If I know it is possible, I don't mind putting in the effort to find out
how. I just don't want to waste time barking up the wrong tree ;-)

Of course any advice about how this would be achieved, such as pointers to
the right parts of the MS Access helpfiles, or to relevant websites would be
greatly appreciated. Some quick code would be even better...
Also, if there is any 3rd-party software which could easily do this, I need
help discovering it...I have looked long and hard, but don't know enough
about what I am looking for.
Yours in hope..
-- 
anthonyberet
Please reply in the groups, as my Usenet email address is not working at the
moment.



0
witfb001 (5)
10/18/2003 12:03:05 AM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

5 Replies
908 Views

Similar Articles

[PageSpeed] 6

anthonyberet wrote:
> I work for an organisation that uses a bespoke document imaging
> system, the database of which is an MS sql server.
> We have MS Access and already use it for some querying of the
> database.
> The database comprises a large number of distinct cases, which are
> differentiated by case reference numbers, in one field (table?) of the
> database. Each of these cases may have many documents associated with
> it, denoted by the reference number, and these documents may be
> "new", "pending" or "complete" shown in another data field.
> We need to know how many cases have work outstanding on them.
> Our problem is that our bespoke software will only count the number of
> documents of each status, and not the cases.
>
> Is it possible to design an MS Access query which will count the
> number of different reference numbers which have any "new" documents
> associated, but wont count each case more than once?
>
> I am reasonably computer-savvy, I just don't know Access or SQL..
> If I know it is possible, I don't mind putting in the effort to find
> out how. I just don't want to waste time barking up the wrong tree ;-)
>
> Of course any advice about how this would be achieved, such as
> pointers to the right parts of the MS Access helpfiles, or to
> relevant websites would be greatly appreciated. Some quick code would
> be even better...
> Also, if there is any 3rd-party software which could easily do this,
> I need help discovering it...I have looked long and hard, but don't
> know enough about what I am looking for.
> Yours in hope..

You'd probably be better off in an MS Access group - try
microsoft.public.access


0
me91 (285)
10/18/2003 10:09:40 AM
Gordon Burgess-Parker wrote:
> anthonyberet wrote:
>> I work for an organisation that uses a bespoke document imaging
>> system, the database of which is an MS sql server.
>> We have MS Access and already use it for some querying of the
>> database.
>> The database comprises a large number of distinct cases, which are
>> differentiated by case reference numbers, in one field (table?) of
>> the database. Each of these cases may have many documents associated
>> with it, denoted by the reference number, and these documents may be
>> "new", "pending" or "complete" shown in another data field.
>> We need to know how many cases have work outstanding on them.
>> Our problem is that our bespoke software will only count the number
>> of documents of each status, and not the cases.
>>
>> Is it possible to design an MS Access query which will count the
>> number of different reference numbers which have any "new" documents
>> associated, but wont count each case more than once?
>>
>> I am reasonably computer-savvy, I just don't know Access or SQL..
>> If I know it is possible, I don't mind putting in the effort to find
>> out how. I just don't want to waste time barking up the wrong tree
>> ;-)
>>
>> Of course any advice about how this would be achieved, such as
>> pointers to the right parts of the MS Access helpfiles, or to
>> relevant websites would be greatly appreciated. Some quick code would
>> be even better...
>> Also, if there is any 3rd-party software which could easily do this,
>> I need help discovering it...I have looked long and hard, but don't
>> know enough about what I am looking for.
>> Yours in hope..
>
> You'd probably be better off in an MS Access group - try
> microsoft.public.access

And I didn't see ALL those crossposts..... doh!


0
me91 (285)
10/18/2003 10:10:24 AM
On Sat, 18 Oct 2003 01:03:05 +0100, "anthonyberet"
<witfb001@sneakemail.com> wrote:

>I work for an organisation that uses a bespoke document imaging system, the
>database of which is an MS sql server.
>We have MS Access and already use it for some querying of the database.
>The database comprises a large number of distinct cases, which are
>differentiated by case reference numbers, in one field (table?) of the
>database. Each of these cases may have many documents associated with it,
>denoted by the reference number, and these documents may be "new", "pending"
>or "complete" shown in another data field.
>We need to know how many cases have work outstanding on them.
>Our problem is that our bespoke software will only count the number of
>documents of each status, and not the cases.
>
>Is it possible to design an MS Access query which will count the number of
>different reference numbers which have any "new" documents associated, but
>wont count each case more than once?
>
>I am reasonably computer-savvy, I just don't know Access or SQL..
>If I know it is possible, I don't mind putting in the effort to find out
>how. I just don't want to waste time barking up the wrong tree ;-)
>
>Of course any advice about how this would be achieved, such as pointers to
>the right parts of the MS Access helpfiles, or to relevant websites would be
>greatly appreciated. Some quick code would be even better...
>Also, if there is any 3rd-party software which could easily do this, I need
>help discovering it...I have looked long and hard, but don't know enough
>about what I am looking for.
>Yours in hope..

this is very difficult without any idea of the tables involved, but
lets try;

Table: Cases
(caseNumber integer) *PK

Table: Documents
(docNumber integer,
caseNumber integer, *FK
status varchar(20))

SELECT COUNT(1) FROM Cases
WHERE caseNumber IN
(SELECT caseNumber 
FROM Documents WHERE status='New')

Of course if your tables don't look like this then you need another
approach....
0
lyndon3763 (31)
10/18/2003 7:08:54 PM
Lyndon Hills wrote:
> On Sat, 18 Oct 2003 01:03:05 +0100, "anthonyberet"
> <witfb001@sneakemail.com> wrote:
>
>> I work for an organisation that uses a bespoke document imaging
>> system, the database of which is an MS sql server.
>> We have MS Access and already use it for some querying of the
>> database. The database comprises a large number of distinct cases,
>> which are differentiated by case reference numbers, in one field
>> (table?) of the database. Each of these cases may have many
>> documents associated with it, denoted by the reference number, and
>> these documents may be "new", "pending" or "complete" shown in
>> another data field. We need to know how many cases have work
>> outstanding on them. Our problem is that our bespoke software will
>> only count the number of documents of each status, and not the cases.
>>
>> Is it possible to design an MS Access query which will count the
>> number of different reference numbers which have any "new" documents
>> associated, but wont count each case more than once?
>>
>> I am reasonably computer-savvy, I just don't know Access or SQL..
>> If I know it is possible, I don't mind putting in the effort to find
>> out how. I just don't want to waste time barking up the wrong tree
>> ;-)
>>
>> Of course any advice about how this would be achieved, such as
>> pointers to the right parts of the MS Access helpfiles, or to
>> relevant websites would be greatly appreciated. Some quick code
>> would be even better... Also, if there is any 3rd-party software
>> which could easily do this, I need help discovering it...I have
>> looked long and hard, but don't know enough about what I am looking
>> for. Yours in hope..
>
> this is very difficult without any idea of the tables involved, but
> lets try;
>
> Table: Cases
> (caseNumber integer) *PK
>
> Table: Documents
> (docNumber integer,
> caseNumber integer, *FK
> status varchar(20))
>
> SELECT COUNT(1) FROM Cases
> WHERE caseNumber IN
> (SELECT caseNumber
> FROM Documents WHERE status='New')
>
> Of course if your tables don't look like this then you need another
> approach....

I think only 2 tables are relevant in the first instance - "reference" and
"status".
Can you rcommend a site where I can read about the functions of the
intructions you have posted?
In particular, the "SELECT COUNT(1) FROM Cases" bit looks very powerful.
However, is this SQL or is it bespoke code used by MS Access?
Thank you for your help.
-- 
Put "usenet" in the subject-line if you want to mail me, otherwise it will
bounce.
Do you use filesharing networks? If so, please visit my online poll:
http://vote.sparklit.com/web_poll.spark/780772
anthonyberet


0
witfb001 (5)
10/20/2003 10:33:41 PM
On Mon, 20 Oct 2003 23:33:41 +0100, "anthonyberet"
<witfb001@sneakemail.com> wrote:

<snip>
>> SELECT COUNT(1) FROM Cases
>> WHERE caseNumber IN
>> (SELECT caseNumber
>> FROM Documents WHERE status='New')
>>
>I think only 2 tables are relevant in the first instance - "reference" and
>"status".
>Can you rcommend a site where I can read about the functions of the
>intructions you have posted?
>In particular, the "SELECT COUNT(1) FROM Cases" bit looks very powerful.
>However, is this SQL or is it bespoke code used by MS Access?
>Thank you for your help.

I would google for sql tutorials. www.sqlcourse.com looks basic. Also
there should be some of this at least in the access help files. I
guess you do need to know what your looking for though. Above, all the
words in capitals are sql keywords, and they should be in the help
files. The IN is a subselect which could be replaced with EXISTS and a
slightly different syntax.

SELECT COUNT() FROM,  just counts the number of rows that meet the
conditions. It just returns one number, not the actual rows of data.
There are similar options like MAX, MIN AVERAGE which apply to number
columns. A quick word of warning if you plan to use them, be careful
of the case where the number column is null. Average in particular may
give wrong results.

SQL has relatively few keywords, although each of the big
manufacturers have added their own.
0
lyndon3763 (31)
10/21/2003 7:30:02 PM
Reply:

Similar Artilces:

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible? If I migrate the MS Access to SQL Server, would the queries, back-end VBA, macro, tables and forms be affected? Do I need to change the MS Access data type to SQL server supported data type? Which tool I can use to do the migration? Upsizing wizard or exporting the Access database and then importing it to the SQL server? Thanks in advance Cheers Bon This is somewhat easy. Basically you need to port or transfer your Access tables to a SQL DB and then link the Access DB/App. to the SQL tables on the SQL Server. If you keep the table names the same, the code etc shouldn't need altering, bu I would test it first. db55 wrote: > This is somewhat easy. > > Basically you need to port or transfer your Access tables to a SQL DB > and then link the Access DB/App. to the SQL tables on the SQL Server. > If you keep the table names the same, the code etc shouldn't need > altering, bu I would test it first. > If only it were that easy :-) Bon wrote: > Hello all > > Would it be possible to migrate the MS Access 2000 to MS SQL Server > 2000? > > My application is using MS Access 2000 as database and a...

Trouble when submitting sql-query to MS Access Database
Hi, I have some problems when trying to submit a simple insertion statement to a MS Access Database. The reading is fine, but when I want to execute an insertion statement using the following code: public void SQLCommand(String sql) throws SQLException { connection = DriverManager.getConnection(database); Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); connection.close(); } gives the following error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source) at org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:45) at org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:15) Could someone help me? kito Suggestion 1: The error is caused because you're referring to a table that does not exist. Try to run the query in a seperate query editor and verify that it does work. Also check that the string you pass as argument is correct, e.g. not concatenation that has destroyed the intended string. Suggestion 2: You probably need to add quotes in the executeQuery. Whenever you pass a string to a database try to inclose the statement with...

export sqlserver database to ms-access by sql script
Hi Is there anyway to export a sqlserver database to ms-access through a sql scipt only without using the sql enterprise manager. -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200601/1 ...

Create MS/ACCESS database app for non-ms/access users
Is it possible to create an MS/ACCESS database application and package it for users who do not have MS/ACCESS loaded in their PCs? I was told that this is possible, but I don't know what software is needed. Do you know? If so, please answer this post. Thanks. SueB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Per Sue Bricker: >Is it possible to create an MS/ACCESS database application and package >it for users who do not have MS/ACCESS loaded in their PCs? > >I was told that this is possible, but I don't know what software is >needed. Do you know? If so, please answer this post. Thanks. I think it comes down to semantics. "Licensed" vs "Loaded". No you can't create an app that will run on a PC with nothing loaded. Yes you can create an app that will run on a PC where nobody's paid the fee to run MS Access. You still have to install something on that PC, however. What it is is a freebie version of MS Access that only runs applications - doesn't allow anybody to develop an app. You develop the app on your PC, which has MS Access installed. Then somebody installs the freebie ("Runtime Access") on somebody's PC and they can run the app you developed without having to pay any money to anybody. -- PeteCresswell Sue, If Access isn't present, some version of it must be installed to run an Access database. VB.NET, ...

newbie needs guidance from mastuh on querying mysql database from ms access... please
i've already set up mysql server and msaccess wif myodbc driver (myodbc 3.51) on my computer and i've created a new "database" (it only links to the tables in mysql server) in ms access. The problem comes when i try to do a UNION query from ms access on 3 or more linked mysql tables, it prints me an error... any ideas? It works when i do a union query only on 2 tables but not on more. i've checked that the number of fields in the tables and the field properties of each table are the the same, but i still get that error. thanx for your time "angel duran" <angelduran2004@hotmail.com> wrote in message news:a4e32a26.0408181126.5ee648cc@posting.google.com... > i've already set up mysql server and msaccess wif myodbc driver > (myodbc 3.51) on my computer and i've created a new "database" (it > only links to the tables in mysql server) in ms access. The problem > comes when i try to do a UNION query from ms access on 3 or more > linked mysql tables, it prints me an error... any ideas? It works when > i do a union query only on 2 tables but not on more. i've checked > that the number of fields in the tables and the field properties of > each table are the the same, but i still get that error. thanx for > your time MySQL Server = You get what you pay for. MyODBC Driver = You get what you pay for. Find out what "free" is all about at: http://www.mysql.com/support/techsupport.ht...

MS Access Database Query
Hi, first of all I have to say I'am new to MS Access, I'm used to database systems like Oracle or MySQL. I created a simple database consisting of a single table and a form to fill in new entrys and search for existing entrys. But the search function that Access generates as I created the proper button is way to simple, I need a function that enables to search in several columns. I saw a few quite simple examples where this was done by DAO, but this doesn't work with a .mdb database, does it? (Access allways replies 'Unknown user datatype' once it gets to the line: 'Dim db As Database') Now, is it possible to generate a new query for .mdb file from a form, or at least with CurrentData Object, or not? And if it is possible with the CurrentData Object is it still a database request then or would it be faster to search through a text file? Regards, Bj�rn Keil Assuming you are running AK2 in the modules you need a reference to the Microsoft DAO 3.6 Object Library So open any VB module Goto Tools->References and select the Microsoft DAO 3.6 Object Library Phil "Bj?rn Keil" <abgrund@silberdrache.net> wrote in message news:e8ef0c96.0311210655.79a6192e@posting.google.com... > Hi, > first of all I have to say I'am new to MS Access, I'm used to database > systems like Oracle or MySQL. > I created a simple database consisting of a single table and a form to > fill in new entrys and search for existing entrys. Bu...

Problems with MOD-Function by accessing MS-Access Database via SQL in Delphi
Hello, the following problem: I use Delphi 6 to access a MS Access Database. In short, the Delphi Code looks like this: ADOConnection1.Open; ADOQuery1.Close; ADOQuery1.SQL.Text :=3D 'SELECT * FROM database1 WHERE value1=3D1 AND value2=3D ' + inttostr(array[3]); ADOQuery1.Open; Label5.Caption:=3DADOQuery1.Fiel=ADdByName('value4').AsString; Now I only want to select these rows, where the Integer value3 ends on 1=2E So I need the Modulo-Function. I found in the Internet these two functions: MOD(x,y) und x % y But I get an error if I want to use one. For example, If I change ADOQuery1.SQL.Text :=3D 'SELECT * FROM database1 WHERE value1=3D1 AND value2=3D ' + inttostr(array[3])+' AND MOD(value3,10) =3D 1; Delphi shows me a Syntax Error in this statement. Thx for help! Sorry, there is a ' missing in the code. But it has nothin to do with the problem: The first code runs perfectly, but If I insert AND MOD(20,10) = 0 there is the syntax error again. On 23 Aug 2005 11:51:15 -0700, sarah18web wrote: > Sorry, there is a ' missing in the code. But it has nothin to do with > the problem: > > The first code runs perfectly, but If I insert > AND MOD(20,10) = 0 there is the syntax error again. Don't know what it might have to do with Delphi; but MOD() is not a function supported by SQL server. Only the % operator works. ...

Updating MS-Access Database query
hello i need to update my MS-Access database thru matlab. when using the following code using function "updates" it updates the database i.e .......whereClause = 'where month = ''Nov'''..... Here "Nov" is an entry in the database. Now when i assign a variable to the months..ie variable name "MNTH" could contain Jan /Feb etc,.....and i want to pass the variable "MNTH" ie .......whereClause = 'where month = ''MNTH'''..... it DOES NOT update the database and i receive the error .....error in horxcat......

query ms access database from the user
At college i have been given this piece of untidy code because it mixes a gui interface with the console, i prefare one or the other, but its not my code, i understand what it does. It opens the database CarDB by using the java odbc (Open Database Connectivity ) driver and searches for the row make with nissan and displays to the screen as text in a gui text box the columns Registration, Model, Year, Price in the same row as the nissan . I would like the programme to ask the user which car their want to search for . Unfortunately i do not learn at college or have taught myself sql. If i am understanding proberly the line ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year, Price FROM Table1 where Make='nissan'"); is where the query starts, but its hardcoded in, i presume i need to have a line similar to ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year, Price FROM Table1 request Make=' '") or something similar, am i along the correct lines? here is the code.. import java.sql.*; //import javax.swing.JOptionPane; //import javax.swing.JTextArea; import java.io.*; public class Car2 { public static void main(String args[]){ Connection connection; Statement st; String out=""; JTextArea display=new JTextArea(); try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); connection= DriverManager.getConnection("jdbc:odbc:CarDB","","");...

Help connect Ms access database
Hello All, I am trying to connect an MS database without utilizing the help of the "querybuilder", I know how to do that using the "querybuilder" but this can't work as I am trying to do an exe file and be able to use it for other pc's that I have. I am doing the following, please correct me if I am wrong, but it is not working at all: dbpath=[matlabroot,'a\toolbox\database\dbdemos\trial.accdb']; conurl = ['jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,*.accdb)};' ... 'DBQ=' dbpath] conn=database('Trial_now','','&#...

Help setting up Ms-Access Database
I'm hoping that someone here can give me some assistance with a database I'm trying to set up. My skills in Access are fairly basic, and I'm trying to skill up, but some of the stuff is a little opaque. I'm trying to put some data I've got on paper into Access. Recently, I got together with some other people marketing organic produce and offered to do some marketing for us on a coop basis in the city. The idea I had was to have four marketing packages for coop members with different rates and services. For want of something better lets call them P1, P2, P3 & P4 I'm thinking I'll need a table with the packages, and one listing the coop members with their details. In theory, a coop member might acquire another farm (either a new one or one from someone bailing out) and put it on a separate package. Which tables would have to have a relationship? I'm thinking the packages table containing the dat about each package would have to be related via a common field to the coop member table, and there would probably have to be a link between that and the farm table, through common fields -- maybe a unique ID in the coop member table could appear in the farm table and maybe the primary key from the package table could also appear in the farm table as a foreign key. But am I right? I'd also like to create a form that would list an individual coop member's details plus any packages they own and assume that all I'd need to do would ...

MS Access database with a file extension of .mde
Good day: Can you kindly assist me in opening a MS Access database with a file extension of .mde? I get an error message stating "This database is in an unrecognized format". Any help is greatfull. TIA This usually indicates an attempt to open an Access 2000 database in an earlier version. -- MichKa [MS] NLS Collation/Locale/Keyboard Development Globalization Infrastructure and Font Technologies This posting is provided "AS IS" with no warranties, and confers no rights. "mary" <chloemelmom@yahoo.com> wrote in message news:a0a64947.0311151242.4df0c8a3@posting.google.com... > Good day: > > Can you kindly assist me in opening a MS Access database with a file > extension of .mde? > > I get an error message stating "This database is in an unrecognized > format". > > Any help is greatfull. > > TIA "mary" <chloemelmom@yahoo.com> wrote in message news:a0a64947.0311151242.4df0c8a3@posting.google.com... > Good day: > > Can you kindly assist me in opening a MS Access database with a file > extension of .mde? > > I get an error message stating "This database is in an unrecognized > format". > > Any help is greatfull. > > TIA Best place to get answers is at the official MVP site: http://mvp.org Robyn Schulz, MVP A) That url isn't. B) If that's the best place to get answers, why do you hang out in a newsgroup designed to give ...

ms access to sql server query help!
Hi All, I'm desperately trying to use an old access query and translate it to be used in SQL enterprise manager. The dataset i'm trying to update is too large for access (28 million rows). IIf([TableName]![FIELDNAME] Like "??0*",Left([TableName]![FIELDNAME], 2) & Right([TableName]![FIELDNAME],4),IIf([TableName]![FIELDNAME] Like "?0*",Left([TableName]![FIELDNAME],1) & Right([TableName]! [FIELDNAME], 4),[TableName]![FIELDNAME])) This works to remove padding zeros in the middle of a postcode field. eg SR04 PQJ would be SR4 PQJ or S03 4PY would be S3 4PY. It does this by finding all postcodes that begin with either ??0 or ? 0 and then recompiles them using concatenation. This works fine in access but i can't translate it to be read in SQL server. I'm aware of the differences in wildcard chars and the CONCAT function but cannot seem to get it right. Could someone please help!! In SQL Server: 1. There is no such function as Iif, you need to use the CASE function. 2. The string delimiter is a single quote mark, not a double quote mark. 3. "*" is not a wildcard character, you need to use the "%" character. 4. The "!" character will not be recognised, use a full stop character. 5. The "&" operator does not exist, the nearest equivalent is the "+" operator. SQL Server 2000 Books Online can be downloaded for free: http://www.microsoft.com/downloads/details.aspx?Fa...

MS Access Help: Copying database Relationships
Does anyone know if there's an easy way to export or copy the relationship structure from one mdb to a new one? The relationships in the db are time consuming to reproduce. I am trying to make a copy of a database including all the relationships, forms, queries and modules while still within the original database. This has to be a relatively automated process (button click). Any suggestions? yeah; use ACCESS DATA PROJECTS and you can do this all through TSQL. -aaron Instead of Exporting, make: Get External Data --> Import... All the existing relationships will be imported with the tables. Click on the Options button on the Import dialog window to have more choices. Also, I don't see your need to have made that much crossposting. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC "news.giganews.com" <anthony@cambria.com> wrote in message news:3PqdnaFVzZFht1HfRVn-vw@giganews.com... > Does anyone know if there's an easy way to export or copy the relationship > structure from one mdb to a new one? The relationships in the db are time > consuming to reproduce. I am trying to make a copy of a database including > all the relationships, forms, queries and modules while still within the > original database. This has to be a relatively automated process (button > click). Any suggestions? > > Check out the CreateRelation method in Access Help. There is an example there. I have not done this, ...

MS Access & SQL Database for JPEGS
I'm trying to create a web accessed JPEG database for all of my p 0 r m pics. Anybody know where to start? The site I'm interested in putting in an Access database is at the link below: Don't click on this link unless you want to be seriously offended. http://www.tubgirl.com ...

Need Help on MLM database in MS Access
I am working on a MS Access database to track the sales of a 3 Tier Multi- level Marketing system. I have created a table to track a new RepID and a SponsorID. I have created a query that will show the Sponsor and all of their reps but I am not sure how to show the reps that the second tier has sponsored. Example: RepID 0011 SponsorID 0010 RepID 0012 SponsorID 0010 RepID 0013 SponsorID 0010 The above is easy but I am not sure how to get: RepID 0014 SponsorID 0013 and his SponsorID 0010 Does anyone have any idea of a good way to do this? Thank you, Brent Brent, It concept, it is a hierarchical data problem. Others here have solutions different from the three I know about. Each solution to this problem has its benefits and drawbacks. Study each one to decide which works best for you. Solution 1: Single table joined to itself and modeling a one-to-many relationship between a row in the table and other related rows in the same table. Solution 2: Two tables, one is the detailed information about each thing in the hierarchy (sales representative in your case) and the other is a two column list of primary keys from the first table that lists which thing in the first table is related to the other and modeling a many-to-many relationship between a row in the first table and another row in the first table. Solution 3: Two tables like Solution 2 but with as many columns as are needed in Table 2 to list each level of the hierarchy. This can make navigating...

MS Access Script to import data from external mde database to mdb database
Hi, Does anyone know of a way I can write a script that imports a table from a particular .mde file? I don't seen any obvious way to do it as a macro. Any suggestions if it can be done as a macro or as a written module? My ultimate goal is to have script that clears info out of 1 table in an .mdb file, imports an identically structured table from a .mde file, runs an append query I have in place to copy the data from the imported table to the table in my.mbd file, and then delete the imported table. Any help that could be provided would be greatly appreciated. Best wishes, George Hadley ghadley_00@yahoo.com With DBEngine(0)(0) .Execute "DELETE * FROM Suppliers WHERE SupplierID IN (SELECT SupplierID FROM Northwind.mde.Suppliers)" .Execute "INSERT INTO Suppliers SELECT * FROM Northwind.mde.Suppliers" End With Is this code I would run as a module? Yes or you could use the strings in two separate saved queries. ...

help MS ACCESS\SAS, general database questions
Hi to all, I need some help deciding on the best database system for our specific application. OPTIONAL INFO: We currently have 2 separate projects that need to be updated from dos-based dBase. I'll only describe one of these but the other is comparable in size. The project currently has about 32 files. The 2 largest are about 30,000 records and 5 columns include data from the last 30 years. The other 30 files are created by year and are about 1000 records and 10 columns. There are only 2 or 3 people keying in data, never at the same time. We also have a sonic digitizer (measures distance between age rings on fish scales) from which data is fed into the dBase form. I think the data transfer program is written in C and the dBase form is written in Clipper. We currently have MS Access and SAS. My suggestion was to design the same type of user entry forms in MS Access and place the files on a shared drive on our network server (already in place and routinely backed up). If the amount of data or number of users increased we could consider going to a database server with Access as the "front end." Most of the analysis is performed in SAS which can readily import Access tables. I was met with some opposition: SAS/ACCESSS/SERVER QUESTIONS: There was a strong objection to using ODBC to get data into SAS (when discussing database servers). Does SAS Import wizard use ODBC? Are there inherent problems with ODBC? Is there any advantage to having data analyze...

Tutorial; Accessing MS SQL Database using C++!
hey! Im looking for a db tutoral which covers accessing a Microsoft SQL database on windows. I will be using VC++ if it matters. NO details just a basic introduction! Oracle DB access using C++ is also of interest! Thanks! -- Majk majk <msa_pub@softhome.net> wrote in message news:<BB7F5CCB.6D61%msa_pub@softhome.net>... > hey! > > Im looking for a db tutoral which covers accessing a Microsoft SQL database > on windows. I will be using VC++ if it matters. NO details just a basic > introduction! > > Oracle DB access using C++ is also of interest! > >...

Export relations from SQL Server Database to MS Access
Hi I have to make export of SQL Server Database to MS Access and I have done it with the tables but now I need to transfer(export) the relations, keys and indexes. Can any1 tell me how to read relations,keys and indexes from SQL Server and convert them to MS Access.(it seams that for creation of indexes in MS Access ADOX is the only way but ADOX cannot read all information about keys,indexes from SQL Server.) All I can see is that I have to use SQLDMO to enumarate all realtion/keys/indexes from SQL Server database and use ADOX to create this relations/keys/indexes in MS Access database, but I don't know exact matching attributs of relations/keys/indexes from SQL to Access. Thanks Hi You posted the same question in microsoft.public.sqlserver.programming. If you are not sure about the answers you were given then please do not cross post. John "Bose" <bosejob@sympatico.ca> wrote in message news:ztBFc.97111$Ax1.1377116@news20.bellglobal.com... > Hi > I have to make export of SQL Server Database to MS Access and I have > done it with the tables but now I need to transfer(export) the > relations, keys and indexes. Can any1 tell me how to read relations,keys > and indexes from SQL Server and convert them to MS Access.(it seams that > for creation of indexes in MS Access ADOX is the only way but ADOX > cannot read all information about keys,indexes from SQL Server.) > All I can see is that I have to use SQLDMO to enumarate ...

Adding records to a SQL Server database from MS Access
Thanks in advance for your help. I have just upsized an Access database to SQL Server with the hope of using a fairly extensive Access application that was part of it. But now, the forms will not allow the addition of new records (the >* button is greyed out). The forms use a macro to advance the dataset to a new record. How can I get the additions (inserts) turned back on? Regards. ...

Please, recommend me an application to read (only) Ms-Access databases.
Dear friends: Please, recommend me a Mac application to read (only) Ms-Access databases. Thanks Juan I. Cahis Santiago de Chile (South America) Note: Please forgive me for my bad English, I am trying to improve it! In article <037m241jvlm60fde84o0ebacfijpda8k22@4ax.com>, Juan I. Cahis <jiclbchSINBASURA@attglobal.net> wrote: > Dear friends: > > Please, recommend me a Mac application to read (only) Ms-Access > databases. <http://www.google.com/search?client=safari&rls=en-us&q=read+access+data base+macintosh&ie=UTF-8&oe=UTF-8> -- Woodworking ...

Re: Accessing MS SQL server or ORACLE database from the HP3K
Folks, One small clarification to my previous question: We are not trying to migrate our IMAGE data to another platform. We are trying to provide an enhancement where data that already exist in a database on another platform can be viewed, extracted and used to "complete" an existing entry in the IMAGE database, thus eliminating the need for the user to manually 're-enter' the data. Regards Paul Christidis =============================================================================== Folks, I was asked the following question today and decided to turn to y...

how do you update a java.sql.date type in an ms access database
when i update a row in an access database the date field does not update to the correct date it brings up some funny date which i dont know where it comes from. Below is the method i use to update. maybe the date types are not compatable? thanks in advance, public static void bookIN(int custID, int rNumber,java.sql.Date bkInDate ){ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String filename = "hotelDB.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; Connection con = DriverManager.getConnection( database ,"",""); int yes = 1; Statement s = con.createStatement(); s.executeUpdate("update Rooms set CustomerId ="+custID+",BookINDate ="+ bkInDate +",Occupied = "+yes+" where RoomNo = "+rNumber+" "); s.close(); con.close(); } catch (Exception err) { System.out.println("ERROR: " + err); } } "chose" wrote... > when i update a row in an access database the date field > does not update to the correct date it brings up some > funny date which i dont know where it comes from. It comes from db's attempt to parse an invalid date-string... In Access/Jet,...

Web resources about - Is this possible? please help (MS access query of sql database) - comp.databases.ms-sqlserver

Obama's Visit Will Hasten Cuba's Freedom
WASHINGTON The historic visit of a sitting U.S. president to Havana which should have come a half-century sooner will almost surely hasten the ...

10 Ways Twitter Changed Marketing in the Past 10 Years
Twitter recently highlighted 10 ways the company has changed marketing since it launched 10 years ago.

Attack on EU military training mission in Mali over
ABIDJAN (Reuters) - An attack by gunmen on Monday that targeted a hotel in Mali's capital Bamako that had been converted into a base for a European ...

At least ten dead in Brussels Metro blast: Horrific scenes as second terror attack hits packed station ...
Shocking images from the scene show smoke pouring out of Maelbeek station and casualties littered on the pavement outside.

Hulk Hogan-Gawker jury awards $25M in punitive damages
On Monday, the jury hit Gawker Media with a $15 million judgment and its owner, Nick Denton, with $10 million. It also assessed $100,000 against ...

Andy Grove, tech legend and former Intel CEO, passes away
Intel's former CEO and Chairman Andrew S. Grove has passed away today, March 21st. He was 79 years old. The company owes much of its success ...

'Batman v Superman' will be a key test for Warner's film CEO and a slate of DC Comics films
At an October 2014 investor meeting in New York, Warner Bros. Entertainment Chief Executive Kevin Tsujihara laid out a bold plan to release at ...

FanDuel, DraftKings shut down daily fantasy sports games in New York
By Michael Erman and Karen Freifeld NEW YORK (Reuters) - Top daily fantasy sports companies FanDuel and DraftKings agreed to halt their business ...

Sorry, Mischa Barton: Nyle DiMarco is our early fave on 'Dancing With the Stars'
Monday's season premiere of Dancing with the Stars may have been one of the most impressive yet. Not only did seven couples crack scores of ...

China says Philippine fishermen used fire bombs in South China Sea
Philippine fishermen threw fire bombs at Chinese law enforcement vessels in the South China Sea, China's Foreign Ministry on Tuesday, after Philippine ...

Resources last updated: 3/22/2016 10:33:06 AM