• data selection

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » data selection

    Author
    Topic
    WSdanieles
    AskWoody Lounger
    January 18, 2001 at 7:19 am #352020

    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
    Viewing 0 reply threads
    Author
    Replies
    • WSBart
      AskWoody Lounger
      January 18, 2001 at 8:02 am #510856

      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
      • WSdanieles
        AskWoody Lounger
        January 18, 2001 at 1:30 pm #510885

        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
        • WSBart
          AskWoody Lounger
          January 18, 2001 at 1:54 pm #510888

          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
          • WScharlotte
            AskWoody Lounger
            January 18, 2001 at 2:31 pm #510891

            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
            • WSdanieles
              AskWoody Lounger
              January 18, 2001 at 3:59 pm #510901

              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
            • WSJudyJones
              AskWoody Lounger
              January 18, 2001 at 4:20 pm #510904

              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
            • WSkbelesky
              AskWoody Lounger
              January 18, 2001 at 7:58 pm #510929

              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
            • WSdanieles
              AskWoody Lounger
              January 19, 2001 at 7:57 am #511002

              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
            • WSBart
              AskWoody Lounger
              January 18, 2001 at 7:59 pm #510930

              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
    Viewing 0 reply threads
    Reply To: data selection

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.