Results 1 to 5 of 5
  1. #1
    csiro is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    1

    Summary of records

    Hi

    I am very new to Access, but I have managed to do most of what I want to so far with the aid of a book and the internet. However I am now stumped - I want a report that will summarise all the records in the database and give me totals only of all the people living in each State eg CA 25, VT 5. I have fields ID, lastname, firstname and State, but State is the only field I am interested in.

    I have tried to do this using a query with the Summary Options but it does not work as Access will only accept ID in the Summary Options (probably because it is a number). I have also tried doing it from a report but this does not work either - I can get a total of all the records but not a total for each state.

    Can someone please point me in the right direction?

    Many thanks


    csiro

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So can a person have multiple records? If not, this is a simple aggregate with Count.

    SELECT State, Count(*) AS CountByState FROM tablename GROUP BY State;

    Or build a report and use its Sorting & Grouping features with aggregate calc in textbox.
    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
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IIRC, you can have a textbox in the report (in State group) control source = 1 and set it to running sum over group. Put another textbox in the group footer and set its control source to Sum([name of first textbox]). The running sum textbox doesn't have to be visible once you've vetted the totals.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    AFAIK, aggregate functions cannot reference controls.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Correct. So much for memory. Remove the aggregate function and it should work: = [name of first textbox]

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

Similar Threads

  1. Summary Report
    By balajigade in forum Access
    Replies: 4
    Last Post: 02-05-2016, 09:24 PM
  2. How to get just a summary?
    By Russellh in forum Queries
    Replies: 1
    Last Post: 06-18-2014, 09:06 PM
  3. Summary query
    By dsk96m in forum Queries
    Replies: 3
    Last Post: 02-29-2012, 01:40 PM
  4. Yearly Summary
    By GordonCopestake in forum Queries
    Replies: 2
    Last Post: 05-05-2011, 01:29 AM
  5. How do I do a summary of my records
    By cowboy in forum Access
    Replies: 6
    Last Post: 02-04-2010, 11:58 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