Crosstab Query Question

[DaveCollins181]

I have two fields:

1) DueDate
2) JobType

I want to create a query or report with Month&Year in the left hand row and JobType in the column header with a count of job types under the headers

Example:
PPM Electrical PPM HVAC PPM Fire Alarm
Apr-2015 15 5 15
May-2015 20 10 6
Jun-2015 21 7 12

I guess I'm looking for a crosstab query but I cannot figure out how to group the date by month in the query? Can anyone explain how to do this in a query or report? It would also be helpful if I could show the results in a bar chart.

I have attached a screenshots of where I am at.

Thanks, David

 

[JHB]

Try the below, (maybe use a ; instead of a ,):

Format([DueDate],"mm-yyyy")

 

[DaveCollins181]

Hi JHB, thanks for the code. I have tried it and it looks like it is working which is great and shows the results I am looking for (See Attached Screenshot). Appreciate your help.

The only thing I have to resolve now is to set a criteria for the DueDate. I only want to find records between two dates. I have tried putting Between [Start] and [End] into the criteria of Due Month: Format([DueDate],"yyyy-mm") but access doesn't like it. Do you know how I could achieve this?

Thanks, David.

 

[DaveCollins181]

Hi JHB, I think I have resolved. I added the following parameters to the crosstab query:

Parameter Data Type
[Begin Date:] Date/Time
[End Date:] Date/Time

I then inserted the [DueDate] field into the query and added the following criteria:

Between [Begin Date:] And [End Date:]

It seems to work. Thanks again for your help.

Regards David.

 

[JHB]

...Thanks again for your help.

You're welcome, good luck.