Totals Query

[justme]

I'm not completely clear on the intricacies of totals queries. I have a
bunch of fields from one table. I want to filter them down to ANY ONE RECORD
with the LATEST EventDate of the BIGGEST FitID number for EACH Style (style
is distinct).


Essentially, I want to just tell the query to find

-ONE record for each STYLE
- with the biggest fitID number

- Then, if there are multiple records with the same FitID number
- find the record with the latest date

- And if there are still multiple records with that latest date, then
- choose ANY one of those records
(I really don't want to aggregate the rest of the fields)


I made a totals query in design view that has

StyleID | FitID | EventDate | EventType | EventStatus

GroupBy: Style
Max: FitID
Max:EventDate
First: EventType
First: EventStatus
First: etc......


However, the information in the columns seem mismatched. Is it grabbing
information for each column, independently of another?

Any aggregate functions I try instead of "First" for the remaining columns
changes the total number of records returned.

The only way I know how to get what I want is to do an aggregate query for
just the first three columns, then do another query on that query, adding the
matching information in the rest of the fields. Is that correct?

Is there any way to do it all in one swoop? How can I get what I am after?

Mucho thanks.

 

[Michel Walsh]

Better to do it in multiple queries.


SELECT styleID, MAX(fitID) as maxFit
FROM tableName
GROUP BY styleID


saved as q1.


SELECT a.styleID, a.fitID, MAX(a.EventDate) AS maxDate
FROM tableNameI AS a INNER JOIN q1
ON a.styleID = q1.styleID AND a.fitID=q1.maxFit
GROUP BY a.styleID, a.fitID


saved as q2.


Finally, you should get it with:


SELECT a.styleID, a.fitID, a.EventDate, FIRST(EventType),
FIRST(EventStatus), FIRST( ... ), ...
FROM tableName As a INNER JOIN q2
ON a.styleID=q2.styleID
AND a.fitID=q2.fitID
AND a.eventDate=q2.maxDate
GROUP BY a.styleID, a.fitID, a.EventDate




Hoping it may help,
Vanderghast, Access MVP

 

[justme]

Michel,

thanks so much for your reply. I was unable to see my post until just now
due to the problem with the forum. What you suggested does make sense to me,
but I have some questions.

1. Am I correct in assuming that the way I was doing it actually was
mismatching column data?

2. Is it also correct to assume that you can generally only use one type of
aggregate function per query?



Unfortunately, I found yesterday that what I need is a actually a little
more complex. Any further help you could offer would be a lifesaver.

It's just a variation of what I had before, but the resulting records will
NOT have a unique key, which completely throws me off, and the books just
don't discuss this type of stuff in much detail:


Again, 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. For each STYLE,
if there are multiples of any FACTORYID (long integer),
the greatest EVENTID will be chosen FOR EACH FACTORYID

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

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

4. 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.



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


Thank you for your help!

 

[Michel Walsh]

Since I don't see exactly what you did before, I can only assume that
indeed, you where caught with a mismatch problem.

For the second point, we can, in general, use more than one type, but here,
given the problem, that is just easier to do it one type per query.

Change q2 to:


SELECT a.styleID, a.fitID, MAX(a.EventDate) AS maxDate, MIN(a,EventDate) AS
minDate
FROM tableNameI AS a INNER JOIN q1
ON a.styleID = q1.styleID AND a.fitID=q1.maxFit
GROUP BY a.styleID, a.fitID



and q3 to:


SELECT a.styleID, a.fitID, a.EventDate, FIRST(EventType),
FIRST(EventStatus), FIRST( ... ), ...
FROM tableName As a INNER JOIN q2
ON a.styleID=q2.styleID
AND a.fitID=q2.fitID
AND (a.eventDate=q2.maxDate
OR
a.eventDate=q2.minDate)

GROUP BY a.styleID, a.fitID, a.EventDate



Hoping it may help,
Vanderghast, Access MVP

 

[justme]

Hi Michel,

I'm confused. Why do you have Max Event Date and Min Event Date in query 2?
Also, Where does the factory come in?

Thanks

 

[Michel Walsh]

LASTEST is the same concept as MAXIMUM applied to a date.


I misread your post. I thought you want and the Max and the Min (latest and
earliest). There was also a typo, should have use a dot, not a coma, in
MIN(a,EventDate). But in fact, the MIN is to be applied to the eventStatus
(not to eventDate), so:

--query2--
SELECT a.styleID, a.fitID, MAX(a.EventDate) AS maxDate,
MIN(a.EVENTSTATUSID ) AS
minStatus
FROM tableNameI AS a INNER JOIN q1
ON a.styleID = q1.styleID AND a.fitID=q1.maxFit
GROUP BY a.styleID, a.fitID



--query3--
SELECT a.styleID, a.fitID, a.EventDate, FIRST(EventType),
FIRST(EventStatus), FIRST( ... ), ...
FROM tableName As a INNER JOIN q2
ON a.styleID=q2.styleID
AND a.fitID=q2.fitID
AND a.eventDate=q2.maxDate
AND a.eventStatusID= q2.minStatusID
GROUP BY a.styleID, a.fitID, a.EventDate



and about factory, I assumed it was the field fitID. Your first post does
not mention any "factory", just some (obscure) field fitID, which I took as
the factoryID you mentioned after.



Vanderghast, Access MVP

 

[justme]

Michel,

Thank you for your reply again. I kind of got caught up in finding a new
apartment to move to. If you are still there, I revised my original question
about half-way down the thread. In that one, I mentioned factory and a whole
different way to run the query. Please take a look. Thanks.