[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Combining Tables

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
liquid22
Asked:
liquid22
3 Solutions
 
MaBCommented:
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
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
liquid22Author Commented:
Thank you all for your help.  I split my points because each answer helped clarify my problem.
0
 
NevHollandCommented:
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
 
NevHollandCommented:
How slow am I
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now