Results 1 to 10 of 10

Thread: data selection

  1. #1
    New Lounger
    Join Date
    Aug 2004
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    data selection

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data selection

    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

  3. #3
    New Lounger
    Join Date
    Aug 2004
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data selection

    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

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data selection

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

    Bart

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: data selection

    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.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Aug 2004
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data selection

    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

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data selection

    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  8. #8
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data selection

    Daniele,
    Perhaps you should group the data rather than selecting distinct rows.
    <pre>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;
    </pre>


    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

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data selection

    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

  10. #10
    New Lounger
    Join Date
    Aug 2004
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data selection

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •