Grouping Records in a Query

[Julie]

I have a query with two fields. I want to make it so that there is only one
CaseNumberKey listed with an associated DispositionSeverityCode. What I want
to do is similar to setting the DispositionSeverityCode = Max, but instead I
want to force the query to select down a list. First select FEL, and if that
doesn't exist then GMD, then MSD, then PMD.

Here's the SQL for what I have so far using the Max option. Any ideas on
how I can force it to select from a list I write (FEL, GMD, MSD, PMD)?

SELECT qryMNCIS27JVdispositions.caseNumberKey,
Max(qryMNCIS27JVdispositions.DispositionSeverityCode) AS
MaxOfDispositionSeverityCode
FROM qryMNCIS27JVdispositions
GROUP BY qryMNCIS27JVdispositions.caseNumberKey;

 

[vanderghast]

use MIN, since, as strings "FEL" < "GMD" < "MSD" < "PMD", and thus, you
really look for the minimum 'string' the group may own (min as in the first
string to appear as ordered in a dictionnary).


Otherwise, you could have define a table with the proper order. Assume you
want "ZUG" first, then "FEL", then ...


Who myOrder
ZUG 1
FEL 2
GMD 3
....


What is left is to make an inner join, with that table and your original
table, and pick MIN(myOrder).

You can then retranslate back that numerical value into its alphanumerical
name with a DLookup, or otherwise (like using another query, implying an
inner join to make the translation).


Vanderghast, Access MVP