Hello,

I have 2 databases, one our client database holding contact details etc and one a database containing details of documents we hold for our clients ('we' are a firm of solicitors)

So, DB1 is client info and DB2 is Doc info. Both are access 97 (yes I know!).

I'm using ASP and ADO to present a nice front end and SQL to access the data.

This is my problem:

DB2 has a table called client info that links to DB1 to avoid replication of data. Another table in DB2 called doc info has a field which does a lookup against a field in the DB2 client info table to obtain client info data.

Code as below:

sql="SELECT PcktID, CLID, Archive_Date, Office_Stored, PrAddr1, EnteredBy, PacketDescription, DeedDescription FROM XXXX"

OK, so CLID is the field looked up by the document table in DB2 which points to the second table in DB2 (client info) which then references DB1 for its data.

My question is:

How do I force my SQL select call to display the field in DB2 client info that I want? By default it picks up the first field (as this is the CLID) but in the database that is just a numerical code, further along the same row will be the actual client name etc that I want to display.

Does that make any sense?

Thanks in advance.

Roger

(learning SQL - can you tell? ;) )

[Edited by rogp - 1/29/2004 3:46:15 PM]

[Edited by rogp - 1/29/2004 3:49:20 PM]

Not sure if I completely understood you, but I think you want to something like this:

SELECT
DBx.tablename.PcktID,
DBx.tablename.ClientName,
DBx.tablename.Archive_Date,
DBx.tablename.Office_Stored,
DBx.tablename.PrAddr1,
DBx.tablename.EnteredBy,
DBx.tablename.PacketDescription,
DBx.tablename.DeedDescription
FROM DB1,DB2
WHERE DB1.tablename.CLID = DB2.tablename.CLID

Obviously insert the correct database for each "DBx" and the correct table name for each "tablename".

Hope this helps...

[Edited by Jerome - 1/29/2004 7:23:54 PM]

Thanks, I'll give that a go.

So you can use mutliple tables within a single SELECT command?

Roger

rog,

yes you can just do a search in help for 'joins' be very careful though access is not SQL standard (ANSI92) compliant so what you learn will not carry you thru to SQL Server/Oracle/DB2 but it does support joins and thats what you need to learn up on.

Cheers
\
Gary

Thanks again to you both.

Roger