I often need counts of related records.  So I set up a calculation on each record to equal 1 (RecordCount1) then in the table where I need to know how many related records there are, I create a field that is Sum(RelatedTable::RecordCount1)

That works well for me.

Sue

On Jan 11, 2012, at 7:55 AM, Peter Buchanan wrote:

> 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