Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Will 1960 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    22

    Grouping duplicate bames in the colun of a query.

    I have a list of duplicate names in the column of a query and I want to group them in the query (so a name appears only once) rather than a report. How do I do I?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Either make a query,set property Unique values= true
    or,
    make a sum query on name

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use a group by query - in query design view, click on the Epsilon (Totals) button in the ribbon.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Sigma sign,not epsilon.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sigma sign,not epsilon.
    oops! Think I've made that mistake before

  6. #6
    Will 1960 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    22
    Sorry Ajax and Ranman 256 but neither suggestions have worked. Maybe I wasn't clear enough. I have attached a pdf of the screenshot. This shows the results for one days results. I am trying to group together the part name so I just have one entry. So I have one entry per model.
    It would return the following result.
    Model Part name Quantity OK Total of Quantity
    L462 Grille surround 3 15
    Attached Files Attached Files

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    group by model and partname, sum the rest - query would be something like


    SELECT Model, [Part Name],sum(QuantityOK) as SumofQuantityOK, sum([Total of Quantity]) as sumoftotalofquantity
    FROM [qry_weekly report_ppm)
    GROUP BY Model, [Part Name]

    if that doesn't work, post the sql you are actually using

  8. #8
    Will 1960 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    22
    Sorry Ajax I am not familiar with the terminology you have used.
    I have added another screenshot of the design view I have so far with the error message which has been returned.
    Attached Files Attached Files

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your attachment does not have an error message

    1. open a new query
    2. close the 'show table' form
    3. click on the sql view button on the ribbon
    4. copy and paste this code into the sql window

    Code:
    SELECT Model, [Part Name],sum(QuantityOK) as SumofQuantityOK, sum([Total of Quantity]) as sumoftotalofquantity
     FROM [qry_weekly report_ppm)
     GROUP BY  Model, [Part Name]
    5. run the query

  10. #10
    Will 1960 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    22
    Copied and pasted in. When I try to run it I get a syntax error and the word SELECT is highlighted as the error?

  11. #11
    Will 1960 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    22
    Error message also states- Check the subquery's syntax and enclose the subquery in parentheses.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    my guess is you have not removed the default SELECT that was already there, so try again but this time delete any code that is there before you paste

  13. #13
    Will 1960 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    22
    Screenshot attached.
    Attached Files Attached Files

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    that is not a query

  15. #15
    Will 1960 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    22
    SORRY Ajax. I missed out line 3 on your original instructions (click sql button).
    I don't have any experience of that. I'm still learning really.

    Just followed your instructions again and IT WORKS (just needed to change 1 bracket).
    Thanks very much for your help.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. my totals query is not grouping
    By josekreif in forum Queries
    Replies: 3
    Last Post: 09-08-2015, 07:53 AM
  2. Query Grouping Help
    By athyeh in forum Queries
    Replies: 1
    Last Post: 04-15-2014, 09:38 AM
  3. Grouping query with percentage
    By msadiqrajani in forum Queries
    Replies: 1
    Last Post: 03-16-2012, 02:14 PM
  4. typical query on grouping
    By johnbest in forum Access
    Replies: 2
    Last Post: 03-13-2012, 10:36 PM
  5. Grouping query
    By Mphiri in forum Programming
    Replies: 10
    Last Post: 06-15-2010, 08:58 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums