Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Merging Queries for Users

Posted on 2004-09-30
4
Medium Priority
?
214 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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

636 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