Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2012

    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.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts