Client has a database, containing vehicle registration numbers (plate
numbers), recorded by a network of cameras. So each time a vehicle passes a
camera, its plate is read, and recorded. Data is outputted from camera
software in .csv format, and into Filemaker. Six months' data is retained in
Filemaker, this totals approx 6 million records. The data is recorded in 4
fields : Plate, Date, Time, CameraName

Client requires a reporting function, to show one day's data for camera1
only, with a time range midnight to 6am. Finding these records is easy and
remarkably fast (a few seconds). But client also requires that this found
set then be constrained to show only those plates with at least 2 (or more)
records, for this same date and time range. So if a vehicle moves only once
through camera1 in the date/time specified, drop it. To this found set, he
would want to add any records for these plates, but from the remaining
cameras in the network, as long as they match date and time range.

I created a self join relationship with four match fields :  plate, date,
time_start, time_end.

Then a calculation field : Count(matching records)

Searched for records with Count>1

This takes many minutes, and I can understand why.

There has to be a smarter method?

Any help appreciated, once again.


Regards

Peter