No result query feeding a query that does a calculation

S

SteveR

Hello, I have a table (tbl_Audit_Answer) that has two boolean fields [AD]and
[CAR]. I have a query (qryADCount) that counts the number of [AD] that are
TRUE and another one (qryCARCount) that does the same for [CAR]. A third
query (qryScore) takes the counts from both of these queries, multiplies each
by a value in a table assocated with the penalty of an AD and CAR violation
(different values) and subtracts from 100 leaving a score. My problem is
that it is possible to go through an audit with no AD or CAR violations so
one or both of the count queries can come up blank which of course doesn't do
the score query any good.

Here is the AD query: SELECT tbl_Visit.Ship, tbl_Visit.Date,
tbl_Audit_Answer.AD, Count(tbl_Audit_Answer.AD) AS CountOfAD,
tbl_Visit.TagOffDate
FROM tbl_Visit INNER JOIN tbl_Audit_Answer ON tbl_Visit.Visit_ID =
tbl_Audit_Answer.Visit_ID
GROUP BY tbl_Visit.Ship, tbl_Visit.Date, tbl_Audit_Answer.AD,
tbl_Visit.TagOffDate
HAVING (((tbl_Visit.Ship)=[Forms]![frmNonCurrentVisits]![Ship]) AND
((tbl_Visit.Date)=[Forms]![frmNonCurrentVisits]![Date]) AND
((tbl_Audit_Answer.AD)=True));

How can I get this to sent qryScore a "0" if it can't find any records with
a true indication?

Thanks for the help.
 
M

MGFoster

SteveR said:
Hello, I have a table (tbl_Audit_Answer) that has two boolean fields [AD]and
[CAR]. I have a query (qryADCount) that counts the number of [AD] that are
TRUE and another one (qryCARCount) that does the same for [CAR]. A third
query (qryScore) takes the counts from both of these queries, multiplies each
by a value in a table assocated with the penalty of an AD and CAR violation
(different values) and subtracts from 100 leaving a score. My problem is
that it is possible to go through an audit with no AD or CAR violations so
one or both of the count queries can come up blank which of course doesn't do
the score query any good.

Here is the AD query: SELECT tbl_Visit.Ship, tbl_Visit.Date,
tbl_Audit_Answer.AD, Count(tbl_Audit_Answer.AD) AS CountOfAD,
tbl_Visit.TagOffDate
FROM tbl_Visit INNER JOIN tbl_Audit_Answer ON tbl_Visit.Visit_ID =
tbl_Audit_Answer.Visit_ID
GROUP BY tbl_Visit.Ship, tbl_Visit.Date, tbl_Audit_Answer.AD,
tbl_Visit.TagOffDate
HAVING (((tbl_Visit.Ship)=[Forms]![frmNonCurrentVisits]![Ship]) AND
((tbl_Visit.Date)=[Forms]![frmNonCurrentVisits]![Date]) AND
((tbl_Audit_Answer.AD)=True));

How can I get this to sent qryScore a "0" if it can't find any records with
a true indication?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Count() should return zero when there isn't any row that meets the
criteria. Move the criteria out of the HAVING clause into a WHERE
clause. That is what is making the result set return NULLs.

SELECT tbl_Visit.Ship, tbl_Visit.Date,
tbl_Audit_Answer.AD, Count(tbl_Audit_Answer.AD) AS CountOfAD,
tbl_Visit.TagOffDate
FROM tbl_Visit INNER JOIN tbl_Audit_Answer ON tbl_Visit.Visit_ID =
tbl_Audit_Answer.Visit_ID
WHERE(((tbl_Visit.Ship)=[Forms]![frmNonCurrentVisits]![Ship]) AND
((tbl_Visit.Date)=[Forms]![frmNonCurrentVisits]![Date]) AND
((tbl_Audit_Answer.AD)=True))
GROUP BY tbl_Visit.Ship, tbl_Visit.Date, tbl_Audit_Answer.AD,
tbl_Visit.TagOffDate

BTW, using "Date" as a column name can lead to confusion while reading
queries, 'cuz it is also a VBA function that returns the current system
date. Try a more descriptive name like "Visit_Date," or something that
describes what the date represents: arrival date, departure date,
booking date, etc. The same applies to all your columns, make them as
descriptive as possible.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwOSAIechKqOuFEgEQL5XgCeMDKbFF5W2xvMAZN8VQCP/TDHgpUAn3/f
oaGby98UWah1SB7fnSuAEzny
=uy1K
-----END PGP SIGNATURE-----
 
S

SteveR

Thanks Mr. Foster, It is still not giving me any rows but I think you put me
SteveR said:
Hello, I have a table (tbl_Audit_Answer) that has two boolean fields [AD]and
[CAR]. I have a query (qryADCount) that counts the number of [AD] that are
TRUE and another one (qryCARCount) that does the same for [CAR]. A third
query (qryScore) takes the counts from both of these queries, multiplies each
by a value in a table assocated with the penalty of an AD and CAR violation
(different values) and subtracts from 100 leaving a score. My problem is
that it is possible to go through an audit with no AD or CAR violations so
one or both of the count queries can come up blank which of course doesn't do
the score query any good.

Here is the AD query: SELECT tbl_Visit.Ship, tbl_Visit.Date,
tbl_Audit_Answer.AD, Count(tbl_Audit_Answer.AD) AS CountOfAD,
tbl_Visit.TagOffDate
FROM tbl_Visit INNER JOIN tbl_Audit_Answer ON tbl_Visit.Visit_ID =
tbl_Audit_Answer.Visit_ID
GROUP BY tbl_Visit.Ship, tbl_Visit.Date, tbl_Audit_Answer.AD,
tbl_Visit.TagOffDate
HAVING (((tbl_Visit.Ship)=[Forms]![frmNonCurrentVisits]![Ship]) AND
((tbl_Visit.Date)=[Forms]![frmNonCurrentVisits]![Date]) AND
((tbl_Audit_Answer.AD)=True));

How can I get this to sent qryScore a "0" if it can't find any records with
a true indication?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Count() should return zero when there isn't any row that meets the
criteria. Move the criteria out of the HAVING clause into a WHERE
clause. That is what is making the result set return NULLs.

SELECT tbl_Visit.Ship, tbl_Visit.Date,
tbl_Audit_Answer.AD, Count(tbl_Audit_Answer.AD) AS CountOfAD,
tbl_Visit.TagOffDate
FROM tbl_Visit INNER JOIN tbl_Audit_Answer ON tbl_Visit.Visit_ID =
tbl_Audit_Answer.Visit_ID
WHERE(((tbl_Visit.Ship)=[Forms]![frmNonCurrentVisits]![Ship]) AND
((tbl_Visit.Date)=[Forms]![frmNonCurrentVisits]![Date]) AND
((tbl_Audit_Answer.AD)=True))
GROUP BY tbl_Visit.Ship, tbl_Visit.Date, tbl_Audit_Answer.AD,
tbl_Visit.TagOffDate

BTW, using "Date" as a column name can lead to confusion while reading
queries, 'cuz it is also a VBA function that returns the current system
date. Try a more descriptive name like "Visit_Date," or something that
describes what the date represents: arrival date, departure date,
booking date, etc. The same applies to all your columns, make them as
descriptive as possible.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwOSAIechKqOuFEgEQL5XgCeMDKbFF5W2xvMAZN8VQCP/TDHgpUAn3/f
oaGby98UWah1SB7fnSuAEzny
=uy1K
-----END PGP SIGNATURE-----
Click to expand...
 
Top