Group query but first nor last work on text field

[NeoFax]

I have a problem where I am combining three crosstab queries back
together and grouping the info, but one field may change on a daily
basis. This field has a sequence to it that could help, but am not
certain. The field is status and by progression the letter is L
first, e second, t third and q last. If I use last it does not work
as when I check the underlying table the work was "Q'ed" but the query
shows "L". Currently I made a hack that is fine on say 300 records,
but takes forever for more. I use dlookup to grab the most recent
status in my crosstab query. Here is my SQL code:

SELECT crtbWeeklySAPDeltaOpLvl.HELO, crtbWeeklySAPDeltaOpLvl.STATION,
Avg(crtbWeeklySAPDeltaOpLvl.TotalSpent) AS TotalSAP, Avg
(crtbWeeklyMECAPPWkdDeltaOpLvl.TotalErnd) AS TotalMECAPPErnd, Avg
(crtbWeeklyMECAPPDeltaOpLvl.TotalPlnd) AS TotalAsg, Avg
(crtbWeeklySAPDeltaOpLvl.Mon) AS SAP_Mon, Avg
(crtbWeeklySAPDeltaOpLvl.Tue) AS SAP_Tue, Avg
(crtbWeeklySAPDeltaOpLvl.Wed) AS SAP_Wed, Avg
(crtbWeeklySAPDeltaOpLvl.Thu) AS SAP_Thu, Avg
(crtbWeeklySAPDeltaOpLvl.Fri) AS SAP_Fri, Avg
(crtbWeeklyMECAPPWkdDeltaOpLvl.Mon) AS MECAPP_Mon, Avg
(crtbWeeklyMECAPPWkdDeltaOpLvl.Tue) AS MECAPP_Tue, Avg
(crtbWeeklyMECAPPWkdDeltaOpLvl.Wed) AS MECAPP_Wed, Avg
(crtbWeeklyMECAPPWkdDeltaOpLvl.Thu) AS MECAPP_Thu, Avg
(crtbWeeklyMECAPPWkdDeltaOpLvl.Fri) AS MECAPP_Fri,
crtbWeeklySAPDeltaOpLvl.OP, crtbWeeklySAPDeltaOpLvl.STATUS AS STAT
FROM crtbWeeklyMECAPPDeltaOpLvl INNER JOIN (crtbWeeklySAPDeltaOpLvl
INNER JOIN crtbWeeklyMECAPPWkdDeltaOpLvl ON
(crtbWeeklySAPDeltaOpLvl.OP = crtbWeeklyMECAPPWkdDeltaOpLvl.OP) AND
(crtbWeeklySAPDeltaOpLvl.STATION =
crtbWeeklyMECAPPWkdDeltaOpLvl.STATION) AND
(crtbWeeklySAPDeltaOpLvl.HELO = crtbWeeklyMECAPPWkdDeltaOpLvl.HELO))
ON (crtbWeeklyMECAPPDeltaOpLvl.OP = crtbWeeklyMECAPPWkdDeltaOpLvl.OP)
AND (crtbWeeklyMECAPPDeltaOpLvl.STATION =
crtbWeeklyMECAPPWkdDeltaOpLvl.STATION) AND
(crtbWeeklyMECAPPDeltaOpLvl.HELO = crtbWeeklyMECAPPWkdDeltaOpLvl.HELO)
GROUP BY crtbWeeklySAPDeltaOpLvl.HELO,
crtbWeeklySAPDeltaOpLvl.STATION, crtbWeeklySAPDeltaOpLvl.OP,
crtbWeeklySAPDeltaOpLvl.STATUS
ORDER BY crtbWeeklySAPDeltaOpLvl.HELO,
crtbWeeklySAPDeltaOpLvl.STATION, crtbWeeklySAPDeltaOpLvl.OP;

If anyone could help, I would appreciate it. Thanks!

 

[KARL DEWEY]

If I use last it does not work as when I check the underlying table the
work was "Q'ed" but the query shows "L".

The 'First' and 'Last' functions do not work the way you might expect. It
is the first or last the database encounters.

You probably need Min or Max but 'e' would be Min and 't' Max.
You can create a translation table like this --
Status Sort
L 1
E 2
T 3
Q 4
Then join the translation table to yours on the Status fields and sort on
the Sort field.

 

[NeoFax]

work was "Q'ed" but the query shows "L".

The 'First' and 'Last' functions do not work the way you might expect.  It
is the first or last the database encounters.  

You probably need Min or Max but 'e' would be Min and 't' Max.  
You can create a translation table like this --
Status Sort
   L        1
   E        2
   T        3
   Q        4
Then join the translation table to yours on the Status fields and sort on
the Sort field.
--
KARL DEWEY
Build a little - Test a little







- Show quoted text -

Thanks! I never thought of using that.