crosstab counts of dates within months

[bobmac-]

Hi,

I have a table:

app_id
app_date_received
app_date_processed
app_date_accepted
app_date_confirmed

I need to produce a report giving the following:

Number of applications (app_id) received by month
Number of applications (app_id) processed by month
Number of applications (app_id) accepted by month
Number of applications (app_id) confirmed by month

one possibility

................aug2012 sept2012 ......
received........2............0
processed..... 1...........0
accepted........0..........0
confirmed......1...........0

Any help with the query design would be greatly appreciated. I realise it may not even be a crosstab I'm really after

Cheers

 

[Alansidman]

This may be helpful

http://www.datapigtechnologies.com/flashfiles/crosstab.html

 

[bobmac-]

Thanks Alan, I'm still confused on how to handle the rows.
If I just use app_date_received as a row it will give me all the unique dates available which is not what I need. I need a monthly summary of how many applications have a app_date_received in that month

 

[Alansidman]

How about posting your db with sample data. Also what the expected results of your query would look like based upon the data in your sample. In this manner we can give you specifically what you are looking for.

 

[bobmac-]

Hi Alan
I have just worked a way around it.
First I created summary queries for each field i.e. count of applications received, processed, accepted and confirmed
Second, I created a UNION of all four summary queries to get a date field for all possible months
I then created another query using the UNION query and the four summary queries with left outer join on the UNION query

It works
I thank you for your time

 

[Alansidman]

Glad you solved it.