-----Original Message-----
Let me get this right. Based on your sample data below, what you want
to do is get a query that looks like:
SELECT Code3, Code1, Code2
FROM your Table.
The only I know of to accomplish this is by creating the SQL
dynamically, and it would be significantly easier if your table had
the data in a normalized format. The other advantage of this is that
if your number of codes changes, you won't have to rewrite your
queries.
Once the data is normalized properly, you would create a aggregate
query to do your counts, then do a crosstab query to get the Codes in
columns. The key to getting them in the order you want them is to add
an IN () clause at the end of the PIVOT statment. In this syntax, the
IN clause defines which columns to return, and the order to return
them in. So you might have something that looks like:
Dim strSQL as string, strSQL2 as string
strSQL = 'TRANSFORM Sum(Q.CountOfName) AS SumOfName ' _
& 'SELECT Q.room_id '
& 'FROM Query1 Q '
& 'GROUP BY Q.room_id '
& 'PIVOT Q.event_type_id '
strSQL2 = "SELECT Event_Type_ID from Q ORDER BY CountOfName DESC'
Dim rst as recordset
rst.open strSQL2
strSQL2 = ''
While not rst.eof
strSQL2 = strSQL2 + rst(0) + ','
rst.movenext
wend
rst.close
set rst = nothing
IF LEN(strSQL2) > 1 THEN
strSQL2 = 'IN (' & LEFT(strSQL2, LEN(strSQL2) - 1) & ')'
strSQL = strSQL + strSQL2
ENDIF
-----At this point, create a querydef and use strSQL as the SQL string
for the query.
--
HTH
Dale Fye
I appreciate your help. Unfortunately, one of us does not
understand the other, and I don't know which. Here's an
example of my table fields:
[Name] as text (name of employee)
[Date] as date
[Code1] as number (# of points awarded, 0 = not used)
[Code2] as number
etc
I can use a query to say that IIf([Code1] = 0, 0,1), then
sum the field [Code1] to get number of times used within a
certain time frame ([Date] between..., and no [Name]in
query). I end up with a query that looks like this:
Field name: Code1 Code2 Code3 etc
Value: 26 14 307
Then I need to find out which codes were used most & put
them in order. That's the part where I get stuck.
Thanks again,
April
-----Original Message-----
Seems that your table is un-normalized. Normalized tables allow better
sorting. You can normalize the "look" or the records by using a UNION query.
SELECT FieldA, Code1 as Code, "1" as CodeNum
FROM tblYourTable
UNION ALL
SELECT FieldA, Code2, "2"
FROM tblYourTable
UNION ALL
'etc
--
Duane Hookom
MS Access MVP
.
.
