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!