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]
