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

    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
    8,260
    Your query would do the count,
    select ID,count(ID) from table.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,442
    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
    8,260
    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
    41
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,442
    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.

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 - Senior Forums