By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Combining Data from 4 Tables

P: 2
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
Oct 12 '07 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 12 '07 #2

P: 2
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)
Oct 14 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Well done !

Success with your application !

Nic;o)
Oct 14 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.