Solved Criteria - Referencing Another Field

[monkeyman77]

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!!!!!

 

[Pat Hartman]

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]

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

 

[Pat Hartman]

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.

 

[monkeyman77]

Awesome! Thank you!