completion - transformed Query always gives 100%

[Misiek]

Hello all,

This works:

SELECT sum(iif(dDateClosed is null,0,1))/sum(iif(dDateFound is null,0,1))*100 AS Calcul
FROM Q_Defects
WHERE (Q_Defects.dAreaFK)=[Forms]![F_MainMenu].[cboStatsArea];

This doesn't, can't find out why...

TRANSFORM nz(sum(iif(dDateClosed is null,0,1))/sum(iif(dDateFound is null,0,1))*100,0) AS Completion
SELECT sum(iif(dDateFound is null,0,1)) AS [Found], sum(iif(dDateClosed is null,0,1)) AS Fixed, sum(iif(dDateClosed is null,0,1))/sum(iif(dDateFound is null,0,1))*100 AS Compl
FROM Q_Defects
WHERE (dAreaFK=Forms!F_MainMenu.cboStatsArea 
OR Forms!F_MainMenu!cboStatsArea IS NULL)
AND (dShiftFK=Forms!F_MainMenu.cboStatsShift 
OR Forms!F_MainMenu!cboStatsShift IS NULL
AND month(dDateClosed)=month(Date()))
GROUP BY month([dDateClosed])
PIVOT month([dDateClosed])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

Thanks

 

[spikepl]

"not working" gives millions of hits on google. Just use one of those.

Or be specific and say the symptoms of "Not working"

 

[JHB]

Read here:
https://support.microsoft.com/da-dk/kb/209778?wa=wsignin1.0

 

[Misiek]

Well, actually they both work, no errors, all data loading etc...
I have records with [entryDate] and [closureDate].
I want to calculate completion for those records, then group them by month, thats why I created pivot query.
The problem is:
there is approx 30 records with 16 of them completed, so the completion should be 51%, and this works correctly in the first code i've provided but then when I add months and grouping (the second code) it gives ALWAYS 100% no matter how many records have or haven't got date.

 

[JHB]

Could you post a print screen of what you get, or maybe your database with some sample data?

 

[Misiek]

Re: calculate completion - transformed Query

Still struggling, but I think I understand my problem now.

I am trying to make a query calculate completion for each month, based on 2 fields [dDateFound] and [dDateClosed].

I run a query that counts all found records and then group it by month[dDateFound],
then I run another query that count all records that been closed and group it by month[dDateClosed].

Until this moment everything works perfectly, but I want to calculate completion which is count([dDateClosed]) / count([dDateFound]) and group it by month.

At this stage I get weird figures due to grouping, as if I group by month[dDateClosed] the query will only base calculation on records found and closed on that month, excluding everything that been found but not closed.

If I do it other way around, group it by month[dDateFound], the query splits results between found each month and closed, (skips data found previous month but closed current month).

Is it clear what I am trying to do?

I think I need an INNER query, but I am not sure if that's the solution plus I don't know how to combine 2 of those transformed queries into one.

Can someone advise, please.