eliminating duplicate records in select query

[spence]

I have the following:

tblCustomer
[CustomerID] (key - autonumber)
[CustomerName]

tblPlan
[planID] (key - autonumber)
[CustomerID]

tblInvoice
[invoiceID] (key - autonumber)
[CustomerID]
[InvoiceDate]
[Amount]


There can be multiple plans and multiple invoices per customer.

I am trying to create a query with these three tables to pull the invoices
for a particular date range for a particular customer. Because the invoices
aren't attached to a plan, I am getting duplicate invoices, one for each
[PlanID]. (Example: If Joe Smith has an invoice for $271 in July 2008, and if
he has three different PlanIDs, the query returns $271 three times instead of
just once.) I'm looking for a way for the query to look at only the first
[PlanID] for each customer, thus eliminating my issue with duplicates.
Possible?

Thank you for your assistance,
spence

 

[Allen Browne]

Create a query that returns just the first plan per customer, e.g.:
SELECT CustomerID Min(PlanID) AS MinPlan
FROM tblPlan
GROUP BY CustomerID;

You can now use this query as an input 'table' for your main query, instead
of tblPlan.