Reports, Queries, and Dates.

[Remdul]

Howdy,

I am attempting to design a database for a local animal shelter to count how many dogs come through in a year, and I am fairly new to the access field. I have 5 queries, each specific to the type of animal... I have Dog, Cat, Kit, Pup, and Other. The SQL code for my 5 queries look like..

SELECT Count([ACO Report]!Type) AS Dogs
FROM [ACO Report]
WHERE ((([ACO Report]![Type]) Like "*Dog*"));

Like "*Dog*" is changed for each specific query. The cat is Like "*Cat*" and so on. These give the output number of how many dogs have come through. Now I added them to a report, to generate the total amount, using a subreport for each of the 5 queries. I have the general information required for this report now... except for one thing. I need to be able to specify how many dogs came through within a certain date...

I have a total of 94 dogs and 48 cats in my current database. However that spans 5 months. I would like to see how many dogs on the same report came through during January, or Jan-Feb.

I have another query that uses the criteria..

Between [First Date:] AND [Second Date:]

That allows me to locate all of the records within that certain date, but I do not know how to combine these 6 queries to give me the desired information. As a summary...

I have 94 dogs in 5 months.
I want to search the first 2 months to tell me I have X dogs during that time.
I want it on the same report.

If this is feasable, could I ask for some assistance in guiding me to the right path for achieving this? My Access 2000 Fundamentals book does not cover this topic. Thank you,

Bryan Elliott

 

[randy700]

How about...

SELECT Type, Count(Type) AS [Pets Processed], Format(Month([DateProcessed]),"mmmm") AS [Month Processed}
FROM [ACO Report]
WHERE DateProcessed Between #[COLOR=red]YourStartDate[/color]# And #[COLOR=red]YourEndDate[/color]#
GROUP BY Type, Format(Month([DateProcessed]),"mmmm")

Then, build a report based on this query.


Randy

 

[Shirley]

Are all the queries created from the same table? Is there a date in the table?

 

[Remdul]

Thank you very much Randy! I am not entirely sure I set it up right though.

SELECT Type, Count(Type) AS [Pets Processed], Format(Month([DateProcessed]),"mmmm") AS [Month Processed]
FROM [ACO Report]
WHERE DateProcessed Between 1/1/2006 And 9/9/2009
GROUP BY Type, Format(Month([DateProcessed]),"mmmm")

The code worked, but returned no results when I put in 1/1/2006 under the Date Processed. Did I not set it up right?

 

[Remdul]

Yes, all the queries are created from the Main Table, ACO Report. It gave me an error when I tried to combine them into on report using the wizard saying that didnt work, so I used subreports.

Bryan

 

[randy700]

You need to enclose your date(s) with hash marks....
#1/1/2006#


Randy

 

[Remdul]

When running this code, Randy, It gives me The type, how many total, and the wrong month?

Cat, 48, December (for 1/1/2006)

Bryan

 

[randy700]

Please post your corrected code.


Randy

 

[Remdul]

SELECT Type, Count(Type) AS [Pets Processed], Format(Month([DateProcessed]),"mmmm") AS [Month Processed]
FROM [ACO Report]
WHERE DateProcessed Between #1/1/2006# And #12/1/2009#
GROUP BY Type, Format(Month([DateProcessed]),"mmmm")


Sorry about that! And thanks very much for your help!

Bryan

Note - I don't have dates in my database up to 2009, does that matter?

 

[randy700]

Doesn't matter. And the sample data?


Randy

 

[Remdul]

Here's some of the data, very small portion on it. If you need more, please let me know.

Type Date of Pickup
Pup 1/1/2006
Dog 1/1/2006
Cat 1/2/2006
Dog 1/2/2006
Dog 1/2/2006
Dog 1/3/2006
Pup 1/3/2006
Dog 1/3/2006
Pup 1/3/2006
Pup 1/3/2006
Pup 1/3/2006
Pup 1/3/2006
Cat 1/30/2006
Dog 1/31/2006
Pup 1/31/2006
Pup 1/31/2006
Cat 2/1/2006
Dog 2/1/2006
Dog 2/2/2004
Pup 2/2/2006
Pup 2/2/2006
Pup 2/2/2006
Pup 2/2/2006

 

[Remdul]

Is there any way to have the report run the query?

Bryan

 

[randy700]

Sorry Remdul... I've been trying to figure this out, but keep getting the same (incorrect) results you are. Apparently, the Format(Month([DateProcessed]),"mmmm") is wrong, but, I haven't found anything yet. I'll keep trying.

Randy

 

[Remdul]

I appreciate you trying very much! I will continue to research this also. It's a very confusing matter, for attempting to get some simple data, hehe. Thank you again,

Bryan

 

[randy700]

If you don't have the solution yet, try this...

Format([Date Processed]),"mmmm")


Randy

 

[Remdul]

It looks that that gave me the same problem as before.. Perhaps I need to change something in the rest of my code to get it to work? Thanks again! You've been very helpful so far Randy!

Bryan