Eliminating dupes in a query

[wizard_chef]

I know how to eliminate dupes in a table by copying the table
structure, making the duplicate fields in the copied table primary
keys, and then copying the table with the dupes into that table.

This works but is time-consuming. Is there any way to eliminate
duplications at the query level????

[By "duplications" I mean selecting a field and specifiying that only
the first instance of that field in the query will be included in the
query output.]

wizard_chef

 

[Jeff Boyce]

Define "first". Tables are "buckets o' data", not necessarily ordered in a
way humans would/could use.

Are you truly looking for one (specific) record, or for each unique
combination? If the latter, you can use the Unique Values property to get
one (query) record per unique combination of fields/columns.

Good luck

Jeff Boyce
<Access MVP>

 

[wizard_chef]

I have a customer DB with purchases of various kinds. I want to have a
query that provides me with the name of anybody who has ever bought
anything. What I get now is a list of *every* purchase. Hence, I'd like
to have, e.g., the customer number to be unique, or only pull that
customer's info out on the first encounter of the query.

Can you tell me how to do that in the SQL query?? Thanks!

Sorry aoubt the lag here. Katrina knocked out our power for a week.
Just got back on line.

wizard_chef

 

[Duane Hookom]

PMFJI but you still haven't told us what you mean by "first".

Picture a school yard full of students. The principal comes out and asks for
the "first" student for each grade level. Does the principal want the
oldest, youngest, tallest, smartest,... Do you know this information about
each student?
School year = Table
Student = Records
Grade Level = Customer
Tallest = Max(Quantity)?
Oldest = Min(OrderDate)?

First of what?

 

[John Spencer (MVP)]

Sounds as if you need to use the distinct clause and you need to NOT SHOW any of
the fields except the Customer number. Something like:

SELECT Distinct CustomerID
FROM tblPurchases

If you are using the query grid.

Field:CustomerID
Table: tblPurchases

Then right-click in the gray area at the top, show properties, and set Unique
Values to Yes (True).

 

[wizard_chef]

I believe John may have the answer, but I haven't given it a try yet.

Duane, I am not communicating here. Here is a specific mini-example:

I have a database with customers in it. There are fields for the
customer name, address, etc., AND a unique customer number (the primary
key) for each customer. Forget the "first" thing I said earlier. I have
other relational tables that show the purchases that a customer may
have made with the item code, the description, the date of purchase,
etc., etc. For example, John Smith may have bought a harmonica last
May, a flute in July, and a waterbed last week.

I want to produce a mail-merged set of names and addresses of all the
customers in my database that have *ever* bought *anything* from me, so
I can send out a sale announcement (for example). Some of my customers
in the database have not made any purchases. Some have made dozens.

When I run a normal query, even if I set Distinct Row in the the sql
code (although I am not a wizard at SQL ... I use the design view and
let Access do the code construction for me ... occasionally I may try
to twiddle with the SQL ... but the results are more often than not
unsuccessful), I will get an individual entry in the query output for
each customer each time they made a purchase. In the example above,
John Smith's name will show up 3 times. I only want one John Smith in
my query output so I won't have 3 address labels and have to manually
go in and throw two labels away (in this example case).

I will try John's suggestion.

Thanks for the input from all.

wizard_chef

 

[wizard_chef]

John, your suggested changes did not work. Here is the SQL code built
in the Design View:

SELECT DISTINCT Customers.ContactFirstName, Customers.ContactLastName,
Customers.EmailAddress, Products.ProductName
FROM Products INNER JOIN ((Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID =
[Order Details].ProductID
GROUP BY Customers.ContactFirstName, Customers.ContactLastName,
Customers.EmailAddress, Products.ProductName, Customers.CustomerID,
Orders.OrderID, Products.ProductID
HAVING (((Products.ProductID) Between 46 And 49))
ORDER BY Customers.ContactLastName;

Any other advice is appreciated.

 

[Duane Hookom]

Why is ProductName in your SELECT and all those other fields in your GROUP
BY? Try this:

SELECT Customers.ContactFirstName, Customers.ContactLastName,
Customers.EmailAddress
FROM Products INNER JOIN ((Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID =
[Order Details].ProductID
GROUP BY Customers.ContactFirstName, Customers.ContactLastName,
Customers.EmailAddress, Customers.CustomerID
WHERE Products.ProductID Between 46 And 49
ORDER BY Customers.ContactLastName;