Multi-table query question

H

Hanksor

I have 5 tables;
Customers
CustNo
tblSales2002
tblSales2003
tblSales2004
tblSales2005
All Sales tables have the same fields; CustNo; Div; Year; Rank; Total

When I try to create a query with these tables linking Customer.CustNo with
CustNo in the Sales tables, I get many duplicate records. What am I doing
wrong? Any help will be appreciated.
 
J

John Vinson

I have 5 tables;
Customers
CustNo
tblSales2002
tblSales2003
tblSales2004
tblSales2005
All Sales tables have the same fields; CustNo; Div; Year; Rank; Total

When I try to create a query with these tables linking Customer.CustNo with
CustNo in the Sales tables, I get many duplicate records. What am I doing
wrong? Any help will be appreciated.

The first thing you're doing wrong is storing data in tablenames.

A much better design would have ONE Sales table, with an indexed
SaleDate field - or, if you have a REALLY good reason for it, an
indexed Integer SalesYear field. This will take *less* space than your
four tables (only one table overhead, not four), allow new years to be
entered automatically, will allow you to create Queries to pull the
data for any one year, and will solve your current problem.

You're getting duplicate records because if a Customer has multiple
records in two or more of the sales tables, and you just join all four
sales tables to the Customer table, there is no relationship between
the records in tblSales2004 and the records in tblSales2005. Access
will (quite properly) give you every possible combination of that
customer's records in all the years for which they have sales.

A getaround would be to use a UNION query: in the SQL window type

SELECT CustNo, Div, Year, Rank, Total FROM tblSales2002
UNION
SELECT CustNo, Div, Year, Rank, Total FROM tblSales2003
UNION
SELECT CustNo, Div, Year, Rank, Total FROM tblSales2004
UNION
SELECT CustNo, Div, Year, Rank, Total FROM tblSales2005


Save this query as uniAllSales and join IT to the customer table.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sub Query Question 2
Update Query Question 2
fConcatChild issues 7
Advanced export to excel file 4
me.requery 3
Monthly Totals Plus YTD by Item 3
can't find the right query 0
matching records 3

Top