Left Join 3 tables but only most recent record for each joined record.

[benshole]

Its not all that easy a question to sum up in a subject box. So here is a "better" description.

I have 3 tables, one table containing the main information and 2 tables containing running information about the records in the first.
The first table contains no duplicates and pretty much as is.
The other 2 have a number of records for each record in the first table but these are accompanied by a "startdate" and "enddate" field.

I have been trying to create a query to select all MOST RECENT information from the 2nd and 3rd tables and join that to the single record from table1 so i end up with a recordset with all the current information for each item in table1.

Ok maybe "better" description wasn't the best phrase. Ill try and make it a little clearer.

Table 1
ID, name
1, foo
2, bar
3, baz

Table 2
ID, table1id, startdate, enddate, otherfield
1, 1, 19840203, 19900304, cheese
2, 1, 19910103, NULL, chocolate
3, 2, 19940407, 20011605, mice

Table 3
ID, table1id, startdate, enddate, otherfield
1, 2, 19920615, 19921112, dancing
2, 4, 20060202, 20061101, plumbing
3, 4, 20070101, NULL, eating

I am trying to figureout a query to return...
1, foo, 19920103, NULL, chocolate, NULL, NULL, NULL
2, bar, 19940407, 20011605, mice, 19920615, 19921112, dancing
3, baz, NULL, NULL, NULL, 20070101, NULL, eating

I hope that makes it a little clearer!
Many thanks,

Ben

P.s. Sorry for the very lengthy first post! I promice they wont always be like that!

[fischermx]

Is "ID" in table2 and table3 its primary key or a foreign key?
I don't get which is the joining column.
It'd be nice if you provide the DML to create the tables and populate a few rows.

[benshole]

the ID in all tables are their own primary key. the "table1id" is the forign key and joining column with ID from table 1.

Ben