Pull total in report from unassociated query

Posted on 2014-08-08
Last Modified: 2014-08-08
Hi.  I have an equipment/maintenance Tracking database.  I've created a form with 2 unbound fields.  The fields are referenced in a date field on a query to create a date range for a report run.  The report displays/prints all data that meets my criteria within the specified date range.  (All open maintenance due within date range).  I'm being asked for a percentage of open records due within my date range over all open maintenance records.   For instance; In August I have 30 records due but I have 248 open maintenance records total so I need to display the percentage (12%) in the report footer of the existing report.  Best I can think of is usign a subrepoert but this gives me a of of extra paper.  Is there any way to pull the total open count into this report that is already specific to only a range of data?

Question by:valmatic
    LVL 74

    Accepted Solution

    Put a textbox on your Report.

    Set the controlsouce to something like this:



    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    ...also you can provide a criteria to your Dsum...

    But yu may be OK with what I posted above.

    and example of criteria would be:
    =Dsum("Sales","qrySales", "State=" & "'" & YourState & "'")
    ...for a text criteria

    =Dsum("Sales","qrySales", "EmployeeID=" & YourEmployeeID)
    ...for a numerical criteria

    LVL 7

    Author Closing Comment

    Hi Jeff,  I keep forgetting about the "D" commands for totaling.  I used Dcount instead and it worked great.  thanks
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Glad I could help...

    Enjoy your weekend.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now