?
Solved

Combining Tables

Posted on 2004-04-02
6
Medium Priority
?
394 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 180 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 580 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 580 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…
Suggested Courses

762 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