Results 1 to 10 of 10
  1. #1
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107

    Best way to count specific text data types in a query?

    Hi there

    I have created a report whereby an S, E or D is listed under certain headings. I was wondering whats the best way to show the totals of each of these letter choices in a query as a total of all the records. I need to do this for 6 different fields. Also I am hoping to be able to create a chart displaying this information. is this possible? if so any help on how to do this would be great! I managed to do a totals count of all records but it wasn't specific to each option only how many, which is not useful in this case.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I am pretty certain that you can use an Aggregate Query, using the COUNT of SUM functions to get what you want, but exactly how depends on how you want this output to look.
    Can you post a small sample of your data and expected output?

  3. #3
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107
    Hi JoeM - Appreciate your help and input here. Hoping you can totally help me out on this one.

    Attached is a small sample of the data I have in Access and what I want to achieve, thats the totals breakdown and also a graph/chart of those totals.


    Thanks you so much

    SAMPLE DATA.zip

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Unfortunately, I cannot download files from my current location (corporate security policies), so cannot look at that file until I get home tonight.
    I am confident I can help with the query/count part, but really have not done much with graphs or charts in Excel.

  5. #5
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107
    Thanks so much JoeM thats absolutely fine, I really appreciate it. Look forward to seeing the magic can come up with!

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is what the data and wanted/expected results look like. Did not try and paste the chart...

    Data
    StudentName
    Counting Multiplying Subtracting
    Student 1 S S E
    Student 2 S D E
    Student 3 E D S
    Student 4 D D D
    Student 5 E D D
    Student 6 D S D
    Student 7 S S D
    Student 8 S E D
    Student 9 S E D
    Student 10 D E E
    Student 11 E E E
    Student 12 S S S
    Student 13 E D S


    Wanted/Expected Results
    TOTALS
    Counting Multiplying Subtracting
    S 6 4 3
    E 4 4 4
    D 3 5 6

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Thanks Steve! That gives me what I needed.

    So, here is what I would do.

    First, create a one field table named "Options" with a field named "Option".
    Add three records to this table, "S", "E", and "D".

    Now assuming that your table name is "Data" (modify to suit), this aggregate query will give you what you need:
    Code:
    SELECT Options.Option, Sum(IIf([Option]=[Counting],1,0)) AS CountingCount, Sum(IIf([Option]=[Multiplying],1,0)) AS MultiplyingCount, Sum(IIf([Option]=[Subtracting],1,0)) AS SubtractingCount
    FROM Options, Data
    GROUP BY Options.Option;

  8. #8
    ayupchap is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2013
    Posts
    107
    Wow!!! OK this is great. i have managed to create the query!!! I did have to change the name from Data to Strands as thats what I have called it. Thanks so much JoeM and thanks ssanfu. Anyone want to help me tackle the chart part?

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!

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

Similar Threads

  1. Performing a count of specific text in report
    By Bobwords in forum Reports
    Replies: 3
    Last Post: 06-20-2014, 01:35 PM
  2. Replies: 12
    Last Post: 02-25-2014, 08:32 AM
  3. Change Data Types in a Make Table Query
    By nyneave in forum Queries
    Replies: 1
    Last Post: 09-18-2012, 11:46 PM
  4. Replies: 0
    Last Post: 01-19-2012, 11:36 PM
  5. Replies: 3
    Last Post: 12-03-2009, 04:38 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