Windows 7 32bit 
Access 2010 32bit
How to get a query to sort the date properly...
DATABASE FUNCTION:
We have a database designed to track the information for our fleet of vehicles, and to keep track of the services that were performed on them. One particular set of vehicles (from the “Department of Public Safety (DPS))” are required to be serviced monthly, while the rest are on an as-needed basis.
To help us accomplish this task we designed a report titled “Service: DPS Due Report”. It tells us which of the DPS vehicles have not been serviced in the last 30 days, 60 days, and 90 days.
What does that mean to a lay person. Well, if you have a newer vehicle you will notice that, if you don't get your oil changed at the right interval your "change oil soon" light will come on. That is designed to remind you that your vehicles' oil is ready to be changed. You then take it to a service station and have the service performed. Once that service has been performed, the service technician will reset your "change oil soon" light so that it starts the cycle over again.
Well, our security vehicles (we have 7 of them) pretty much run 24 hours a day, 7 days a week. Therefore we, the service technicians, need to tell them when they are due for an oil change. We try to service these vehicles every 30 days to keep them in the best condition possible.
When the vehicle comes in for this service, (say Security Vehicle #1 for arguments sake), we enter the service date into our database under that specific vehicles' record.
- If, at that point we run this report, the report will say that Security Vehicle #1's "Status" is "Current", and that the "Date of Last PM Srvc." was on "05/09/2014".
- If we run the report 31 days from today, it will tell us that Security Vehicle #1's "Status" is "Over 30 Days" (from it's last service), and that the "Date of Last PM Srvc." was on "05/09/2014".
- If we run the report 61 days from today it will tell us that Security Vehicle #1's "Status" is "Over 60 Days" (from it's last service), and that the "Date of Last PM Srvc." was on "05/09/2014".
- If we run the report 91 days from today it will tell us that Security Vehicle #1's "Status" is "Over 90 Days" (from it's last service), and that the "Date of Last PM Srvc." was on "05/09/2014".
This report is crucial to us knowing which vehicles are NOT current, and what order (date wise) we need to service them in. (I.E: If Security Vehicle #1 was last serviced on 09/19/13 and Security Vehicle #2 was last serviced on 09/18/13, we need to bring Security Vehicle #2 in for service first, as it has been longer since it's last service.)
Now, to be clear, we have two (2) reports; one for the Department of Public Safety vehicles, and one for all of the rest of them. (FYI - Neither of these reports are functioning properly.)
The strange thing is that the reports were working just fine until sometime early this year. But now they are messing up real bad.
For example, if I run the query that handles these reports, I get the following results:
CUA# Alternate ID MaxOfDate 134 B-50 (Big Ugly) 9/19/2013 137 DPS-6 9/20/2013 149 B-40 9/23/2013 162 B-60 (Little Ugly) 9/24/2013 144 DPS-4 9/24/2013 145 DPS-3 9/25/2013 138 DPS-2 9/25/2013
...but if I look in my "Service" table, I have this in there:
ServiceID CUA# Date Mileage WorkItem SubWorkItem 330 162 5/6/2014 57505 1 Changed Oil & Filter 331 162 5/6/2014 57505 11 Replaced PS parking light bulb 332 162 5/6/2014 57505 11 Replaced license plate bulb 333 162 5/6/2014 57505 4 Replaced front brake pads
That means that CUA# 162 was last serviced on 05/06/2014, but the report is showing that it was last serviced on 09/24/2013.
As you can see, Access is "Sorting" the "Date" column by the first number in the date, and "9" is the highest number (9/24/2013). That's why it keeps showing up as the first one! But when I sort them myself, 5/6/2014 is the MOST CURRENT date!
Any idea how I can get the query to sort the date properly???
Reply With Quote
