is there such a thing as a fast pivot table? (CAN they byoptimized???)

[Bob]

running access 2k

I have a result table that looks something like this:
* contact_id long
* kit_id long
kit_date date
score_ab number
score_et number
(etc...)

simple really; people take tests at different times, and each "kit"
gets various scores.

I want to do something VERY simple - at least in concept ...
I want to take the most recent 2 kit's from a given person, and
display all the data in a single record.

so - first i have to make a query to give me the kit_id's of the 2
most recent kit's - pivot'ed so both kit_id's are on the same line;
then I need to relate them back to 2 copies of the result table (in
another query).

here's the problem: the query to get the 2 most recent kit's TAKES
FOREVER to run - it currently looks like this:

TRANSFORM First(T_results.kit_id) AS FirstOfkit_id
SELECT T_results.contact_id
FROM T_results
GROUP BY T_results.contact_id
PIVOT DCount("*","T_results","contact_id=" & [contact_id] & " AND
date_entry>= #" & [date_entry] & "#") In (1,2);

I'm sure that someone will tell me that using dcount costs TONS of
time... so I tried to create a query which uses sql to select the
records I want; and then try to pivot that query. This mess looks like
this: (i called this query: Q_last2results)

SELECT v_results.* FROM (
SELECT T_results.contact_id AS cid, T_results.kit_id,
T_results.date_entry AS de FROM T_results
) AS v_results
WHERE (
(SELECT COUNT(*) FROM T_results WHERE contact_id = [v_results.cid] AND
date_entry > [v_results.de])<2
);

Please note that I needed to rename the table & fields so my where
clause didn't get confused, because of course; everything's coming
from the same table. This query works nice & fast!

So then, I create the crosstab that uses the above query as the
source:
TRANSFORM First([Q_last2results].kit_id) AS FirstOfkit_id
SELECT [Q_last2results].cid
FROM Q_last2results
GROUP BY [Q_last2results].cid
PIVOT [Q_last2results].de;

here, wonderful microsoft access tells me that it doesn't know what
the heck [cid] is !!!!
so then i try "backing out my source query" by using
WHERE IN (source query)
this removes the error message, but now this freaking mess of queries
takes longer to run than my original using dcount!!!!!!


THERE's GOT TO BE A BETTER WAY!! (I do not want to do this using
temporary tables)
PS - i've also tried every permutation of TOP 2 you can think of -
needless to say - that's all wasted time....

any ideas?
TIA - Bob

 

[Michel Walsh]

If there is no duplicated couple ( contact_id, kit_date ), then you can
use:


SELECT a.contact_id, a.kit_date
FROM tableName AS a INNER JOIN tableName As b
ON a.contact_id = b.contact_id
AND a.kit_date <= b.kit_date
GROUP BY a.contact_id, a.kit_date
HAVING COUNT(*) <= 2




Have an index on contact_id, and preferably one too on kit_date, to speed
things up, a little bit.


You can then make another query which will join the initial table with the
two fields of this one to get additionnal data, relative to this top 2
kit_date per contact_id, from your table.


The technique used is based on 'ranking' the data, per group (made of
contact_id) and guaranteed to work only if there is no ex-equo (no
duplicated couple). If there are duplicated values, say some contact_id took
three kits at the same date, which two out of these three would have to be
picked, to start with...







Vanderghast, Access MVP

running access 2k

I have a result table that looks something like this:
* contact_id long
* kit_id long
kit_date date
score_ab number
score_et number
(etc...)

simple really; people take tests at different times, and each "kit"
gets various scores.

I want to do something VERY simple - at least in concept ...
I want to take the most recent 2 kit's from a given person, and
display all the data in a single record.

so - first i have to make a query to give me the kit_id's of the 2
most recent kit's - pivot'ed so both kit_id's are on the same line;
then I need to relate them back to 2 copies of the result table (in
another query).

here's the problem: the query to get the 2 most recent kit's TAKES
FOREVER to run - it currently looks like this:

TRANSFORM First(T_results.kit_id) AS FirstOfkit_id
SELECT T_results.contact_id
FROM T_results
GROUP BY T_results.contact_id
PIVOT DCount("*","T_results","contact_id=" & [contact_id] & " AND
date_entry>= #" & [date_entry] & "#") In (1,2);

I'm sure that someone will tell me that using dcount costs TONS of
time... so I tried to create a query which uses sql to select the
records I want; and then try to pivot that query. This mess looks like
this: (i called this query: Q_last2results)

SELECT v_results.* FROM (
SELECT T_results.contact_id AS cid, T_results.kit_id,
T_results.date_entry AS de FROM T_results
) AS v_results
WHERE (
(SELECT COUNT(*) FROM T_results WHERE contact_id = [v_results.cid] AND
date_entry > [v_results.de])<2
);

Please note that I needed to rename the table & fields so my where
clause didn't get confused, because of course; everything's coming
from the same table. This query works nice & fast!

So then, I create the crosstab that uses the above query as the
source:
TRANSFORM First([Q_last2results].kit_id) AS FirstOfkit_id
SELECT [Q_last2results].cid
FROM Q_last2results
GROUP BY [Q_last2results].cid
PIVOT [Q_last2results].de;

here, wonderful microsoft access tells me that it doesn't know what
the heck [cid] is !!!!
so then i try "backing out my source query" by using
WHERE IN (source query)
this removes the error message, but now this freaking mess of queries
takes longer to run than my original using dcount!!!!!!


THERE's GOT TO BE A BETTER WAY!! (I do not want to do this using
temporary tables)
PS - i've also tried every permutation of TOP 2 you can think of -
needless to say - that's all wasted time....

any ideas?
TIA - Bob