This Puzzle Should Be Easy...Maybe

[TForward]

All,

I have an Access 2000 database at work that contains quality control data
for written reports which are released to our customers. It's a flat
table with columns like:

ReportWriter - person writing the report
ReportDate - when the report was released
FormatCorrect? - y/n, was the format correct
FormatErrors? - the number of formatting errors
GrammCorrect? - y/n, was grammar correct
GrammErrors? - the number of grammar errors
SpellCorrect? - y/n, was everything spelled right
SpellErrors? - the number of spelling errors

There are 12-ish categories for QC and some misc other fields, but I
won't list them all. I'm trying to use a totals-query to first, group
all records by ReportWriter, then get the sum of errors in each column
for each ReportWriter, and finally get the count of all "y" values in all
the y/n columns for that ReportWriter. When I run my query, I get the
count of "y" values for *all* the ReportWriters. Here's my SQL I got
using the query builder:

SELECT QCRecords.ReportWriter, Count(QCRecords.[FormatCorrect?]) AS
[CountOfFormatCorrect?], Sum(QCRecords.[FormatErrors?]) AS
[SumOfFormatErrors?], Count(QCRecords.[GrammCorrect?]) AS
[CountOfGrammCorrect?], Sum(QCRecords.[GrammErrors?]) AS
[SumOfGrammErrors?], Count(QCRecords.[SpellCorrect?]) AS
[CountOfSpellCorrect?], Sum(QCRecords.[SpellErrors?]) AS
[SumOfSpellErrors?]
FROM QCRecords
GROUP BY QCRecords.ReportWriter, QCRecords.ReportDate;

This is a good start but it doesn't give me the count of "y" values in
each column matching the selected ReportWriter. The resulting dataset
will be used to create bar charts for each ReportWriter and it should
show the number of errors in each column and the number of error-free
reports which that ReportWriter released. I also want to incorporate
ReportDate so I can filter only those records within a date range based
on start/end calendar objects on a form somewhere.

I hope this is clear enough. Please help and thanks.


TForward

 

[JLamb]

I'm not sure I understand what's happening here. Given the code you listed
you should be seeing essentially just a repeat of the table as you are
grouping by writer and date. That constitutes just one line in your original
table. You should not be seeing a sum of all 'Y' answers on the whole table
based on this code. If you take out the date group by this should work
correctly.

You want to include the report date in a WHERE clause for your date criteria
but you do not want to group by report date if you want every report each
writer wrote to be summed up.

 

[John Spencer (MVP)]

Count counts the number of records with a value in the field (True and False are
both values. Try Summing the number of true values (which are equal to negative
1) and then get the absolute value of the sum.

SELECT QCRecords.ReportWriter,
Abs(SUM(QCRecords.[FormatCorrect?])) AS [CountOfFormatCorrect?],
Sum(QCRecords.[FormatErrors?]) AS [SumOfFormatErrors?],
Abs(Sum(QCRecords.[GrammCorrect?])) AS [CountOfGrammCorrect?],
Sum(QCRecords.[GrammErrors?]) AS [SumOfGrammErrors?],
Abs(Sum(QCRecords.[SpellCorrect?])) AS [CountOfSpellCorrect?],
Sum(QCRecords.[SpellErrors?]) AS [SumOfSpellErrors?]
FROM QCRecords
GROUP BY QCRecords.ReportWriter

 

[TForward]

I'm not sure I understand what's happening here. Given the code you
listed you should be seeing essentially just a repeat of the table as
you are grouping by writer and date. That constitutes just one line
in your original table. You should not be seeing a sum of all 'Y'
answers on the whole table based on this code. If you take out the
date group by this should work correctly.

You want to include the report date in a WHERE clause for your date
criteria but you do not want to group by report date if you want every
report each writer wrote to be summed up.

ReportWriter - person writing the report
ReportDate - when the report was released
FormatCorrect? - y/n, was the format correct
FormatErrors? - the number of formatting errors
GrammCorrect? - y/n, was grammar correct
GrammErrors? - the number of grammar errors
SpellCorrect? - y/n, was everything spelled right
SpellErrors? - the number of spelling errors

There are 12-ish categories for QC and some misc other fields, but I
won't list them all. I'm trying to use a totals-query to first,
group all records by ReportWriter, then get the sum of errors in each
column for each ReportWriter, and finally get the count of all "y"
values in all the y/n columns for that ReportWriter. When I run my
query, I get the count of "y" values for *all* the ReportWriters.
Here's my SQL I got using the query builder:

SELECT QCRecords.ReportWriter, Count(QCRecords.[FormatCorrect?]) AS
[CountOfFormatCorrect?], Sum(QCRecords.[FormatErrors?]) AS
[SumOfFormatErrors?], Count(QCRecords.[GrammCorrect?]) AS
[CountOfGrammCorrect?], Sum(QCRecords.[GrammErrors?]) AS
[SumOfGrammErrors?], Count(QCRecords.[SpellCorrect?]) AS
[CountOfSpellCorrect?], Sum(QCRecords.[SpellErrors?]) AS
[SumOfSpellErrors?]
FROM QCRecords
GROUP BY QCRecords.ReportWriter, QCRecords.ReportDate;

This is a good start but it doesn't give me the count of "y" values
in each column matching the selected ReportWriter. The resulting
dataset will be used to create bar charts for each ReportWriter and
it should show the number of errors in each column and the number of
error-free reports which that ReportWriter released. I also want to
incorporate ReportDate so I can filter only those records within a
date range based on start/end calendar objects on a form somewhere.

I hope this is clear enough. Please help and thanks.


TForward

You're right about grouping by ReportDate, I meant to take that out
before posting this. I forgot to mention all my y/n columns are text
fields because some of them need to be n/a. If I use:

WHERE Count(QCRecords.[GrammCorrect?]) AS [CountOfGrammCorrect?] = "y"
AND Count(QCRecords.[SpellCorrect?]) AS [CountOfSpellCorrect?] = "y"

and so forth, I only get the records where *all* the y/n values are "y".
Do I need to use nested queries, maybe?

I'm stumped.

 

[TForward]

Count counts the number of records with a value in the field (True and
False are both values. Try Summing the number of true values (which
are equal to negative 1) and then get the absolute value of the sum.

SELECT QCRecords.ReportWriter,
Abs(SUM(QCRecords.[FormatCorrect?])) AS [CountOfFormatCorrect?],
Sum(QCRecords.[FormatErrors?]) AS [SumOfFormatErrors?],
Abs(Sum(QCRecords.[GrammCorrect?])) AS [CountOfGrammCorrect?],
Sum(QCRecords.[GrammErrors?]) AS [SumOfGrammErrors?],
Abs(Sum(QCRecords.[SpellCorrect?])) AS [CountOfSpellCorrect?],
Sum(QCRecords.[SpellErrors?]) AS [SumOfSpellErrors?]
FROM QCRecords
GROUP BY QCRecords.ReportWriter

All,

I have an Access 2000 database at work that contains quality control
data for written reports which are released to our customers. It's a
flat table with columns like:

ReportWriter - person writing the report
ReportDate - when the report was released
FormatCorrect? - y/n, was the format correct
FormatErrors? - the number of formatting errors
GrammCorrect? - y/n, was grammar correct
GrammErrors? - the number of grammar errors
SpellCorrect? - y/n, was everything spelled right
SpellErrors? - the number of spelling errors

There are 12-ish categories for QC and some misc other fields, but I
won't list them all. I'm trying to use a totals-query to first,
group all records by ReportWriter, then get the sum of errors in each
column for each ReportWriter, and finally get the count of all "y"
values in all the y/n columns for that ReportWriter. When I run my
query, I get the count of "y" values for *all* the ReportWriters.
Here's my SQL I got using the query builder:

SELECT QCRecords.ReportWriter, Count(QCRecords.[FormatCorrect?]) AS
[CountOfFormatCorrect?], Sum(QCRecords.[FormatErrors?]) AS
[SumOfFormatErrors?], Count(QCRecords.[GrammCorrect?]) AS
[CountOfGrammCorrect?], Sum(QCRecords.[GrammErrors?]) AS
[SumOfGrammErrors?], Count(QCRecords.[SpellCorrect?]) AS
[CountOfSpellCorrect?], Sum(QCRecords.[SpellErrors?]) AS
[SumOfSpellErrors?]
FROM QCRecords
GROUP BY QCRecords.ReportWriter, QCRecords.ReportDate;

This is a good start but it doesn't give me the count of "y" values
in each column matching the selected ReportWriter. The resulting
dataset will be used to create bar charts for each ReportWriter and
it should show the number of errors in each column and the number of
error-free reports which that ReportWriter released. I also want to
incorporate ReportDate so I can filter only those records within a
date range based on start/end calendar objects on a form somewhere.

I hope this is clear enough. Please help and thanks.

TForward

I never thought of summing ABS values; I could make it work if I were
using true y/n fields. I forgot to mention all my y/n columns are text
fields because some of them need to be n/a. I don't think Access uses
triple-state y/n/na fields so maybe there's a way to translate the values
in the query.

Do I need to use nested queries, maybe?

 

[cpnet]

Access y/n fields are actually triple-state fields - y/n/NULL

 

[TForward]

I never thought of summing ABS values; I could make it work if I were
using true y/n fields. I forgot to mention all my y/n columns are
text fields because some of them need to be n/a. I don't think Access
uses triple-state y/n/na fields so maybe there's a way to translate
the values in the query.

Do I need to use nested queries, maybe?

I made it work today by using:

SELECT sum(iff(QCRecords.[FormatCorrect?]="y", 1, 0)) AS
[CountOfFormatCorrect?]...

This gave me exactly what I was looking for. Now I want to select within
a date range. I have two unbound date fields, StartDate and EndDate, on
the bar chart form which start up empty but get new values from two
calendars on the same form, StartCalendar and EndCalendar. I want the
form to load showing all the entire dataset and filter out the applicable
dates when StartDate and EndDate are filled by the calendars. I want to
give default date range values to the query for the initial form-load so
I tried adding:

WHERE ReportDate BETWEEN iff(isdate([StartDate]), [StartDate], "1 jan
1950) And iff(isdate([EndDate]), [EndDate], "1 jan 2050)

After loading the form I thought I'd be able to reset the date range and
requery/filter the form source data. When I run the query I get an error
saying the query is too complicated. Any suggestions?

TForward

 

[John Spencer (MVP)]

Since your values are text, try using

SUM(IIF([FormatCorrect?]="Yes",1,0) as [CountOfFormatCorrect?]

or

Abs(Sum([FormatCorrect?]="Yes")) as [CountOfFormatCorrect?]

Count counts the number of records with a value in the field (True and
False are both values. Try Summing the number of true values (which
are equal to negative 1) and then get the absolute value of the sum.

SELECT QCRecords.ReportWriter,
Abs(SUM(QCRecords.[FormatCorrect?])) AS [CountOfFormatCorrect?],
Sum(QCRecords.[FormatErrors?]) AS [SumOfFormatErrors?],
Abs(Sum(QCRecords.[GrammCorrect?])) AS [CountOfGrammCorrect?],
Sum(QCRecords.[GrammErrors?]) AS [SumOfGrammErrors?],
Abs(Sum(QCRecords.[SpellCorrect?])) AS [CountOfSpellCorrect?],
Sum(QCRecords.[SpellErrors?]) AS [SumOfSpellErrors?]
FROM QCRecords
GROUP BY QCRecords.ReportWriter

All,

I have an Access 2000 database at work that contains quality control
data for written reports which are released to our customers. It's a
flat table with columns like:

ReportWriter - person writing the report
ReportDate - when the report was released
FormatCorrect? - y/n, was the format correct
FormatErrors? - the number of formatting errors
GrammCorrect? - y/n, was grammar correct
GrammErrors? - the number of grammar errors
SpellCorrect? - y/n, was everything spelled right
SpellErrors? - the number of spelling errors

There are 12-ish categories for QC and some misc other fields, but I
won't list them all. I'm trying to use a totals-query to first,
group all records by ReportWriter, then get the sum of errors in each
column for each ReportWriter, and finally get the count of all "y"
values in all the y/n columns for that ReportWriter. When I run my
query, I get the count of "y" values for *all* the ReportWriters.
Here's my SQL I got using the query builder:

SELECT QCRecords.ReportWriter, Count(QCRecords.[FormatCorrect?]) AS
[CountOfFormatCorrect?], Sum(QCRecords.[FormatErrors?]) AS
[SumOfFormatErrors?], Count(QCRecords.[GrammCorrect?]) AS
[CountOfGrammCorrect?], Sum(QCRecords.[GrammErrors?]) AS
[SumOfGrammErrors?], Count(QCRecords.[SpellCorrect?]) AS
[CountOfSpellCorrect?], Sum(QCRecords.[SpellErrors?]) AS
[SumOfSpellErrors?]
FROM QCRecords
GROUP BY QCRecords.ReportWriter, QCRecords.ReportDate;

This is a good start but it doesn't give me the count of "y" values
in each column matching the selected ReportWriter. The resulting
dataset will be used to create bar charts for each ReportWriter and
it should show the number of errors in each column and the number of
error-free reports which that ReportWriter released. I also want to
incorporate ReportDate so I can filter only those records within a
date range based on start/end calendar objects on a form somewhere.

I hope this is clear enough. Please help and thanks.

TForward

I never thought of summing ABS values; I could make it work if I were
using true y/n fields. I forgot to mention all my y/n columns are text
fields because some of them need to be n/a. I don't think Access uses
triple-state y/n/na fields so maybe there's a way to translate the values
in the query.

Do I need to use nested queries, maybe?

 

[TForward]

Since your values are text, try using

SUM(IIF([FormatCorrect?]="Yes",1,0) as [CountOfFormatCorrect?]

or

Abs(Sum([FormatCorrect?]="Yes")) as [CountOfFormatCorrect?]

That's exactly what dawned on me to try and it worked great. But I still
have another piece to the puzzle. It's in my other post.

TForward