Solved

Merging Queries for Users

Posted on 2004-09-30
4
210 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Record with #Error 8 33
how to format one of my field to percent with precision of two? 3 18
Signature required on a report 5 29
IIF in access query 19 23
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…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

808 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