Solved

Combining Tables

Posted on 2004-04-02
6
381 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

831 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