Eliminate Duplicates?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tammy - 12 Jul 2007 13:36 GMT

Good morning.
I have a customer database that I wish to print labels for.
The thing is, I wish to only view unique records. I've got two tables. One
customersTbl names and address. The second of customershistoryTbl. When I
create the query, using from customersTbl, name, address, city, state zip and
from customerhistoryTbl where datebegan is not null I get multiple records.
I only want to see unique - can anybody help?

Reply to this Message

Arvin Meyer [MVP] - 12 Jul 2007 13:42 GMT

You can use an aggregate query (Totals or Group By) and just sort on the
date field and choose Min (which will give you the first date.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Good morning.
> I have a customer database that I wish to print labels for.
[quoted text clipped - 7 lines]
> records.
> I only want to see unique - can anybody help?

Reply to this Message

Tammy - 12 Jul 2007 13:56 GMT

Here is my query

SELECT Customers.ContactFirstName, Customers.ContactLastName,
Customers.BillingAddress, Customers.City, Customers.Zip,
CustomerHistory.[Date Run Began]
FROM Customers INNER JOIN CustomerHistory ON Customers.CustomerID =
CustomerHistory.CustomerID
WHERE (((CustomerHistory.[Date Run Began]) Is Not Null));

I dont understand what you're telling me.
where do i put group by and how do I choose Min?

> You can use an aggregate query (Totals or Group By) and just sort on the
> date field and choose Min (which will give you the first date.
[quoted text clipped - 9 lines]
> > records.
> > I only want to see unique - can anybody help?

Reply to this Message

Andrew - 12 Jul 2007 15:00 GMT

> Here is my query
>
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Try this

SELECT Customers.ContactFirstName, Customers.ContactLastName,
Customers.BillingAddress, Customers.City, Customers.Zip,

MIN(CustomerHistory.[Date Run Began])

FROM Customers INNER JOIN CustomerHistory ON Customers.CustomerID =
CustomerHistory.CustomerID
WHERE (((CustomerHistory.[Date Run Began]) Is Not Null))

GROUP BY
Customers.ContactFirstName, Customers.ContactLastName,
Customers.BillingAddress, Customers.City, Customers.Zip

Regards
Andrew

Reply to this Message

Jeff Boyce - 12 Jul 2007 17:04 GMT

Assuming you are using the query design view, open your query (in design
view), click on the Totals button (the greek sigma character), and use
Minimum instead of Group By for that one field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

> Here is my query
>
[quoted text clipped - 23 lines]
>> > records.
>> > I only want to see unique - can anybody help?

Reply to this Message