Combining Data from 4 Tables

[cjbutler]

Hi,

I am using MS Access 2003 on a Windows XP platform. I am wondering what the best way is to go about combining data from 4 tables. Here is a sample from all 4 tables, with what I would like my results be in in the table below called "TBL_Compiled". Any help is appreciated, and thanks!

TBL_Start_1:
ID, Resource_Name, Start_Date, End_Date
1111, John Smith, 4/11/2007, <blank>
1112, Susan Doe, 5/25/2007, <blank>

TBL_End_1:
ID, Resource_Name, Start_Date, End_Date
1111, John Smith, <blank>, 06/21/2007
1113, William Henry, <blank>, 09/30/2007

TBL_Start_2:
ID, Resource_Name, Start_Date, End_Date
1113, William Henry, 07/25/2007, <blank>

TBL_End_2:
ID, Resource_Name, Start_Date, End_Date
1112, Susan Doe, <blank>, 10/31/2007

TBL_Compiled:
ID, Resource_Name, Start_Date, End_Date
1111, John Smith, 4/11/2007, 06/21/2007
1112, Susan Doe, 5/25/2007, 10/31/2007
1113, William Henry, 07/25/2007, 09/30/2007

[nico5038]

For this you can use a UNION to get all rows into one set.
Based on the union you can define a GroupBy query to "compact" the Start and End dates into one. The "risk" of the GroupBy will be the possible occurrence of multiple Start and or End dates per ID/Resource_Name and/or typo's in the Resource_Name. This can be handled by taking the Minimum for the Start date and the Maximum for the End date.

Getting the idea and need more support ?

Nic;o)

[cjbutler]

Wahoo! That worked! Thank you!

For this you can use a UNION to get all rows into one set.
Based on the union you can define a GroupBy query to "compact" the Start and End dates into one. The "risk" of the GroupBy will be the possible occurrence of multiple Start and or End dates per ID/Resource_Name and/or typo's in the Resource_Name. This can be handled by taking the Minimum for the Start date and the Maximum for the End date.

Getting the idea and need more support ?

Nic;o)

[nico5038]

Well done !

Success with your application !

Nic;o)