Bartley1969
asked on
How to create a complex join
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.
Hope this will help you
select * into tableA from tableb b join tablec c where b.customeraccount=c.custom eraccount and b.bankaccount = c.customeraccount.
select * into tableA from tableb b join tablec c where b.customeraccount=c.custom
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.CustomerBankAccoun t
FROM qryInfo;
Execute the 2nd query and tblA will have everything from tblB and tblC combined.
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.CustomerBankAccoun
FROM qryInfo;
Execute the 2nd query and tblA will have everything from tblB and tblC combined.
Sorry like this.
select * into tableA from tableb b join tablec c on b.customeraccount=c.custom eraccount and b.bankaccount = c.customeraccount
select * into tableA from tableb b join tablec c on b.customeraccount=c.custom
ASKER
match on both feilds...
attached is sample of contents of tableB and TableC and desired results to show in TableA
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
ASKER
I forgot to mention, I would like to create a maketable query in access to do this....
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]
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rick, I'm following your instructions, many thanks...
You're most welcome.
Rick
Rick
ASKER
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?
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?
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
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
ASKER
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!)
anyhow, can you show sample data of the 2 fields and the required output... to avoid any misunderstandings...