Query Results to a Report

[Scatman]

I am somewhat new to Access and I have a problem. I would like to input a starting date and an ending date and have my query give me a count of records for a few fields for work done between those dates. This is like a monthly report kind of thing. I have created independant queries that give me the counts that I am looking for, however, I cannot seem to get a report or a form to show me the values the queries generated. I know there has to be a better way of doing this than running six seperate queries. Here is the way the data is structured:

Fields
Preliminary_Record_Date

Preliminary_Review_Date

Final_Review_Date

I have 3 more fields that I need to show counts on, but if someone could show me how to setup the ones I have shown here, I can probably manage the others. I would like to be able to get the counts I need from one query if possible. I can then setup the report to get the values I need from the one query.

There are other fields in the table that are not date fields such as job_number that I have used to get my counts from the individual queries.

Thanks for any help you can give me

 

[WayneRyan]

Scat,

Create a form where the parameters can be entered. Then
have the query(s) reference the fields by:

Forms![TheNewForm]![StartDate]

and the Report can reference them in the same way.

Then have the new form launch the report with a command
button.

Wayne

 

[Scatman]

Thanks for the help

Wayne,

Thanks for your help. It turns out I was not too far off track. I slightly modified one of my queries for the individual fields to include a piece of your response and it worked. I am going to include what I did here in case anyone else is having a similar problem.

The query is set up to show a COUNT of the 6 individual date fields.
I setup two expressions that rely on a Form for the inputs to run the query.
The expressions are as follows:
Expr1: Month([Forms]![YourFormName]![Month])
Expr2: Year([Forms]![YourFormName]![Year])

The Form is set up with 2 unbound text boxes(one for month, and one for year) and includes a button(as per your advice) to run the Report which is based on the query. I have the form running the query once the year has been entered.

Once again, thanks for your help. This place is one of the greatest sites on the web and I sincerely mean that.

Scat

 

[Scatman]

Spoke too Soon

Looks like I spoke too soon...when I try to run the report for an individual month, it does not give me the counts I need for that month, but instead gives me a total count of everything entered thus far. How can I make this only give me a count for the month I am looking for?

The Inputs on the form do not seem to make any difference no matter what month you enter.

 

[Scatman]

Count and Date Criteria

Ok Pat,

I fixed the query running prior to the report problem. And I have tried just about everything I can in the query to get the counts I need for the dates I want to specify. I have even tried to run the query by just inputting "Between [Enter Start Date] And [Enter End Date]" in the Criteria box and it tells me "The expression is typed incorrectly or it is too complex to be evaluated. Try simplifying the expression by assigning parts of the expression to variables.

Can you NOT get a count of the number of records based on a date field for a specified date range or month? Their are other fields in the table that I could point to, but how would the query know whether they were in the date range that I want?

This doesn't seem impossible to me, but I can't seem to make it work.

Any input on this will be much appreciated.

Thanks

 

[Scatman]

Count and Date Criteria

Ok Pat,

I fixed the query running prior to the report problem. And I have tried just about everything I can in the query to get the counts I need for the dates I want to specify. I have even tried to run the query by just inputting "Between [Enter Start Date] And [Enter End Date]" in the Criteria box and it tells me "The expression is typed incorrectly or it is too complex to be evaluated. Try simplifying the expression by assigning parts of the expression to variables.

Can you NOT get a count of the number of records based on a date field for a specified date range or month? Their are other fields in the table that I could point to, but how would the query know whether they were in the date range that I want?

This doesn't seem impossible to me, but I can't seem to make it work.

Any input on this will be much appreciated.

Thanks

 

[Scatman]

As You Requested

This is the SQL that is in the query. As it is right now, If I input a month and year, I get the correct count for the first field. The other fields produce a count, but it is not correct and I dont know what to put in the criteria to make the other fields work.

SELECT Count(tblReviews.Preliminary_Records_Date) AS CountOfPreliminary_Records_Date, Count(tblReviews.Preliminary_Review_Date) AS CountOfPreliminary_Review_Date, Count(tblReviews.Final_Review_Date) AS CountOfFinal_Review_Date, Count(tblReviews.Final_Review_2_Date) AS CountOfFinal_Review_2_Date, Count(tblReviews.Final_Review_Date_3) AS CountOfFinal_Review_Date_3, Count(tblReviews.Final_Review_Approved_Date) AS CountOfFinal_Review_Approved_Date
FROM tblReviews
HAVING ((Month([tblReviews]![Preliminary_Records_Date])=[Please enter the month] And Year([tblReviews]![Preliminary_Records_Date])=[Please enter the Year]));

 

[Scatman]

Still did not work

I changed the having statement to where and I get the exact same results. I get an accurate count of the Preliminary Record Date Field, but the other counts are wrong. Someone at my office suggested running 6 seperate queries to get the counts and then running a query that ties them all together to get the results I am looking for. I had six queries that gave me the counts at one time, but thought there had to be a way to get my results from ONE query.

What are your thoughts? Can I make this work as one query or should I go back to the six?

 

[Scatman]

Problem Resolved

I re-created my six queries to give me the counts I need...One query for each individual field and made a seventh query(qryMonthlyReport) that is setup to display the counts for each of the six queries. I then made a report based on the qryMonthlyReport. So now when I run my report, It asks me for a Month and a Year and Displays the numbers I need.

Thanks to everyone for your help on this. I know that somehow there HAS to be a way to do this without creating all of those queries, and should anyone have any further input on it, I'd love to hear your idea(s).

So for now, I am getting the results I need and I'll live with the mass of queries I have *g*

Thanks Again