data selection

Topic

New Reply

Hi,

I racking my brains to solve a problem that *should* be simple enough:

I have a database that includes two tables: customers and orders. The orders table includes various products we sell. I would like to select in a query all customers that have ordered product X. That’s simple enough to do: I have a query with both tables, product X is indicated in one of the criteria, and the customers are shown in alphabetical order.

However, this query results in a long list where EVERY order for product X is shown, so if a customer has ordered X several times, his name appears many times on the list.

How do I narrow down the query so that the customer name appears once only?

Any ideas?

Daniele – Italy

Reply | Quote

Replies

Take a look at the SQL of your query.
Make sure that it looks like:

SELECT DISTINCT field1, field2 etc.

Using DISTINT will filter all duplicate rows out of your results.

Bart

Reply | Quote

Bart,

The original SQL gives SELECT DISTINCTROW. I changed it to SELECT DISTINCT but it doesn’t seem to make any difference to the query results, I still get the duplicates.

Daniele

Reply | Quote

There must be a unique field in the query (before the FROM).
Do all fields in two different rows have the same content?

Bart

Reply | Quote

Can you post the SQL for your query? Just copy it from the SQL view and paste it into a message. Then we can see exactly what you’re doing and may be able to help you fix it.

Reply | Quote

Here is the SQL statement:

———————————————————–
SELECT DISTINCTROW Clienti.Ditta, Clienti.Email, Clienti.Titolo, Clienti.Nome, Clienti.Cognome, [Dettagli ordini].IDProdotto, [Dettagli ordini].[Dettagli prodotto]
FROM (Clienti INNER JOIN Ordini ON Clienti.Ditta = Ordini.Ditta) INNER JOIN [Dettagli ordini] ON Ordini.IDOrdine = [Dettagli ordini].IDOrdine
WHERE ((([Dettagli ordini].IDProdotto)=”Dino”) AND (([Dettagli ordini].[Dettagli prodotto]) Not Like “*28mm/410*”))
ORDER BY Clienti.Ditta;

———————————————————

The field names are in Italian, I hope you understand what I am trying to do. I have THREE tables in my query, not two as I stated in my original post: Customers, Orders and Order details. There is a one-to-many relationship between the Customers and Order tables, and another one-to-many relationship linking the Order and Order Details table.

I want to extract Company, E-mail, Title, Name and Surname from the Customer table, selecting these from customers who have bought a particular product ID (Dino), but NOT in a specific size (“28mm/410”).

But as some customers have bought this product ID several times, the query result lists the same company many times over. How can I get rid of the duplicates?

Thanks for any help.

Daniele
Italy

Reply | Quote

It sounds to me that what you want is best handled through displaying the data in your query through a report so that you could group on your customer and then display the customer info once in the group heading. All the orders would then appear in the detail band under the customer to which they pertain.

Reply | Quote

Daniele,
Perhaps you should group the data rather than selecting distinct rows.

SELECT  Clienti.Ditta, Clienti.Email, Clienti.Titolo, Clienti.Nome, Clienti.Cognome

FROM (Clienti INNER JOIN Ordini ON Clienti.Ditta = Ordini.Ditta) INNER JOIN [Dettagli ordini] ON Ordini.IDOrdine = [Dettagli ordini].IDOrdine

WHERE ((([Dettagli ordini].IDProdotto)="Dino") AND (([Dettagli ordini].[Dettagli prodotto]) Not Like "*28mm/410*"))

GROUP BY Clienti.Ditta, Clienti.Email, Clienti.Titolo, Clienti.Nome, Clienti.Cognome

ORDER BY Clienti.Ditta;

The “GROUP BY” appears in the SQL when you use the summarization option (the sigma icon) in the query design grid. In the “Totals” row, use the “Where” option instead of “Group By” for
Dettagli ordini.IDProdotto=”Dino” and
Dettagli ordini.Dettagli prodotto Not Like “*28mm/410*”. Doing so will uncheck the “Show” box.

That will give you unique rows.

hth

Reply | Quote

Yes, that’s it! Thanks a bunch everyone, you’re great! I should have grouped the data, and this gives the correct result without duplicates.

I worked out why selecting distinct rows did not work: there were still duplicate customers as the query was selecting different versions of the same product ID, listing them once each per customer.

I worked around the problem by creating a subquery, but the real solution is grouping the data.

Thanks again!

Daniele
Italy

Reply | Quote

OK I want to suggest a couple of changes to your query.
First replace the DISTINCTROW by DISTINCT.

Show only the fields you want to see, and no more. Your query looks like this:

SELECT DISTINCT field1, field2, field3 FROM ….
After FROM put only those tables you need for the fields you want to see and the ID’s from your subselects (see further).

Now the WHERE clause, use subqueries here.
A subquery looks like this:
SELECT DISTINCT field1, field2, field3 FROM ….
WHERE ID IN (SELECT ID FROM table1 etc WHERE field1 LIKE etc.)

You can store subqueries in seperate queries.

I think if you do all this, your query looks a lot different and it will work.

Success.

Bart

Reply | Quote