Results 1 to 6 of 6
  1. #1
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93

    ant get total working

    I have a report of truck runs. Each truck run contains a number of items, listed below the truck run. The report always runs yesterday's truck runs, based on the query .



    My problem is, I want the report to put the number of truck runs in the report footer. But, it is counting the number of items in the truck, not the truck runs.

    I have tried every verion of Count, DCount, etc. Nothing is working. Has taken up half of my day. None of the forums I've read are helping.

    Would appreciate any help. BTW, I tried to get total truck runs to calculate in the query, but I just get a bunch of 1's or it totals the truck run numbers (Primary Key)

    Screenshots attached...Click image for larger version. 

Name:	Report.JPG 
Views:	14 
Size:	39.5 KB 
ID:	32456Click image for larger version. 

Name:	Report design.JPG 
Views:	14 
Size:	36.5 KB 
ID:	32457Click image for larger version. 

Name:	Report design 2.JPG 
Views:	14 
Size:	16.1 KB 
ID:	32458

  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,816
    Have to do a query that aggregates the data by job and truck and applies same filter as report.

    SELECT JobID, TruckID, Count(*) AS CountRuns FROM tablename WHERE ... GROUP BY JobID, TruckID

    Options are to join that query to the RecordSource query on the JobID and TruckID fields or use DLookup() domain expression in RecordSource or 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
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    That worked great, thank you.
    My other reports are based on dates ranges and/or by job number. User inputs date ranges, prompted by the query. How do I create a count query based on multiple inputs?

  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,816
    Multiple criteria in the WHERE CONDITION.

    Recommend not to use popup prompts in query. Reference controls on form for user input.
    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
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93

  6. #6
    wolfm is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Dec 2013
    Posts
    93
    I ended up creating a summary query and creating a subreport from it. Put it into the main report.

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

Similar Threads

  1. Sub total and total on invoice form
    By knh2r in forum Access
    Replies: 3
    Last Post: 07-13-2016, 02:09 PM
  2. Sum total on our report is not working
    By Forums10 in forum Queries
    Replies: 2
    Last Post: 04-20-2015, 10:08 AM
  3. Replies: 6
    Last Post: 03-29-2014, 08:47 PM
  4. Replies: 3
    Last Post: 10-17-2012, 09:24 AM
  5. Total Calculation, nz function, not working
    By Jojojo in forum Programming
    Replies: 3
    Last Post: 10-10-2011, 02:33 PM

Tags for this Thread

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