Join of multiple tables

[docblop]

I'm a newbee in sql and I'm just trying to connect two tables in an access database through asp. It is a 1 to n relationship, of which I'd like to display everything of the first table together with the last entry of the second table. Does anyone know how the join statement would look like?
The first table (Contacts) contains a colum 'ID', which is set to autoNumber. The second table (Contacts2) contains a colum 'ID' and a colum called 'ContactID', which leads to the first table. I need all the Information of all records of the first table together with all the information of the latest (highest ID-value) record of the second one. Can anyone help me? I totally got lost. I'd like to know how the sql statement would look like for that.

Thank you guys in advance..

Mike.. [sig][/sig]

 

[Amiel]

You will end up with a join from your table (on the left side) and a query on the right side, thus...

Create a query designed to get the last record for the ContactID column, call it "qryTemp1" (or whatever):

"SELECT Last([table2].[ContactID]) as [Contact Id], First([table2].[ID]) as [ID], First([table2].[Field2])AS [Field 2] from [table2] Group By [table2].[ContactID] Order By [table2].[ContactID] DESC"
...adding the necessary fields

...then create your final query (call it "Query1" or whatever) with....


"SELECT * from [Table1] left join qryTemp1 on [Table1].[ID] = [qryTemp1].[ID]"


This should work, let me know if it brings down the network ... |-I
[sig]<p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br> [/sig]