Unique Record

[BL]

Hi, There,

I would like to get your asistance to work an more effiicent query. I can
accomplish the job by break the task into several queries and I believe there
should be a better way.

A table contains all the errors experienced by each machine for each type of
errors

OperDate Loc Equip ID Event Start End
20050501 1 1 415 06:00 06:01 (1)
20050501 1 1 415 06:03 06:10 (2)
20050501 1 1 415 06:07 06:10 (3)
20050501 2 1 415 06:03 06:10 (4)
20050501 3 1 416 06:03 06:10 (5)

My purpose is to count the number of error, by types, by machines within a
given date range. However, I notice that due to self checking mechanism of
the machines, the mahcine generated a second (or third) record for the same
event. As a result, record# 2 and 3 are of the same nature and the machine
problem was solved by 6:10. I will be doubly count I do not drop the third
record.

MS Query
For query stuff, I use query to select all the record with the date range.
Then using another query to find out the duplicated one by grouping on
Date/Loc/Equip ID/End time. After then, I have selected the first record of
each duplicated entries. Finally, all records are then combined into one
using the 4th query.

It is cumbersome to write code lke them. Anyay can give me idea? Thank you.

Regards,

Barry

 

[Michel Walsh]

Hi,



Could



TRANSFORM COUNT(*) As theVar
SELECT Loc, EquipID, COUNT(theVar) As TheDistinctCount
FROM myTable
GROUP BY Loc, EquipID
PIVOT End IN(Null)




do the job? Add a WHERE clause, just before the GROUP BY to limit
OperDate, if required, to a value or to a range of values.

I assume you have a limited number of values under "End" ( no more than, say
than around 200 different values... even if I haven't tried for a large
number of different values).




Hoping it may help,
Vanderghast, Access MVP