Solved

Merging Queries for Users

Posted on 2004-09-30
4
208 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
ID: 12193808
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
ID: 12193842
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
ID: 12193946
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
ID: 12194148
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

947 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

22 Experts available now in Live!

Get 1:1 Help Now