How to construct a query for my pivot chart in access?

[munkee]

I cant figure out how to construct a query for my pivot chart in access.

I basically have the following table:

tblCorrective
CorrectiveLinkID - Number - FK
CorrectiveActionID - Autonumber -PK
CorrectiveStartDate - Date
CorrectiveEndDate - Date
CorrectiveActualEndDate - Date
CorrectiveEnteredDate - Date
 

The above table is used to basically monitor corrective actions that are being sent out.

I wanted to produce a pivot chart graph to display the number of corrections to start, corrections to end, corrections that actually ended and corrections entered per month by taking the short date values entered and formatting them as say "Jan - 2011" etc.

My issue is I cant get my head around the query. I am not used to using subqueries but I feel that is the route I need to go down. The real problem though is my graph will typically be y-axis frequency and x-axis Month with 4 seperate series running across for the different dates I want to see.

I am not asking for a complete solution as I can probably get the bulk sorted I am just confused with the adding multiple series and getting some form of common date timeline on the x-axis.

[jbt007]

Munkee,

I am no expert, however my suggestion would to extract some data from your db and build the chart you would 'like' to see in XL. Then you will have a model from which to build your pivot chart. For example, what do you want to see for every CorrectiveStartDate? A Count of CorrectiveLinkID, which would show you how many Corrective items started on that day. Do you want the data grouped by month? (IE: Format(CorrectiveStartDate,"yyyy-mm") Or, is your data day or hour specific in which case you would need to group accordingly. Or are there some values you need to sum or calculate in some way, which would mean you would need to link some other table to the one you show above.

Hope this helps...