Hi all,

I have two tables, vehicle and vehicle service. I want to know which vehicles have been into the service department. The vehicle table is linked to the service table via a ”VIN_number”. There are about 5000 vehicles in the vehicle table and each service can be identified by an RO_Number.

All I really want is a list of the 5000 vehicles and a yes or no if they have been in for a service.

My problem at the moment is because a vehicle may have been into the service department multiple times, my report is listing the vehicle and each RO_Number relating to it. This gives me a list of about 15,000 instances of the vehicles.

I only want to identify if the vehicle has been into the service department and not how many times.

I have tried adjusting table joins and currently the vehicle table is connected to the service table via a Left Outer Join, Not enforced with an = link type. As an option to narrow down the numbers I tried adjusting the running total evaluate and reset field options without luck.

Ideally I would like a formula field that only returns the Max RO_Number for each VIN_Number.... but at this stage I have not had any luck... hence this call for help.

Can anyone suggest how my resolve this.

Thanks,

KR

Thanks for that. I REALLY appreciate your help so far.

My criteria changed slightly in that I decided I needed to count the number of times the vehicle has been in as opposed to the yes or no in my previous post.

I now have the following script which increments by 1 each time a particular vehicle has been into the service department after the specified date.

One more issue to fix now is that if the same vehicle has been in twice after the specified date I only want to count it once. At the moment my script counts each and every instance the vehicle has been in. Hence I am trying to count by the max date. Not sure if I am using the correct logic construct, but the existing script below nearly gives me what I need.

Existing Script

WhilePrintingRecords;

NumberVar CountService;

NumberVar CountReport;

If maximum ({RO_Date},{VIN_Number}) > Date (2010, 06, 07) then

(CountService := CountService + 1;

CountReport := CountReport + 1)

A snapshot of the current result looks like the following,

RO Date                  VIN Number                      Count Service

19.04.2010              6FPAAAJG517840                        1

11.06.2010              6FPAAAJG517840                        2

24.08.2010              ABC12345891111                        3

What I am trying to achieve

RO Date                  VIN Number                      Count Service

19.04.2010              6FPAAAJG517840                        0

11.06.2010              6FPAAAJG517840                        1

24.08.2010              ABC12345891111                        2