How to count Data Value by Date (Each month/each year)?

[YasanthaBandula]

Hi. i am Yasantha.. I have a Access 2007 database.. This is fields.. Child ID,Child Name, DOB, Admission date, Departure date .. So i want a create query for get count values in .How many Children Admit in Each month, Each Year, use "Admission date".???

Ex. "Child ID" "Child Name" "Admission Date"

A001 , Yasantha , 10/10/2000
A002 , Amal , 21/01/1999
A003 , Danial , 21/10/2000
A004 , Nadiya , 15/04/2000

Summery : Year 2000 Admit " 3 Children "
Month October Admit" 2 Children "

[zmbd]

YasanthaBandula , Welcome to Bytes!

This would be something best handled in an aggregate or cross-tab query. What you'll find for the aggregate query is that you'll need one such query for each grouping level (one for months and one for years, etc...). And then there is the crosstab query that should be able to handle the same thing... I would think in one query.

As for the date to use depends on what you want... the admit date would ofcourse show you how many were admitted with the time frame where as the discharge would tell you how many left during that time frame.

You might consider reading thru Aggregate Query Woes as some of the pitfalls are pointed out here.

Here's Mr. Brown's site... a ton of tips so I'd bookmark this site! However, in this case the cross-tab: Crosstab query techniques

Some basic SQL stuff - and a mention of the cross-tab which is why I place this here: Queries in Access using SQL View

Another mention of the cross-tab; however, this will help with some with the report side: Dynamic Reports

That should be enough to get you started. Rabbit, Neopa, and/or others should be along shortly (if they haven't already posted while I was typing this ... they type faster than I do :-P )

Once you get your query/report put together, should you still be having issues, please read the FAQ and posting guidelines about how to post your SQL/Code and will take another poke at the problem.

[NeoPa]

In a query you can aggregate across months or years, but not both. That can, if necessary, be handled in a report. As your question doesn't indicate exactly what you want here it's hard to answer directly.

On the other hand, Z has provided already all the background information you could hope for for such a situation.

As an example though, you might want something like the following if GROUPing BY Year :

SELECT   Year([Admission Date]) AS [Year]
       , Count(*) AS NumAdmissions
FROM     [tblAdmission]
GROUP BY Year([Admission Date])