tallying occurrences independently in a query?

[starlingseven via AccessMonster.com]

Hello all -

i am trying to tally results in a query - here's an e.g. of the type of data:

[YesNo] [Qtr1] [Qtr2] [Qtr3] [Qtr4]
Yes A A A A
Yes A B B B
Yes B B B B
No A A A B
No B A A B
No B A B B

what i want to be able to do is to tally the occurrences of A's and B's in
the last four columns grouped on the selection in the first column. i.e.

[Qtr1] [Qtr2] [Qtr3] [Qtr4]
Yes, A 2 1 1 1
Yes, B 1 2 2 2
No, A 1 3 2 0
No, B 2 0 1 3

i've tried all sorts of grouping and counting in the query but to no avail.
anyone have any idea how i would go about doing that?

thanks,

mya

 

[Duane Hookom]

I would first normalize the table with a union query [quniYN_Qtr]

SELECT YesNo, Qtr1 AS AorB, 1 AS Qtr
FROM QueryNoName
UNION ALL
SELECT YesNo, Qtr2, 2
FROM QueryNoName
UNION ALL
SELECT YesNo, Qtr3, 3
FROM QueryNoName
UNION ALL SELECT YesNo, Qtr4, 4
FROM QueryNoName;

Then create a crosstab query based on the union query:

TRANSFORM Val(Nz(Count([YesNo]),0)) AS Expr1
SELECT quniYN_Qtr.YesNo, quniYN_Qtr.AorB
FROM quniYN_Qtr
GROUP BY quniYN_Qtr.YesNo, quniYN_Qtr.AorB
PIVOT quniYN_Qtr.Qtr;

 

[starlingseven via AccessMonster.com]

Horray!

Thank you so so so so so so much!

this looks to be exactly what i needed to solve my quandry - i couldn't get
the AS statements in the union query to work for me (it popped up an error
msg that said 'SELECT statement includes a reserved word or an argument name
that is misspelled or missing or the punctuation is incorrect) - but that's
no biggie - the last column's just named something funny like Expr1002 -

so here's the first part - almost identical to what you suggested - a union
query (btw this is my *first* union query *ever* - i am learning so much from
this forum!):

SELECT YESNO_Key, Qtr1, 1
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr2, 2
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr3, 3
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr4, 4
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr5, 5
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr6, 6
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr7, 7
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr8, 8
FROM _Academic_Disadvantaged_Distribution
UNION ALL SELECT YESNO_Key, Qtr9, 9
FROM _Academic_Disadvantaged_Distribution;

i'm still having some trouble with the crosstab query - it's not liking the
Val(Nz part of it - but i'll fiddle with that on Tuesday - but for now it's
time for me to dream of barbecued cow flesh - yay!

thanks again,

mya

I would first normalize the table with a union query [quniYN_Qtr]

SELECT YesNo, Qtr1 AS AorB, 1 AS Qtr
FROM QueryNoName
UNION ALL
SELECT YesNo, Qtr2, 2
FROM QueryNoName
UNION ALL
SELECT YesNo, Qtr3, 3
FROM QueryNoName
UNION ALL SELECT YesNo, Qtr4, 4
FROM QueryNoName;

Then create a crosstab query based on the union query:

TRANSFORM Val(Nz(Count([YesNo]),0)) AS Expr1
SELECT quniYN_Qtr.YesNo, quniYN_Qtr.AorB
FROM quniYN_Qtr
GROUP BY quniYN_Qtr.YesNo, quniYN_Qtr.AorB
PIVOT quniYN_Qtr.Qtr;

Hello all -

[quoted text clipped - 23 lines]

mya

 

[starlingseven via AccessMonster.com]

Duane:

I fiddled with the queries I had this AM - this is my first delving into SQL,
and I think the SQL queries are starting to make sense - and i figured out
what i was doing wrong when I entered in the queries you suggested last week.
Anyhoo, here's what I ended up with query-wise in the end - it's pretty much
what you suggested -

(union query named "_uQry_Academic_Disadvantaged"

SELECT YESNO_Key, Qtr1 AS Status, 1 AS QTR
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr2, 2
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr3, 3
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr4, 4
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr5, 5
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr6, 6
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr7, 7
FROM _Academic_Disadvantaged_Distribution
UNION ALL
SELECT YESNO_Key, Qtr8, 8
FROM _Academic_Disadvantaged_Distribution
UNION ALL SELECT YESNO_Key, Qtr9, 9
FROM _Academic_Disadvantaged_Distribution;


crosstab query:

TRANSFORM Val(Nz(Count([_uQry_Academic_Disadvantaged].Status),0)) AS
StatusCount
SELECT [_uQry_Academic_Disadvantaged].YESNO_Key, (
[_uQry_Academic_Disadvantaged].Status)
FROM _uQry_Academic_Disadvantaged
GROUP BY [_uQry_Academic_Disadvantaged].Status, [_uQry_Academic_Disadvantaged]
YESNO_Key
PIVOT [_uQry_Academic_Disadvantaged].QTR;


once again, thank you very very much for your help-

mya