Eliminating dupes in a query

  • Thread starter Thread starter wizard_chef
  • Start date Start date
W

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
 
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
 
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.
 
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;
 
Back
Top