Results 1 to 2 of 2
  1. #1
    Khenry458 is offline Novice
    Windows 8 Access 2000
    Join Date
    Apr 2014
    Posts
    1

    Is there a way to count subtotal (not detail) records in a report?

    I need to be able to count subtotals on a report and was able to do this by having a detail query and a summary query. But then I had to have 2 reports one showing the detail records and their calculations and one for the summary. I would like to be able to do this in one report. I had tried using Dcount on the subtotal query in the detail report in a subsection footer but it is still showing me the count of the detail records. Here is some of the fields in my queries that I am working with....

    Query 1 (pulling data all data except for last column) from Transactions Table and last column is from Product Master Table linked on Item No
    ID
    DATE
    LAST NAME
    WORK AREA
    ITEM NO (with 2 parameters for user to key in specific item no up to 2 items no)
    LOT NO
    TOTAL PROCESS TIME (calc. in query from related time fields)
    QUANTITY
    PROCESS
    NO OF BEDS

    Query 2: (pulls data from query 1)
    PROCESS (group by)
    LOT NO (group by)
    SUM OF TOTAL PROCESS TIME (sum)
    SUM OF PROCESS QUANTITY (sum)



    The detail report is grouping by Process (header and footer set to yes) and then by Lot No (header and footer set to yes). It has calculations for the total in the LOT NO footer for PROCESS TIME AND QUANTITY.
    I would like to have a count on the LOT NO subtotals in the PROCESS SECTION but I get a parameter error when I run the report. Another thing I tried is DCOUNT but I am unable to get that working correctly also.
    I was able to get this count in a report running from the summary query but would like the info on one report. I am not sure if a sub report is needed here and how exactly to do it or if there is an equation that will work on the detail report.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You want to count distinct Lot No values for each Process? Don't think will be easy, especially if the report is filtered. I expect will involve subreport or DCount, regardless, will need a query besides the report's RecordSource. The real trick is synchronizing the two datasets for the same filter criteria.

    Bing: access report count distinct value in group

    http://msgroups.net/microsoft.public...e-values/19067
    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. Simple report with master/detail records
    By dsajones in forum Reports
    Replies: 2
    Last Post: 06-21-2012, 04:36 PM
  2. Count records based on report ,not in query
    By Abasalic4 in forum Reports
    Replies: 1
    Last Post: 04-13-2012, 12:46 PM
  3. Count in detail section of a report
    By ccordner in forum Reports
    Replies: 9
    Last Post: 02-08-2012, 06:55 AM
  4. Subtotal Count
    By veritoanimus in forum Queries
    Replies: 3
    Last Post: 12-21-2011, 01:34 PM
  5. Help with subtotal first record in report
    By leslina76 in forum Access
    Replies: 1
    Last Post: 08-25-2011, 01:54 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