Solved

Combining Tables

Posted on 2004-04-02
6
391 Views
Last Modified: 2008-09-29
Hello,
I need a push in the right direction.  I have 3 tables that I need to combine into one.  Two of my tables are indentical as far as the layout is concerned.  I have one static master table that holds user ID information, which is also my primary key.  The tables are layed out as follows:

TABLE 1 (Drop ship orders):
USER_ID, NAME, NUM_OF_ORDERS, TOTAL_AMOUNT, DATE

TABLE 2 (Stock orders):
USER_ID, NAME, NUM_OF_ORDERS, TOTAL_AMOUNT, DATE

TABLE 3 (Purchase Orders):
USER_ID, NAME, NUM_OF_PO, TOTAL_AMOUNT, DATE

These need to be merged into one table with the following layout:

MASTER TABLE:
USER_ID, NAME, NUM_OF_ORDERS, TOTAL_AMOUNT, NUM_OF_PO, TOTAL_AMOUNT, DATE

The master table has all possible user ID's and names already populated but the remaining columns will change daily.  The reason behind the two order tables is the system we use holds drop-ship orders and stock orders in two different tables.  I have an update query to update the master table but the problem I'm having is it will only add information that it finds in all three tables.  So if I have a user who keyed a drop-ship order but didn't key in a stock order it won't add it.  I've tried using different joins with no luck and now I'm grasping at straws.  I'm very new to Access to if someone has code examples that would be helpful.  

Thank you in advance.
0
Comment
Question by:liquid22
[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
6 Comments
 
LVL 7

Assisted Solution

by:MaB
MaB earned 45 total points
ID: 10740583
Hi liquid22,
I have three taables myself which I combine in a nested JOIN query. Maybe that will help to push you in the right direction:

SELECT Frvtid.anstID, Frvtid.startdate, Frvtid.enddate, Frvtid.starttime, Frvtid.endtime, Frvtid.description, Frvtid.privat, Orsak.frvOrsak, Lokal.lnamn, Lokal.beteckn, Lokal.ort, Lokal.ftg, Lokal.tfn
FROM Orsak RIGHT JOIN (Lokal RIGHT JOIN Frvtid ON Lokal.lokID=Frvtid.lokID) ON Orsak.frvID=Frvtid.frvID
ORDER BY Frvtid.anstID, Frvtid.startdate, Frvtid.starttime, Frvtid.endtime;

The tables are Frvtid, Orsak and Lokal.

BR MaB.
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 145 total points
ID: 10740588
you could do an insert into select

ie. select records from one table and insert into another

e.g.

INSERT INTO EE1 ( StringField, NumberField )
SELECT [StringField], [NumberField]
FROM EE2
WHERE NumberField NOT IN (SELECT NumberField FROM EE1);


This inserts into the table EE1,the fields StringField and NumberField are populated, those same values are taken from EE2

I put the where clause in to stop duplicates, but that is optional
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 145 total points
ID: 10740609
create a table as defined by MASTER TABLE
then insert into that

INSERT INTO tblMaster (USER_ID, NAME, NUM_OF_ORDERS, TOTAL_AMOUNT, DATE)
SELECT USER_ID, NAME, NUM_OF_ORDERS, TOTAL_AMOUNT, DATE FROM tblOne

INSERT INTO tblMaster (USER_ID, NAME, NUM_OF_ORDERS, TOTAL_AMOUNT, DATE)
SELECT USER_ID, NAME, NUM_OF_ORDERS, TOTAL_AMOUNT, DATE From tblTwo

INSERT INTO tblMaster (USER_ID, NAME, NUM_OF_PO, TOTAL_AMOUNT, DATE)
SELECT USER_ID, NAME, NUM_OF_PO, TOTAL_AMOUNT, DATE FROM tblThree

if you want to assign default values to the other fields not populated, either do it thru DB defintion or thru either
e.g.
INSERT INTO tblMaster (USER_ID, NAME, NUM_OF_PO, NUM_OF_ORDERS, TOTAL_AMOUNT, DATE)
SELECT USER_ID, NAME, NUM_OF_PO, 0, TOTAL_AMOUNT, DATE FROM tblThree


0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:liquid22
ID: 10740635
Thank you all for your help.  I split my points because each answer helped clarify my problem.
0
 
LVL 2

Expert Comment

by:NevHolland
ID: 10740726
There are two ways that I can think of.
1) A union query
2) Update Queries

With the union query you would have to add some columns to you tables so they look exactly the same.

With the Update query if your users appear in two tables then some data would be over written.

Which one do you want to look at?

Nev
0
 
LVL 2

Expert Comment

by:NevHolland
ID: 10740734
How slow am I
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
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…

696 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