Blank results from Audit Query

[johnwiseman]

Hi. Wondered if I could get some assistance.

I have 3 querys.

1. qryAUDITED

CountofReference ¦ Operator ¦ Date

2. qryAUDITEDOK

CountofReference ¦ Operator ¦ Date ¦ Auditok (criteria -1)


These two feed into qryRATIO unlinked

3. qryRATIO

A:Countofreference¦B:CountofReference¦ Ratio:/[a]*100
qryAUDITED qryAUDITEDOK


This is used as part of a operator audit. 10 peiced of work are checked of which 2 are incorrect and marked on a tickbox which is picked up on qryAUDITOK as 2 records.This will return these results in qryRATIO

A:Countofreference ¦B:CountofReference¦Ratio
10 2 80


However, if all check work is ok then qryAUDIT will return no results so when qryRATIO is run this will also produce no results. We need this to show 100%. Its not good when giving training and the operators with a 100% success rate has a blank report.

Thanks in advance for any assistance.

 

[PHV]

What is the actual SQL code of qryRATIO ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnwiseman]

Hi.

SQl as below.

You will see that field names are slightly different but you will get the idea.


SELECT [qry Site Audit MSC - Checked].[Site Location], [qry Site Audit MSC - Checked].[CountOfDatabase Reference] AS [MSC Checked], [qry Site Audit MSC - Audit OK].[CountOfDatabase Reference] AS [MSC AuditOK], [MSC AuditOK]/[MSC Checked]*100 AS [MSC Ratio]
FROM [qry Site Audit MSC - Audit OK], [qry Site Audit MSC - Checked];

Thanks for looking at this..

 

[PHV]

if all check work is ok then qryAUDIT will return no results
I assume that qryAUDIT is [qry Site Audit MSC - Checked]
Perhaps something like this ?
SELECT B.[Site Location], Nz(A.[CountOfDatabase Reference],B.[CountOfDatabase Reference]) AS [MSC Checked], B.[CountOfDatabase Reference] AS [MSC AuditOK], [MSC AuditOK]/[MSC Checked]*100 AS [MSC Ratio]
FROM [qry Site Audit MSC - Checked] AS A
RIGHT JOIN [qry Site Audit MSC - Audit OK] AS B
ON A.[Site Location] = B.[Site Location]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnwiseman]

Hi PH.

Thanks for your time but it still will not return a result, if the [qry Site Audit MSC - Checked] returns no result.

I have taken a step back to tried to get the [qry Site Audit MSC - Checked] to return a result as follows.

SELECT Count([tbl Main Input].[Database Reference]) AS [CountOfDatabase Reference], [tbl Main Input].[Site Location], [tbl Main Input].[AUDIT OK], [tbl Main Input].[Identify Method], IIf([Database reference] Is Null,"0",[database Reference]) AS Total
FROM [tbl Main Input]
WHERE ((([tbl Main Input].Date) Between [forms]![frm audit]![txtstartdate] And [forms]![frm audit]![txtenddate]))
GROUP BY [tbl Main Input].[Site Location], [tbl Main Input].[AUDIT OK], [tbl Main Input].[Identify Method], IIf([Database reference] Is Null,"0",[database Reference])
HAVING ((([tbl Main Input].[Site Location])="msc") AND (([tbl Main Input].[AUDIT OK])=-1) AND (([tbl Main Input].[Identify Method])="Audit"));

I was hoping [Total] would return a "0" if [CountOfDatabase Reference]is null, if [CountOfDatabase Reference] is null the query returns a blank result.

Any Ideas????