Solved

How to create a complex join

Posted on 2007-12-03
14
192 Views
Last Modified: 2010-04-21
I would like to create a summary table "tblA" based on records in two source tables "tblB" and "tblC". both of these source tables have two index fields - customer account and custoemr bank account. A record in "tblB" maynot have a corresponding record in "tblC" and vice versa. but "tblA" must show sum of all records between tableB and TableC.
0
Comment
Question by:Bartley1969
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
corresponding... with a "match" on both fields or only 1 of the fields?
anyhow, can you show sample data of the 2 fields and the required output... to avoid any misunderstandings...
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
Comment Utility
Hope this will help you
select * into tableA from tableb b join tablec c where b.customeraccount=c.customeraccount and b.bankaccount = c.customeraccount.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
Comment Utility
This is a 2 step process.  First Create a UNION query to pull all the data togeter.  For this example let's just call it qryInfo

1st Query's Name: qryInfo

SELECT CustomerAccount, CustomerBankAccount FROM tblB;
UNION ALL
SELECT CustomerAccount, CustomerBankAccount FROM tblC;

2nd Query to INSERT data from qryInfo into tblA

INSERT INTO tblA ( CustomerAccount, CustomerBankAccount )
SELECT qryInfo.CustomerAccount, qryInfo.CustomerBankAccount
FROM qryInfo;

Execute the 2nd query and tblA will have everything from tblB and tblC combined.
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
Comment Utility
Sorry like this.
select * into tableA from tableb b join tablec c on b.customeraccount=c.customeraccount and b.bankaccount = c.customeraccount
0
 

Author Comment

by:Bartley1969
Comment Utility
match on both feilds...
attached is sample of contents of tableB and TableC and desired results to show in TableA


TableB		

Customer Acc	Cust bank account	txdet

079	0000	120

078	0001	1200

		

TableC		

Customer Acc	Cust bank account	txdet

079	0000	12

080	0001	12000

		

		

TableA		

079	0000	132

078	0001	1200

080	0001	12000

Open in new window

0
 

Author Comment

by:Bartley1969
Comment Utility
I forgot to mention, I would like to create a maketable query in access to do this....
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if both tableB and tableC do NOT have duplicated on the customer/cust bank account:
select coalesce(b.[customer acc], c.[customer acc]) as [customer acc]

, coalesce( b.[cust bank account], c.[cust bank account]) as [cust bank account]

, coalesce( b.txdet , 0 ) + coalesce( c.txdet , 0) as total_txdet

FROM TableC c

FULL OUTER JOIN TableB b

  on c.[Customer Acc] = b.[Customer Acc]

 and c.[cust bank account] = b.[cust bank account]

Open in new window

0
Highfive Gives IT Their Time Back

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!

 
LVL 16

Expert Comment

by:Rick_Rickards
Comment Utility
This is a 2 step process.  First Create a UNION query to pull all the data togeter.  For this example let's just call it qryInfo

1st Query's Name: qryInfo

SELECT Customer, Acc, [Cust bank account] FROM tblB;
UNION ALL
SELECT Customer, Acc, [Cust bank account] FROM tblC;

2nd Query to INSERT data from qryInfo into tblA (If tblA already exists)

INSERT INTO tblA (Customer, Acc,  [Cust bank account])
SELECT qryInfo.Customer, qryInfo.Acc, qryInfo.[Cust bank account]
FROM qryInfo;

OR...

2nd Query to create tblA if it does not already exist (Make Table Query)

Customer, Acc, [Cust bank account]
SELECT qryInfo.Customer,  qryInfo.CustomerAccount, qryInfo.[Cust bank account] INTO tblA
FROM qryInfo;


Execute the 2nd query and tblA will have everything from tblB and tblC combined.
0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 500 total points
Comment Utility
Sorry, missed the last field in your example...

This is a 2 step process.  First Create a UNION query to pull all the data togeter.  For this example let's just call it qryInfo

1st Query's Name: qryInfo

SELECT Customer, Acc, [Cust bank account], txdet FROM tblB;
UNION ALL
SELECT Customer, Acc, [Cust bank account], txdet FROM tblC;

2nd Query to INSERT data from qryInfo into tblA (If tblA already exists)

INSERT INTO tblA (Customer, Acc,  [Cust bank account], txdet)
SELECT qryInfo.Customer, qryInfo.Acc, qryInfo.[Cust bank account], qryInfo.txdet
FROM qryInfo;

OR...

2nd Query to create tblA if it does not already exist (Make Table Query)

Customer, Acc, [Cust bank account]
SELECT qryInfo.Customer,  qryInfo.CustomerAccount, qryInfo.[Cust bank account], qryInfo.txdet INTO tblA
FROM qryInfo;

Execute the 2nd query and tblA will have everything from tblB and tblC combined.
0
 

Author Comment

by:Bartley1969
Comment Utility
Rick, I'm following your instructions, many thanks...
0
 
LVL 16

Expert Comment

by:Rick_Rickards
Comment Utility
You're most welcome.

Rick
0
 

Author Comment

by:Bartley1969
Comment Utility
Rick,
I've created the union query but it returned each record and not the summary of each unqie record, for example customer acc 079 with bank account 0000 has a record in TableB and TableC, this shows up as two record in the union as aopposed to a single record with the sum of the two records. Any ideas?
0
 
LVL 16

Expert Comment

by:Rick_Rickards
Comment Utility
Yes, the original query was designed to display all data from both tables B and C even if an identical record existed in each of the two tables.  If you wanted duplicate records to be excluded or rather displayed only once then your UNION query would look like this...

SELECT Customer, Acc, [Cust bank account], txdet FROM tblB;
UNION
SELECT Customer, Acc, [Cust bank account], txdet FROM tblC;

The difference is that UNION ALL shows everything but UNION by itself will remove any duplication between the two tables and display it only once.

Rick
0
 

Author Closing Comment

by:Bartley1969
Comment Utility
dsicovered that using * to select all fields with UNION requires that all fields in both tables are in the same roder and have same heading spelling (logically!)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

15 Experts available now in Live!

Get 1:1 Help Now