Unanswered: Help building a day by day status sql query in Access 2007
I have a database which contains information about a vehicle fleet. It has a table [Vehicle_Status_Table] which has fields [Vehicle_Reg], [Vehicle_Status] and [Date_Status_Started]. The status contains information such as damaged, maintenance, wreck etc.
This table is updated whenever the status of a vehicle changes and so only shows the date the status starts (ie it doesnt record the status of every vehicle every single day)
I am trying to obtain output which would show for each day of a calendar month the total count of vehicles in each status. I can create a query to get the status of each vehicle on a specific day, but am not sure how to do this into a pivot table type output showing the results for each calendar day.
As an example of what I am trying to achieve, if the table has this information:
Vehicle_Reg Vehicle_Status Date_Status_Started
A On Road 01 Nov 12
B On Road 01 Nov 12
C On Road 01 Nov 12
D Maintenance 01 Nov 12
A Damaged 02 Nov 12
D On Road 03 Nov 12
then the output would look like:
Status 01 Nov 12 02 Nov 12 03 Nov 12
On Road 3 2 3
Damaged 0 1 1
Maintenance 1 1 0
I have attached this example in excel if this is easier
Is anyone able to help me with this?
Thank you very much in advance.