Results 1 to 13 of 13
  1. #1
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340

    Modifying Count Query

    This is a query the wizard made
    Code:
    SELECT First(Query23.[Path]) AS [Path Field], Count(Query23.[Path]) AS NumberOfDups
    FROM Query23
    GROUP BY Query23.[Path]
    HAVING (((Count(Query23.[Path]))>1))
    I want to add "Where Field2" = 4



    But whatever changes I attempt result in an error. How might I resolve that ?
    Also why is "First" there? Is it some kind of Access command ?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you try to make that edit in the query designer grid?

    What error?

    First() is an SQL aggregate function. Suggest you research aggregate functions: Sum, Avg, Max, Min, First, Last, Count, StDev
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340
    The error changed depending on what I tried and I couldn't get anything to work. I was using the sql query window and trying SELECT and WHERE.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Which wizard did you use? - what are you trying to do? What is the error? What attempts did you try?

    I cannot believe the sql you have provided was generated by a wizard - if only because it would not have created a name such as [Path Field], it would have been [FirstOfPath], and you are grouping on a field that has not been selected

  5. #5
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340
    I can assure you Mr CJ, it was. I do not know how else to do it. (A count query, that is).
    And I think it was the Find Duplicates wizard.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Suggest run the wizard again, there are 2 options. Then post the sql again

  7. #7
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340
    Not sure about 2 options but did this

    Create - Query Wizard
    Find Duplicates Query Wizard - OK
    Select Query 23 - Next
    Which fields Value - Path
    Show fields in addition - None, Next
    Finish
    A query named 'Find duplicates for Query23' already exists. Do you want to overwrite this query?
    Yes


    Show Query - r-click sql view


    Code:
    SELECT First(Query23.[Path]) AS [Path Field], Count(Query23.[Path]) AS NumberOfDups
    FROM Query23
    GROUP BY Query23.[Path]
    HAVING (((Count(Query23.[Path]))>1));
    Pretty sure that's identical to the first.

    BTW This forum doesn't work in Firefox 104.0.2 (64—bit), assume that's known ?

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    my apologies - just surprised at that naming convention

    try

    SELECT First(Query23.[Path]) AS [Path Field], Count(Query23.[Path]) AS NumberOfDups
    FROM Query23
    WHERE Field2=4
    GROUP BY Query23.[Path]
    HAVING (((Count(Query23.[Path]))>1))

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    BTW This forum doesn't work in Firefox 104.0.2 (64—bit), assume that's known ?
    Can you elaborate? Same version, same bitness here & I have not noticed any issues.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340
    Thank you @CJ That is working perfectly and is very useful.

  11. #11
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340

    Firefox Problem

    @Micron Screenshot attached. Although I can click on the links the main screen doesn't change. You can't actually go anywhere.
    It is okay in Chrome. I haven't had this problem in FF with anything else.
    Attached Thumbnails Attached Thumbnails forumss.jpg  

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Unless it's because you're not logged in, I have no idea what your problem could be. I'm not experiencing any issues, so I venture to say that the fault doesn't seem to be Firefox - at least when logged in.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    @Micron No change if I log in. A bit odd isn't it? Oh well... must be just me. I can use Chrome OK.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Modifying export query
    By ittechguy in forum Queries
    Replies: 1
    Last Post: 11-09-2015, 11:35 PM
  2. help! need help modifying a query
    By BigDan in forum Access
    Replies: 16
    Last Post: 05-28-2013, 03:58 PM
  3. Need help modifying query
    By redwagontoy in forum Queries
    Replies: 13
    Last Post: 04-30-2013, 03:25 PM
  4. Need help Modifying a query expression
    By leslina76 in forum Queries
    Replies: 3
    Last Post: 04-02-2011, 08:45 AM
  5. Modifying Update Query
    By James Elvin in forum Queries
    Replies: 0
    Last Post: 10-14-2008, 09:07 AM

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