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

x
?
Solved

Combining Tables

Posted on 2004-04-02
6
Medium Priority
?
401 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 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