Solved

Merging Queries for Users

Posted on 2004-09-30
4
207 Views
Last Modified: 2008-03-06
Hey guys, I have a question on building a table/query.  I have three make-table queries that count and summarize some huge linked text files.  The three tables look like this:

tbl_1                                                tbl_2                                    tbl_3
Quarter    Agent     Sales A                Quarter   Agent    Sales B         Quarter     Agent   Sales C
01             Jeff           3                        01          Jeff         7                  01         Jeff        5
02             Jeff           5                        02          Jeff         9                  02         Jeff        9
01             Don          6                        01          Mary       5                  01          Bev        8
02             Don           7                       02          Mary        8                  02         Bev        3
01             Jay            9

What I need to do is somehow combine these dissimilar made-tables into one reference table to stick in the DB backend.  I need the table to look like:

Quarter Agent     Sales A       Sales B       Sales C
 01         Jeff         3                 7                 5
 02         Jeff          5                9                9
01           Don         6                (0)              (0)
02          Don          7                (0)              (0)
01         Mary        (0)              5                 (0)
02         Mary       (0)               8                 (0)

Basicially I want the quarter and agent to be the PK in the new table, then fill in the other columns with the appropriate information IF it is present.  If not I want to be null which I can then manipulate into zeros or whatever.

Does anyone know a way to go about this?  I really need to put this together on one table for a number of reasons.

Thanks,
Jeff
0
Comment
Question by:JeffGMACI
  • 2
4 Comments
 
LVL 5

Expert Comment

by:jmacmicking
Comment Utility
You can do it, but it ain't pretty.  I'd use five seperate queries to do the work.  The first simply appends everything from tbl_1.  The second updates the main table with any matching records from tbl_2.  The third appends any records on tbl_2 that don't match.  Fourth and fifth queries are the same as 2 and 4 but handle the third table.  The queries need to be run in order (1-5) otherwise they won't work.  SQL for the queries follows:

First, you'll have to create the main table and all it's fields (Quarter, Agent, SalesA, SalesB, SalesC).  I called it tbl_All.  Note that I left the space out of the field names; if this isn't possible you may have to put the field names in brackets ([]) to support the spaces.

Pass1:
INSERT INTO tbl_All ( Quarter, Agent, SalesA )
SELECT tbl_1.Quarter, tbl_1.Agent, tbl_1.SalesA
FROM tbl_1;

Pass2:
UPDATE tbl_2 INNER JOIN tbl_All ON (tbl_2.Quarter = tbl_All.Quarter) AND (tbl_2.Agent = tbl_All.Agent) SET tbl_All.SalesB = tbl_2.SalesB;

Pass3:
INSERT INTO tbl_All ( Quarter, Agent, SalesB )
SELECT tbl_2.Quarter, tbl_2.Agent, tbl_2.SalesB
FROM tbl_2 LEFT JOIN tbl_All ON (tbl_2.Agent = tbl_All.Agent) AND (tbl_2.Quarter = tbl_All.Quarter)
WHERE (((tbl_All.SalesB) Is Null));

Pass4:
UPDATE tbl_3 INNER JOIN tbl_All ON (tbl_3.Quarter = tbl_All.Quarter) AND (tbl_3.Agent = tbl_All.Agent) SET tbl_All.SalesC = tbl_3.SalesC;

Pass5:
INSERT INTO tbl_All ( Quarter, Agent, SalesC )
SELECT tbl_3.Quarter, tbl_3.Agent, tbl_3.SalesC
FROM tbl_3 LEFT JOIN tbl_All ON (tbl_3.Agent = tbl_All.Agent) AND (tbl_3.Quarter = tbl_All.Quarter)
WHERE (((tbl_All.SalesC) Is Null));
0
 
LVL 2

Author Comment

by:JeffGMACI
Comment Utility
This is what I had come around to think I'd need to do... The appends make sense but was having trouble with the update piece.  I'll work on it and see if I can get back to you on how it goes.

Thanks!!
Jeff
0
 
LVL 16

Accepted Solution

by:
Nestorio earned 500 total points
Comment Utility
Do first an union query (say q1):

Select Quarter, Agent  From tbl_1
Union
Select Quarter, Agent  From tbl_2
Union
Select Quarter, Agent  From tbl_3

Then do this:

Select q1.Quarter, q1.Agent, Nz([Sales A]) as Sales_A,
Nz([Sales B]) as Sales_B, Nz([Sales C]) as Sales_C
Into tblDest
From
((q1 left join tbl_1 ON q1.Quarter=tbl_1.Quarter and  q1.Agent=tbl_1.Agent)
left join tbl_2 ON q1.Quarter=tbl_2.Quarter and  q1.Agent=tbl_2.Agent)
left join tbl_3 ON q1.Quarter=tbl_3.Quarter and  q1.Agent=tbl_3.Agent
0
 
LVL 2

Author Comment

by:JeffGMACI
Comment Utility
Ohhhh good idea!  Make a reference column of all the possible quarters and salesmen and then left join it to the data... nice.  I'm going to try this tomorrow morning.  SOunds GREAT.

Jeff
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now