Hi,

I have records from one table that I would like to filter through a totals query. I would like to get ONE record for each FACTORYID for each STYLEID with the greatest EVENTID number and latest EVENTDATE.



Meaning:

1. In the resulting record set, no field would be a unique key

2. For each STYLE,
if there are multiples of any FACTORYID (long integer),
the greatest EVENTID will be chosen FOR EACH FACTORYID

3. If there are multiples of an EVENTID (long integer) for a FACTORYID,
the latest EVENTDATE will be chosen

4. If there are multiples of that EVENTDATE,
the smallest EVENTSTATUSID will be chosen

5. If there are multiples of that EVENTSTATUS (long integer),
it really doesn't matter which EVENTSTATUS


(or any of the other fields) is chosen

There are multiple other fields of all data types.

When I use more than one type of aggregate function in a totals query, the information in each of the columns seem to be mismatched, like a frankenstein record is built, as if each of the Columns' aggregate function is calculated independently of the other.

I'm really not clear on exactly how totals queries work with multiple aggregate functions. Do I have to do multiple queries to get this? How do I get the results am after?

I am been working in design mode.


My fields:
STYLE
FACTORYID
EVENTID
EVENTDATE
EVENTSTATUS
OTHER1
OTHER2
OTHER3
ETC...


Thank you for your help!