Results 1 to 8 of 8

need a count of number of records in a query to appear on report

  1. #1
    nninna is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    4

    Post need a count of number of records in a query to appear on report

    I have built a database in Access 2003 to track the number of requests that come in per month, if they are still pending, when they are completed, etc.

    I have my tables and form all set up and am now creating queries and reports to help me run month-end stats.

    The month end stats that I need to run are:

    Current month request recieved
    current month request completed
    requests from prior month completed this month
    current month open requests
    all open requests

    I have created a seperate query for each of these stats and they all run fine and give me the information I need.

    To make it easier at the end of the month I would like to have one report that gives me all the information I need. Except, I dont necessarily want to see ALL the information from each query..


    I would just like the report to be something like


    End of Month Stats

    Current month request recieved..................................100
    current month request completed................................75
    requests from prior month completed this month............10
    current month open requests......................................25
    all open requests.......................................... ............200


    I do not want to see all the records that each query producd, rather I would just like to see a count of those records.

    Is this possible?

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    Welcome to the forum!

    What you want to do is possible. You will need to create a report that is not bound to a table or query. Just add some textbox controls to the report (one for each statistic you want to show) and then set the textbox's control source to an expression that uses the DCount() function

    Here is an example from one of my applications. This basically counts records returned by the query called qryLabPerfBaseQuery where the dteDateRec is between two dates supplied via a form called frmLabStats


    =DCount("*","qryLabPerfBaseQuery","dteDateRec between #" & [Forms]![frmLabStats].[dteStart] & "# and #" & [Forms]![frmLabStats].[dteEnd] & "#")

  3. #3
    nninna is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    4
    I understand how this should work...but is there a way so that it won't pull the start and end dates from a form?

    I have the queries set up so it asks for a start and end date before the query runs.

    so could I just make the DCount expression...


    =DCount("*","MDL_CurrentMonthRequestsRecieved")



    Thanks for the help!

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    Yes, you could do that but you may have to type the start and end dates for each query that runs which becomes a hassle. Doing it with the form allows you to just enter the dates once. I usually use an unbound form with 2 textbox controls (for the dates) and a command button that opens the report. I think your users would appreciate the form method over having to enter the same dates multiple times.

  5. #5
    nninna is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    4
    For now I would like to just do that, I would like to get it at least somewhat up and running asap but when I just enter

    =DCount("*","MDL_CurrentMonthRequestsRecieved") into my textbox it gives me an error

    Am I missing something?

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    In general it looks OK, but should the query name be MDL_CurrentMonthRequestsReceived?

    You have: MDL_CurrentMonthRequestsRecieved

  7. #7
    nninna is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2010
    Posts
    4
    ah, I'm an idot - I did have it spelled 2 different ways. But, even with the spelling correction it does not work for me. I thought that it was maybe because the query had parameter prompts so I removed those and created a form like you had mentioned early but for some reason it is still not working. I am still not sure what I am doing wrong with this. Thanks for all your help tho.

  8. #8
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    BTW, you are not an idiot; I've pulled out enough of my hair with mistakes like that.

    If you hard code the parameters like the date range in the query itself (such as: WHERE yourdatefield between #01/01/2010# and #01/31/2010#) , will the query return a result? If so, then the problem lies in the way the query is pulling the parameters. If you can post your database (with any sensitive data removed), I can try to troubleshoot it. Sometimes the domain aggregate functions are tricky.

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

Similar Threads

  1. The number of records in a query
    By Persist in forum Programming
    Replies: 3
    Last Post: 07-13-2010, 05:23 AM
  2. Limit number of records in report by group
    By Dr Ennui in forum Reports
    Replies: 0
    Last Post: 06-21-2010, 11:36 PM
  3. Number of Records Found by a Query?
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 03-05-2010, 01:34 PM
  4. Replies: 5
    Last Post: 10-08-2009, 04:15 AM
  5. Replies: 1
    Last Post: 01-31-2009, 08:43 AM

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
  •  
Tech Forums: Microsoft Office Forums