Counting queries

P

Papa Jonah

I have a query that is based on a table with a bunch of yes/no check boxes.
I want the query to count the number of yes's for each of the y/n fields
and group them by organization. In theory it would look something like this:

on
time QA
Maint 6 2
Eng 3 4
Plan 12 1

Instead, what seems to be happening is the highest number for a given y/n
field is getting populated for each y/n field.

The above example would look like this:

time QA
Maint 6 6
Eng 4 4
Plan 12 12

Here is the SQL (AM is the organization field):
SELECT [zlookup AM].AM, Count(tblChanges.Addition) AS CountOfAddition,
Count(tblChanges.Deletion) AS CountOfDeletion, Count(tblChanges.Reassign) AS
CountOfReassign, Count(tblChanges.Reschedule) AS CountOfReschedule,
Count(tblChanges.ynIA) AS [Internal Assessments], Count(tblChanges.ynEA) AS
[Extenal Assessments], Count(tblChanges.ynshadow) AS Shadows,
Count(tblChanges.ynSA) AS [Self-Assessments]
FROM tblChanges INNER JOIN [zlookup AM] ON tblChanges.txtAM = [zlookup
AM].AMnum
GROUP BY [zlookup AM].AM;

TIA for any suggestions
Papa J
 
K

KARL DEWEY

Try this ---
SELECT [zlookup AM].AM, ABS(SUM(tblChanges.Addition)) AS CountOfAddition,
ABS(SUM(tblChanges.Deletion)) AS CountOfDeletion,
ABS(SUM(tblChanges.Reassign)) AS CountOfReassign,
ABS(SUM(tblChanges.Reschedule)) AS CountOfReschedule,
ABS(SUM(tblChanges.ynIA)) AS [Internal Assessments],
ABS(SUM(tblChanges.ynEA)) AS [Extenal Assessments],
ABS(SUM(tblChanges.ynshadow)) AS Shadows, ABS(SUM(tblChanges.ynSA)) AS
[Self-Assessments]
FROM tblChanges INNER JOIN [zlookup AM] ON tblChanges.txtAM = [zlookup
AM].AMnum
GROUP BY [zlookup AM].AM;
 
J

Jeff Boyce

I can't be sure from your description, but it sounds like you might have a
table with multiple yes/no fields. This might be appropriate if you were
limited to using a spreadsheet, but it will prevent you from getting good
use of Access' relationally-oriented features/functions.

If you provide a bit more specific description, the newsgroup readers may be
able to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

Papa Jonah

That appears to work!
Thanks a million.

KARL DEWEY said:
Try this ---
SELECT [zlookup AM].AM, ABS(SUM(tblChanges.Addition)) AS CountOfAddition,
ABS(SUM(tblChanges.Deletion)) AS CountOfDeletion,
ABS(SUM(tblChanges.Reassign)) AS CountOfReassign,
ABS(SUM(tblChanges.Reschedule)) AS CountOfReschedule,
ABS(SUM(tblChanges.ynIA)) AS [Internal Assessments],
ABS(SUM(tblChanges.ynEA)) AS [Extenal Assessments],
ABS(SUM(tblChanges.ynshadow)) AS Shadows, ABS(SUM(tblChanges.ynSA)) AS
[Self-Assessments]
FROM tblChanges INNER JOIN [zlookup AM] ON tblChanges.txtAM = [zlookup
AM].AMnum
GROUP BY [zlookup AM].AM;

--
KARL DEWEY
Build a little - Test a little


Papa Jonah said:
I have a query that is based on a table with a bunch of yes/no check boxes.
I want the query to count the number of yes's for each of the y/n fields
and group them by organization. In theory it would look something like this:

on
time QA
Maint 6 2
Eng 3 4
Plan 12 1

Instead, what seems to be happening is the highest number for a given y/n
field is getting populated for each y/n field.

The above example would look like this:

time QA
Maint 6 6
Eng 4 4
Plan 12 12

Here is the SQL (AM is the organization field):
SELECT [zlookup AM].AM, Count(tblChanges.Addition) AS CountOfAddition,
Count(tblChanges.Deletion) AS CountOfDeletion, Count(tblChanges.Reassign) AS
CountOfReassign, Count(tblChanges.Reschedule) AS CountOfReschedule,
Count(tblChanges.ynIA) AS [Internal Assessments], Count(tblChanges.ynEA) AS
[Extenal Assessments], Count(tblChanges.ynshadow) AS Shadows,
Count(tblChanges.ynSA) AS [Self-Assessments]
FROM tblChanges INNER JOIN [zlookup AM] ON tblChanges.txtAM = [zlookup
AM].AMnum
GROUP BY [zlookup AM].AM;

TIA for any suggestions
Papa J
Click to expand...
Click to expand...
 
Top