Count or Sum

[jmarr02s]

I used the following query supplied by Jorn,

SELECT IIf([A_Sub]=15 Or [a_sub]=19, "15 og 19",IIf([A_Sub]=13 Or
[a_sub]=17,"13 og
17",IIf([A_Sub]=16 Or [a_sub]=20,"16 og 20",IIf([A_Sub]=14 Or
[a_sub]=18,"14 og
18",IIf([A_Sub]=9, "9",
IIf([A_Sub]=10,"10",IIf([A_Sub]=11,"11",IIf[A_Sub]=12,"12")))
))))) AS A_SubGruppe, Count(MDSDBA_COMP_ALG.A_Sub) AS Ana_subOfA_Sub
FROM MDSDBA_COMP_ALG
GROUP BY IIf([A_Sub]=15 Or [a_sub]=19,"15 og "19",IIf(A_Sub]=13 Or
[a_sub]=17,"13 og
17",IIf([A_Sub]=16 Or [a_sub]=20,"16 og 20",IIf[A_Sub]=14 Or
[a_sub]=18,"14 og
18",IIf([A_Sub]=9,"9"IIf([A_Sub]=10,"10",IIf([A_Sub]=11,"11",IIf([A_Sub]=12,"12")))
)))));

but can someone tell me based on my error message (which follows) how I
messed up?

Error Message

Extra ) in query expression 'IIf([A_Sub]=15 Or [a_sub]=19, "15 og
19",IIf([A_Sub]=13 Or [a_sub]=17,"13 og
17",IIf([A_Sub]=16 Or [a_sub]=20,"16 og 20",IIf([A_Sub]=14 Or
[a_sub]=18,"14 og
18",IIf([A_Sub]=9, "9",
IIf([A_Sub]=10,"10",IIf([A_Sub]=11,"11",IIf[A_Sub]=12,"12")))
)))))'.

Thanks!,
john

The database I am working with contains a field with as many as 12
different values (09,10,11,12,13,14,15,16,17,18,19,20)

My end user has requested that the number of times these 12 different
values appear in the table be grouped in the following 8 groups (15 or
19), (13 or 17), (16 or 20), (14 or 18), (09), (11), (12), (10)

That is, for example, the end user wants the combined count for the
total number of times (15 or 19) appears in the table.

I created 8 queries for the above 8 groups (15 or 19), (13 or 17), (16
or 20), (14 or 18), (09), (11), (12), (10) though I don't think the
queries for the 4 groups containing two possible values work correctly
or any of them for that matter.
How do I set up my query for these 8 groups?
And especially how do I do this for the 4 groups containing two
possible values?

How do I show, on a report, only the the counts for the number of times

these 8 groupings show in the table?

[Jana]

jmarr:
Rather than this horribly complex SQL, I would do the following:
Create a query based on your MDSDBA_COMP_ALG table.
Add calculated fields to your query like this:
Its19or15: IIF([A_Sub] IN(15,19),1,0)
Its13or17: IIF([A_Sub] IN(13,17),1,0)
Its16or20: IIF([A_Sub] IN(16,20),1,0)
Its14or18: IIF([A_Sub] IN(14,18),1,0)
Its9: IIF([A_Sub] = 9,1,0)
Its10: IIF([A_Sub] = 10,1,0)
Its11: IIF([A_Sub] = 11,1,0)
Its12: IIF([A_Sub] = 12,1,0)

Now, turn on the totalling for your query and change each of these to
'Sum' so it totals up each column. That should get you what you're
looking for.

To understand, look at the first expression. What it does is looks at
the value of your A_Sub field. If the value of A_Sub is 15 or 19, then
you'll get a 1 in the Its19or15 column. Otherwise, you'll get a zero.
Now, by adding up the column Its19or15, you will get a count of the
number of 19s or 15s in your data.

HTH,
Jana