Results 1 to 11 of 11
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    Using Count in MS Access Report


    so am working on this report in my db where the foreign key is track how many times too; against another table and its related field. in A queries Total works jus fine but in Reports I get this Control Source function..... =Count(*)
    but what am trying to get is the persons with the highest to be on top the Report and least at the bottom, tried group-sort in Reports couldn't figure it out ...... could I also build a Criteria say to excluded persons with a lesser amount so they don't appear in the Report

    E.g. I would want persons who foreign key showed up say 3 times or more???? and omit persons with a less Amount.... Right not my db Reports includes everyone and doesn't filter like my Queries
    Last edited by Jamesy_007; 06-30-2020 at 04:13 AM.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Your query would do the count,
    select ID,count(ID) from table.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Could base report on a query that has that static filter criteria or set the criteria with the report OpenReport command.

    Feel free to provide db for analysis.
    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.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    or in the report, at the Sort Group footer, add a text box: =Count(field)

  5. #5
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by June7 View Post
    Could base report on a query that has that static filter criteria or set the criteria with the report OpenReport command.
    Look at ReportHistory am trying to project persons with various offenses .... prefer sort by most Repeaters or most at the TOP least at the bottom;
    as is sort is only by LastName
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Serves no purpose to have sort in query because report sorting and grouping will override.

    Can't have an aggregate function in report Sort criteria.

    If you want the inmates sorted by their number of offences, this will get rather complicated. Consider this query as report RecordSource:

    SELECT tblInmates.Inm_ID, [LastName] & ", " & [FirstName] AS [Names], tblOffences.OffencesName, [tblInmOff Jun].EntryDate, [tblInmOff Jun].Disc, tblInmates.DOB, DateDiff("yyyy",[DOB],Now()) AS Age, tblInmates.Address, tblInmates.Occupation, qCntInmOff.CountOfOff_pd FROM tblOffences INNER JOIN (((SELECT [tblInmOff Jun].Inm_pd, Count([tblInmOff Jun].Off_pd) AS CountOfOff_pd FROM [tblInmOff Jun] GROUP BY [tblInmOff Jun].Inm_pd) AS qCntInmOff INNER JOIN tblInmates ON qCntInmOff.Inm_pd = tblInmates.Inm_ID) INNER JOIN [tblInmOff Jun] ON tblInmates.Inm_ID = [tblInmOff Jun].Inm_pd) ON tblOffences.Off_ID = [tblInmOff Jun].Off_pd;

    Now set a report sort on CountOfOff_pd field.


    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.

  7. #7
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by June7 View Post
    SELECT tblInmates.Inm_ID, [LastName] & ", " & [FirstName] AS [Names], tblOffences.OffencesName, [tblInmOff Jun].EntryDate, [tblInmOff Jun].Disc, tblInmates.DOB, DateDiff("yyyy",[DOB],Now()) AS Age, tblInmates.Address, tblInmates.Occupation, qCntInmOff.CountOfOff_pd FROM tblOffences INNER JOIN (((SELECT [tblInmOff Jun].Inm_pd, Count([tblInmOff Jun].Off_pd) AS CountOfOff_pd FROM [tblInmOff Jun] GROUP BY [tblInmOff Jun].Inm_pd) AS qCntInmOff INNER JOIN tblInmates ON qCntInmOff.Inm_pd = tblInmates.Inm_ID) INNER JOIN [tblInmOff Jun] ON tblInmates.Inm_ID = [tblInmOff Jun].Inm_pd) ON tblOffences.Off_ID = [tblInmOff Jun].Off_pd;

    Now set a report sort on CountOfOff_pd field.
    Thanks June7 this sort of worked.... well!!!.......
    I understand this sql statement above but its not how I want the report to be displayed....
    began to think u can do anything in MS Access once u understand sql and vba....
    Quote Originally Posted by June7 View Post
    Serves no purpose to have sort in query because report sorting and grouping will override.

    Can't have an aggregate function in report Sort criteria.

    If you want the inmates sorted by their number of offences, this will get rather complicated. Consider this query as report RecordSource:

    was trying to count each offenders offences have it displayed their Offences with ">=3" on the report and records with the most count sort "DESC"))) against person names

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not sure I understand. You only want offences committed 3 or more times, like Bachoo has 3 larceny? This means most individuals will be excluded from report.
    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.

  9. #9
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by June7 View Post
    You only want offences committed 3 or more times, like Bachoo has 3 larceny? This means most individuals will be excluded from report.
    YES!...... AS is the sql statement you guided me with shows only the number so then i could criteria that say ">=3" but i want to show also their offences... those who have three or more.....i.e. Habitual Offenders
    once...a pass...shit happens
    twice....a mistake
    three...strikes you're out lol come on you're a repeat offender and i want to be able to report this with your offences. If the db existed a few years ago we'll have persons up to 10 times...Don't want to waist reporting on persons with one or two.... again the objective is to show also their offences.
    am self taught even trying to learn vba... nevertheless cant stress even how much I've learn from you guys in this forum
    Now could get persons with three or more offences to be on the report but it sorts to names... reports everyone in db....i.e. waist of paper and again what we prefer is persons with the largest at the top with their offences showing.... it shows also progression in their crimes

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can either build that filter criteria into the query or use criteria with OpenReport.

    DoCmd.OpenReport "RpHistory", acViewPreview, , "CountOfOff_pd>=3"
    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.

  11. #11
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by June7 View Post
    Can either build that filter criteria into the query or use criteria with OpenReport.
    Ok! Guys,

    There’s a way to aggregate a function say “count” and display such in a report for the purpose of this thread I wanted a report to count the number of times a foreign key showed up in another table, count this and criteria it say “>=3” DESC with greatest to the top and least to the bottom of the report.


    1. So, you first have to create two{2} Make table queries one with the number of times the foreign key showed up creating a column field with this info
    2. Make another select query with the new Make table query and other tables creating a joint and displaying the related info to that foreign keys e.g. “>=3” but in this query, you create or add a Field… PersonID: Val([tblRepeaters]![personID]) using Val function in expression builder.
    3. Builder another Select query with newly created Make table queries [tblRepeaters] and created query displaying the aggregate information by joining this table and query… In building this query you add the aggregate Count as a Field Column. “like a replica ID”
    4. Then take the newly created query and build a report in design using the count column field created as the Group-header in group sort… here it will display in Reports the foreign keys that are greatest and smallest….all the while still sorting names alphabetical meaning if there’s a John Doe and John Roe (7 times); John Doe will show first…. Note their names will show once but their foreign key aggregated with data will show the exact amt. of times…

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

Similar Threads

  1. Distinct Count of Records on an Access Report
    By vinsavant in forum Access
    Replies: 5
    Last Post: 01-14-2018, 08:19 AM
  2. Replies: 3
    Last Post: 01-13-2016, 12:34 PM
  3. Replies: 2
    Last Post: 04-15-2014, 01:59 PM
  4. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  5. =Count in SQL Report
    By Brian62 in forum SQL Server
    Replies: 17
    Last Post: 06-19-2012, 05:15 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