Count Query Help Please

[joave]

Hi:

Here is a sample of a table I am working with:

Name Field 1 Field 2

Dave 1 1
Dave 1 2
Shirley 2 2
Shirley 1 2
Dave 2 2

I need to be able to count the number of times that Field 1 is 1 for each
Name, the number of times that Field 1 is 2 for each Name, and the same for
Field 2. Here is an example of the query result:


Name Field 1= 1 Field 1 = 2 Field 2 = 1 Field 2 = 2

Dave 2 1 1 2
Shirley 1 1 0 2

I know how to do this when I have the names in the query - I want to be able
to do this when I don't know the names (in other words I don't want to have
to change the query whenever I add a new name).

Any help would be appreciated.

Thank you,

Dave

 

[Duane Hookom]

I expect you could start with a normalizing union query
-- quniSample----
SELECT ASample.Name, ASample.Field1 As TheValue, "Field1" AS Fld
FROM ASample
UNION ALL SELECT ASample.Name, ASample.Field2, "Field2"
FROM ASample;

Then create a crosstab:
TRANSFORM Val(Nz(Count([TheValue]),0)) AS Expr2
SELECT quniSample.Name
FROM quniSample
GROUP BY quniSample.Name
PIVOT [Fld] & [TheValue];

 

[John Spencer]

Or you could use a query that looks like the following.

SELECT [Name]
, Abs(Sum(Field1=1)) as F1_1
, Abs(Sum(Field1=2)) as F1_2
, Abs(Sum(Field2=1)) as F2_1
, Abs(Sum(Field2=2)) as F2_2
FROM [YourTable]
GROUP BY [Name]

If you can't figure out how to do this using the SQL view, post back.

Hints:
View: Totals from the menu
Calculated fields as expressions for the counts
Abs(Sum(Field1=1))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[joave]

John,

This worked perfectly. Duane's way was just a bit more complicated than I
wanted to go for and I didn't need a pivot table.....I knew there was a way
to do it but could not think of it!

Thank you,

Dave

Or you could use a query that looks like the following.

SELECT [Name]
, Abs(Sum(Field1=1)) as F1_1
, Abs(Sum(Field1=2)) as F1_2
, Abs(Sum(Field2=1)) as F2_1
, Abs(Sum(Field2=2)) as F2_2
FROM [YourTable]
GROUP BY [Name]

If you can't figure out how to do this using the SQL view, post back.

Hints:
View: Totals from the menu
Calculated fields as expressions for the counts
Abs(Sum(Field1=1))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I expect you could start with a normalizing union query
-- quniSample----
SELECT ASample.Name, ASample.Field1 As TheValue, "Field1" AS Fld
FROM ASample
UNION ALL SELECT ASample.Name, ASample.Field2, "Field2"
FROM ASample;

Then create a crosstab:
TRANSFORM Val(Nz(Count([TheValue]),0)) AS Expr2
SELECT quniSample.Name
FROM quniSample
GROUP BY quniSample.Name
PIVOT [Fld] & [TheValue];

 

[Duane Hookom]

As long as you have a static number of fields and possible values, John's
solution will be very efficient.

--
Duane Hookom
Microsoft Access MVP

John,

This worked perfectly. Duane's way was just a bit more complicated than I
wanted to go for and I didn't need a pivot table.....I knew there was a way
to do it but could not think of it!

Thank you,

Dave

Or you could use a query that looks like the following.

SELECT [Name]
, Abs(Sum(Field1=1)) as F1_1
, Abs(Sum(Field1=2)) as F1_2
, Abs(Sum(Field2=1)) as F2_1
, Abs(Sum(Field2=2)) as F2_2
FROM [YourTable]
GROUP BY [Name]

If you can't figure out how to do this using the SQL view, post back.

Hints:
View: Totals from the menu
Calculated fields as expressions for the counts
Abs(Sum(Field1=1))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I expect you could start with a normalizing union query
-- quniSample----
SELECT ASample.Name, ASample.Field1 As TheValue, "Field1" AS Fld
FROM ASample
UNION ALL SELECT ASample.Name, ASample.Field2, "Field2"
FROM ASample;

Then create a crosstab:
TRANSFORM Val(Nz(Count([TheValue]),0)) AS Expr2
SELECT quniSample.Name
FROM quniSample
GROUP BY quniSample.Name
PIVOT [Fld] & [TheValue];