Retrieving proper records for a report

[Steve Martino]

Hello Forum!  I apologize if this post is in the wrong place, but the 'Reports' section did not appear to have a place for just reports.

I am trying to create a report using information from multiple tables.  The relationships are:

 

Customers >-------Equipment >---------Service.

 

I would like the report to show all the customers that meet a certain criteria (the easy part).  When I bring over their related records from Service, I only want to see their most recent 'Annual Tune Up' record, and if there are not any, then their most recent service record, and if they have no service records at all, then it can be blank.

 

Annual Tune Up, is a value in the Service Table, from field 'Reason for Call'.  This field is tied to a value list populated by the static values:

     Annual Tune Up

     Repair

     Service Call

     Installation

     Initial Inspection

     Warranty

 

I tried many things, but what I got the closest with was another TO of Customers linking Customer PK to Service FK and sorting the relationship by Date (descending).  This gave me all the customers, but only the most recent 'Reason For Call' regardless of what type of call it was.

 

If I filter by "Annual Tune Up" I don't get the customers who have never had an annual tune up.

Sorry for the long post.  I enclosed some screenshots of a Service Record, and the report layout.

 

Any help or guidance would be greatly appreciated

Thanks

Steve.

[Lee Smith]

I moved your topic from "Finding & Searching" to "Layouts” because, you primary need is a report.

[eos]

If you have

 

Customers -< Equipment --< Services

 

and sort the relationship between Equipment and Services by serviceType ascending*, date descending, then the first related record (if any) from Services is either the most recent Annual Tune Up, or the most recent service of any other type.

 

* Based on your value list, rather than alphabetically, if that makes a difference.

 

I only want to see their most recent 'Annual Tune Up' record, and if there are not any, then their most recent service record, and if they have no service records at all, then it can be blank.

[Steve Martino]

Sorry bout that Lee...thanks.

Thanks for your reply Oliver.  Unbelievable (Only to me )  I would've never thought it was that simple.  Thanks for your help .

Of course I don't know quite why it works, and I it doesn't appear to change anything else in the database.

Thanks again.......

Steve

Hey, how long you gonna keep calling your skill level 'Intermediate'?