Query to count values in months between 2 dates

[steve.mccall1]

Hi all,

I'm a little stuck with what seems to be (to me anyway) quite a complicated query.

I need to create a report that shows the number of items of each different type per month within two dates selected on the initial access form. e.g.

TYPE                   10/07         11/07        12/07         01/08

Dog                       1               4              5               6  
Cat                        6              1              5               7

I'm assuming I need to create a function to identify which month the date lies in and return a true / false which I can count but I'm not quite sure how to do it.

Any help would be greatly appreciated!

Steve

 

[neileg]

You can use DatePart() or Month() to extract the month. Use an aggregate query based on Count to count the records. Use a crosstab query to render this in tabular form.

 

[steve.mccall1]

Ok,

Thanks for the help.

I've got the stage where I've created a crosstab query but it is not relevent to the date fields I have on the form and is just adding all the items from every Jan together etc.

What expression do I put in the column heading field? The values for the dates are >=[Forms]![General Reports]![Text14] And <=[Forms]![General Reports]![Text16]

Thanks,

Steve

 

[neileg]

Sorry, I don't actually use crosstabs. But if you are getting all years, then you are going to need to add a criterion somewhere to return only the year you want. That's not a problem with the crosstab.