Solved

How to merge two tables on access

Posted on 2008-10-12
3
1,544 Views
Last Modified: 2012-05-05
Hello Experts,
I am in the process of analyzing the customer base against the salary amounts.  I have the following  
Table one
Cust ID      Salary
4556      500
4321      600
4566      100
123      999
1234      456

Table two
Cust ID      Salary
4556      399
54545      999
4566      5000
123456      500
4321      444


I need to have one table that has all the data.  Please note that not all customers in table 1 are in table two and not all customers in table 2 are in table 1.
Therefore I am having a problem joining these two tables.
I would like to have the following
Cust ID      Salary1      Salary2

Where if for example customer 4321 salary in table1 is 600 but  he is not in table 2 so value of zero should be assigned in the new table im trying to create
Thanks
0
Comment
Question by:dina78
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 22700619
Create Query1 and save it.

Select custid from table1
Union
Select Custid from table2

Create query2:

Select query1.*, table1.salary as salary1, table2.salary as salary2
from (query1 left join table1 on query1.custid = table1.custid)
left join table2 on query1.custid = table2.custid
0
 
LVL 1

Expert Comment

by:rehan_k99
ID: 22700822
select distinct custid, table1.salary, table2.salary from table1, table2
0
 

Author Closing Comment

by:dina78
ID: 31407106
perfect solution thanks alot
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now