Solved Criteria - Referencing Another Field (1 Viewer)

monkeyman77

Registered User.
Local time
Today, 00:55
Joined
Nov 30, 2016
Messages
47
Hello, I am trying to create a query that shows that last time a vehicle was serviced but for multiple services.

So the end view would be a matrix that would look like:
Row - Equipment Number
Column - Service Type
Middle - Dates indicating the last time a piece of equipment was serviced for the column indicated service

The attached pictures show what I've been trying to do in the query but I'm not sure I can even do what I am trying.

Please HELP!!!!!
 

Attachments

  • Capture.JPG
    Capture.JPG
    57.8 KB · Views: 259

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:55
Joined
Feb 19, 2002
Messages
42,981
You should probably look at the SQL string to see just how strange this query is.

With the two criteria tests on the same line, they will be AND'd and since the test is for different values for the same field, both can NEVER be true.

Please tell us in words and with a data example what you want to be returned. Are you looking for the max dates for services when some piece of equipment had BOTH services performed. Or maybe EITHER service. Maybe the services need to be performed within a given date range.

If you just want the last service dates for each type by vehicle, you need to us a totals query
Select Equipment_Number, Service_Type, Max(Service_Date) as MaxDT
From Service_Log
Group by Equipment_Number, Service_Type

This will give you a list of all pieces of equipment that have had ANY service and a row for each type of service with the max service date.

If you want them within a date range, add criteria on the Service_Date.
 

monkeyman77

Registered User.
Local time
Today, 00:55
Joined
Nov 30, 2016
Messages
47
Maybe this is something i need to figure out how to do in a form not the query. Your example of does provide me with all the information i need. However, i need the end sure to be able to see the data differently.

Attached is the view i am trying to show the end user
 

Attachments

  • Capture2.JPG
    Capture2.JPG
    53.4 KB · Views: 253

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:55
Joined
Feb 19, 2002
Messages
42,981
Then create a crosstab based on the totals query. Or forget the totals query and just create a crosstab with Max as the option for the date. The crosstab will aggregate the data for you.
 

Users who are viewing this thread

Top Bottom