Totals Query hard to figure out

[justme]

Hi, this is a re-post of a question not fully solved.

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:


I have records from two tables that are related. I would like to filter
tblFits through a totals query.

tblStyles:
StyleID (PK)
sOther1
sOther2
sOther3
etc...

tblFits
StyleID (FK)
FactoryID
EventID
EventDate
EventStatus
fOther1
fOther2
fOther3
etc...



For Each STYLEID I would like to get the ONE record for each FACTORYID with
the greatest EVENTID number and latest EVENTDATE.



Meaning:

1. For each STYLE, if there is more than one Factory ID, then one of each
FACTORYID (long integer) will be chosen. If there are multiples of any
FACTORYID, the greatest EVENTID will be chosen FOR EACH FACTORYID

2. If there are multiples of an EVENTID (long integer) for any one 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 within each record.

Again, tFits =

STYLE
FACTORYID
EVENTID
EVENTDATE
EVENTSTATUS
fOTHER1
fOTHER2
fOTHER3
ETC...


THANK YOU!!!

 

[Gary Walter]

Hi justme,

The reason you won't find this discussed
in any books is because this is bad design.

Add a unique key to tblFits and this problem
becomes "run of the mill" (and the solution is
probably in one of your books).

If you cannot just add the unique key to the
table, one option is to clear a temp table
that has an autonumber field, then append
your "pre-aggregated" tblFit records to the
temp table,

i.e., start with a totals query where
you group by the 5 pertinent fields and use
FIRST aggregate on remaining fields,
then change to make table query,
run it, then change to append query.

Then go into design of this temp table
and add autonumber primary key field.

Then everytime you need results,
clear temp table,
run append query,
use "easy query" on temp table
that takes advantage of unique key.

Otherwise, I think you are probably
left paying the price with some kind of
"Rank" subquery.

On large data sets ranking is expensive,
especially if you expect to use this ranking
query in further queries -- in fact, most of
my experiences have boiled down to using
(again) a temp table for the ranking to get
optimum performance.

How would it work?

{off top of head -- untested}

SELECT
F.STYLE,
F.FACTORYID,
F.EVENTID,
F.EVENTDATE,
F.EVENTSTATUS,
First(F.fOTHER1) As OTHER1,
First(F.fOTHER2) As OTHER2,
First(F.fOTHER3) As OTHER3
FROM
tblFit
WHERE
(
SELECT Count(*)
FROM
tblFit As t
WHERE
t.STYLE = F.STYLE
AND
t.FACTORYID = F.FACTORYID
AND
(

(
t.EVENTID > F.EVENTID
)
OR
(
t.EVENTID = F.EVENTID
AND
t.EVENTDATE > F.EVENTDATE
)
OR
(
t.EVENTID = F.EVENTID
AND
t.EVENTDATE = F.EVENTDATE
AND
t.EVENTSTATUS < F.EVENTSTATUS
)

)
) = 0
GROUP BY
F.STYLE,
F.FACTORYID,
F.EVENTID,
F.EVENTDATE,
F.EVENTSTATUS;

Note that above still does not handle tie for "4."

OR
(
t.EVENTID = F.EVENTID
AND
t.EVENTDATE = F.EVENTDATE
AND
t.EVENTSTATUS = F.EVENTSTATUS
AND
?????????
)

But, see how expensive it is?

It is going to look *at every record* and
only return that record to be part of a
STYLE/FACTORYID group only if there
are no other records (0) that meet the ties
in the "OR's"

To expect to "continue on" to use the results
of this query in another query will grind things
down to unacceptable performance in most
cases.

My best advice is to add autonumber primary
key to tblFit.

Otherwise, go the temp table route with
the append query.


good luck,

gary

 

[justme]

Hi Gary,

Thank you for your detailed response. Assuming I could add a unique key to
tFits, I don't understand how that solves the problem if it still needs to
group same styles together to then filter them.

 

[Gary Walter]

Thank you for your detailed response. Assuming I could add a unique key
to
tFits, I don't understand how that solves the problem if it still needs to
group same styles together to then filter them.

Hi justme,

I believe you are right (I cannot now imagine what
seemed so clear to me previously).

I made a simple test case "tblFits"

FitID
StyleID
FactoryID
EventID
EventDate
EventStatus
fOther1
1 1 20 30 1/1/2007 1 samestylefactoryideventid_diffdatesStatus1
2 1 20 30 1/2/2007 2 samestylefactoryideventid_diffdatesStatus2
3 1 20 30 1/3/2007 3 samestylefactoryideventid_diffdatesStatus3
4 1 20 30 1/4/2007 4 samestylefactoryideventid_diffdatesStatus4
6 2 20 50 1/2/2007 6 samestylefactoryid_diffeventiddatesStatus6
7 2 20 60 1/3/2007 7 samestylefactoryid_diffeventiddatesStatus7
8 2 20 70 1/4/2007 8 samestylefactoryid_diffeventiddatesStatus8
9 3 20 30 1/1/2007 9 samestylefactoryideventiddates_diffStatus9
10 3 20 30 1/1/2007 10 samestylefactoryideventiddates_diffStatus10
11 3 20 30 1/1/2007 11 samestylefactoryideventiddates_diffStatus11
12 3 20 30 1/1/2007 12 samestylefactoryideventiddates_diffStatus12
13 4 20 30 1/1/2007 13 samestylefactoryideventiddatesStatus13_1st
14 4 20 30 1/1/2007 13 samestylefactoryideventiddatesStatus13_2nd
15 4 20 30 1/1/2007 13 samestylefactoryideventiddatesStatus13_3rd
16 4 20 30 1/1/2007 13 samestylefactoryideventiddatesStatus13_4th

while a version of the original query that did not
involve "FitID" *appeared* to work

SELECT
F.StyleID,
F.FactoryID,
F.EventID,
F.EventDate,
F.EventStatus,
First(F.fOther1) AS OTHER1,
First(F.fOther2) AS OTHER2,
First(F.fOther3) AS OTHER3
FROM tblFits AS F
WHERE (((0)=(SELECT Count(*)
FROM
tblFits As t
WHERE
t.STYLEID = F.STYLEID
AND
t.FACTORYID = F.FACTORYID
AND
(

(
t.EVENTID > F.EVENTID
)
OR
(
t.EVENTID = F.EVENTID
AND
t.EVENTDATE > F.EVENTDATE
)
OR
(
t.EVENTID = F.EVENTID
AND
t.EVENTDATE = F.EVENTDATE
AND
t.EVENTSTATUS < F.EVENTSTATUS
)

)
)))
GROUP BY
F.StyleID,
F.FactoryID,
F.EventID,
F.EventDate,
F.EventStatus;

returning

StyleID
FactoryID
EventID
EventDate
EventStatus
OTHER1
1 20 30 1/4/2007 4 samestylefactoryideventid_diffdatesStatus4
2 20 70 1/4/2007 8 samestylefactoryid_diffeventiddatesStatus8
3 20 30 1/1/2007 9 samestylefactoryideventiddates_diffStatus9
4 20 30 1/1/2007 13 samestylefactoryideventiddatesStatus13_1st

-- the first record came from 4 records
with same style/factoryid/eventid
choosing the record with the latest EventDate
-- the second record came from 3 records
with same style/factoryid
choosing the record with largest EventID
-- the third record came from 3 records
with same style/factoryid/eventid/eventdate
choosing the record with smallest EventStatus
-- the final record came from 3 records
with same style/factoryid/eventid/eventdate/eventstatus
choosing "one of those records"

With limited time to spend on this, I tried to find a
solution that used "FitID" in a more efficient manner
to no avail (maybe I'll try to come back to this if
time allows in the future).

But what was so clear to me previously, I'm afraid
has been lost (or maybe I'm thinking *clearer* now!)

good luck,

gary

 

[justme]

Hi Gary,

Thank you for your time. I will play with it and see if I can get any
closer. I did not even know where to start before, so now I have something
to start with. I will check back periodically to see if you respond again,
but I understand it is a time-consuming matter. If, by chance, you have an
epiphany, by all means, please post! And of course, there's a small chance
someone else will have an answer, too.

Thank you very very much! Have a great day!