Solved

Merging Queries for Users

Posted on 2004-09-30
4
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

717 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