Results 1 to 12 of 12
  1. #1
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35

    Count Function Help

    I have a table that has the following information



    HdrHID = unique ID
    ActionCode = this is a variable column that has a specific set of data being either new, acctgrev, or complete

    Each header ID can have up to three lines being 1 for each code

    I have a query that pulls all records with the acctgrev code

    What I am wanting is for the count function to provide a count for every HdrHID where ActionCode = acctgrev, the desired result would either be 1 or 0. I can't seem to get this to work, everytime I use the count function, it provides the total count of records in the table.

    Any help on this would be fantastic.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    How/where are you displaying this value? One way would be to create a grouped query and use Count in the total line.

  3. #3
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    Sorry, in the query, I have created an ActionCount column that would be to display the count, I have also limited the the query to display only those records that ActionCode = 'AcctgRev'.

    SELECT [#tmp_DeckIncident].ActionCode, [#tmp_DeckIncident].DeckRevPropHID, Count(*) AS ActionCount
    FROM [#tmp_DeckIncident]
    GROUP BY [#tmp_DeckIncident].ActionCode, [#tmp_DeckIncident].DeckRevPropHID
    HAVING ((([#tmp_DeckIncident].ActionCode)='acctgrev'));

  4. #4
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    When I use this query, I get no data at all

    SELECT COUNT (ACTIONCODE) AS ACTIONCOUNT, DECKREVPROPHID, ACTIONCODE
    FROM [#TMP_DECKINCIDENT]
    WHERE ACTIONCODE LIKE "ACCTGREV"
    GROUP BY DECKREVPROPHID, ACTIONCODE

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Instead of the *, use the field name DeckRevPropHID

  6. #6
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    I think that gets me closer, I have some anomalies where some records have more than 1, but I am double checking the table data

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Select deckrevprophid, count(deckrevprophid) as actioncount
    from [#tmp_deckincident]
    group by deckrevprophid
    having actioncode="acctgrev";

  8. #8
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    That did it, thank you for the help

  9. #9
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    this works, but I think I am still missing something. I also need it to count/display 0 count if there is a deckrevprophid that does not have the acctgrev code, can I put a formula within the count function and remove the 'Having' statement

    SELECT [#tmp_DeckIncident].ActionCode, [#tmp_DeckIncident].DeckRevPropHID, Count(deckrevprophid) AS ActionCount
    FROM [#tmp_DeckIncident]
    GROUP BY [#tmp_DeckIncident].ActionCode, [#tmp_DeckIncident].DeckRevPropHID
    HAVING ((([#tmp_DeckIncident].ActionCode)='acctgrev'));

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You will need two queries for this:
    q1- grouped, count of deckrevprophid where action code = accrqrev
    q2 - #tmp table LEFT joined to q1 displaying the deckrevprophid from #tmp and the count from q1 - this query also needs to be grouped

  11. #11
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    Thank you, I think I was trying to make it too difficult, is there anywhere that can convert MySQL code in ACCESS? This is step 9 of a process that is running that we are trying to get to display in access

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I know nothing about MySQL! Create a new thread with your question (or wait for someone to see this).

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

Similar Threads

  1. Count(*) function
    By FJM in forum Access
    Replies: 18
    Last Post: 09-13-2013, 07:47 PM
  2. COUNT function help needed
    By Lynn Cohen in forum Access
    Replies: 4
    Last Post: 08-12-2013, 12:52 PM
  3. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  4. Count + between + parameter function
    By teirrah1995 in forum Queries
    Replies: 3
    Last Post: 08-11-2011, 10:25 AM
  5. Query using count function
    By wasim_sono in forum Queries
    Replies: 0
    Last Post: 11-28-2007, 03:16 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