Results 1 to 8 of 8
  1. #1
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84

    How to count/total data within a field


    [professional administration?] field contains choice of three companies; A, B and C. Want to find out how many As,Bs and Cs. thought i might need a quanity so created Expr1:count([professional administration?]). of course shows 1 for each record...not sure where to go...

    thanks,

    John

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    You need a totals query like this:
    Code:
    SELECT tblShoe.Brand, Count(tblShoe.[Brand]) AS CountOfBrand
    FROM tblShoe
    GROUP BY tblShoe.Brand;

  3. #3
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    thanks but not sure how to interpret - could you instruct how to place in query design view?

    Here is what i have.

    Field: Professional Administration?
    Table: 2015qNewCases
    Total: Group By
    Sort: Ascending
    Show (yes)
    Criteria: A or B or C

    Thanks,

    John

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    1st - Remove the criteria.

    2nd - I hope you can change the name of the field to remove the spaces and ? , special characters in field names lead to problems, especially when it's a wildcard character.

    3rd - Add the ProfAdmin field again and in the totals drop down change it to count.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    Thanks.

    Each company (A,B,C) is given a value of 1 in the new field (Count). I'm trying to sum - to find out how many As, Bs and Cs I have.

    here is the youtube: https://www.youtube.com/watch?v=C3Gab5D25o0

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    We'll need to see your actual query - I'm afraid that a youtube video won't help with that

    Open the query you have in the query designer. Right click and select SQL view.
    Copy and paste the SQL of your query here. It's best if you enclose it with the # code tags (above in the editor) to preserve it exactly as copied.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    #SELECT [2015qNewCases].[Professional Administration?], [2015qNewCases].Claimantfirst, [2015qNewCases].Claimantlast, [2015qNewCases].[Date Closed], [2015qNewCases].tContacts_2.CFirstName, [2015qNewCases].tContacts_2.CLastName, [2015qNewCases].tContacts_2.CClient, [2015qNewCases].[Professional Admin Cost], Count([Professional Administration?]) AS Expr1, Sum([Expr1]) AS Expr2
    FROM 2015qNewCases
    GROUP BY [2015qNewCases].[Professional Administration?], [2015qNewCases].Claimantfirst, [2015qNewCases].Claimantlast, [2015qNewCases].[Date Closed], [2015qNewCases].tContacts_2.CFirstName, [2015qNewCases].tContacts_2.CLastName, [2015qNewCases].tContacts_2.CClient, [2015qNewCases].[Professional Admin Cost]
    HAVING ((([2015qNewCases].[Professional Administration?]) Is Not Null))
    ORDER BY [2015qNewCases].[Professional Administration?];#

    thanks

  8. #8
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay try this ;
    Code:
    
    SELECT [2015qNewCases].[Professional Administration?], Count([Professional Administration?]) AS CountOfProfAdmin 
    FROM 2015qNewCases
    WHERE  [2015qNewCases].[Professional Administration?] Is Not Null
    GROUP BY [2015qNewCases].[Professional Administration?]
    ORDER BY [2015qNewCases].[Professional Administration?]
    The reason your query won't work as you expect is that when you group on multiple fields you will get a distinct count of every different record - so in your example for each [2015qNewCases].[Date Closed] it will group and therefore count by the individual dates.

    You really need to change your field naming. If you ever upgraded to SQL server it will reject any or field name that starts with numbers, and the special characters and the spaces
    I would also be worried that you have a table for data for a specific year. You can always identify a records year from a DateEntered type of field.

    Have a google for Naming conventions
    http://access.mvps.org/access/general/gen0012.htm
    And Normalisation of data http://www.fmsinc.com/tpapers/genacc...abasenorm.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 2
    Last Post: 03-06-2016, 04:23 AM
  2. Replies: 2
    Last Post: 04-15-2014, 01:59 PM
  3. Replies: 6
    Last Post: 02-23-2014, 03:54 PM
  4. Count - Sum Total a record is modified
    By mike760534211 in forum Access
    Replies: 6
    Last Post: 01-08-2014, 03:30 PM
  5. total Count
    By Alaska1 in forum Access
    Replies: 2
    Last Post: 12-06-2010, 08:09 PM

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