Finding the Percentage of 'Yes'

[Eka1618]

Hello,
I am trying to create a query that finds the percentage of 'Yes' values in
the field [P_F]. I have created the following query that seems to kinda work,
but everything is multiplied by 3 for some reason:

SELECT tblResults.Q_INC, tblResults.L_PART_NO, tblResults.K_PART_NO,
tblResults.PAT_NO, tblResults.PAT_SIZE, Count(tblResults.S_ID) AS [Total
Parts], Abs(Sum([P_F]=-1)) AS [Total Passed], [Total Passed]/[Total Parts] AS
[Percentage of Parts Passed]
FROM tblResults, qryPassed
GROUP BY tblResults.Q_INC, tblResults.L_PART_NO, tblResults.K_PART_NO,
tblResults.PAT_NO, tblResults.PAT_SIZE;


This is what I see:

Total Passed Total Parts
12 21
6 15



This is what I should see:

Total Passed Total Parts
4 7
2 5


Also, If I take out the field [Total Passed] the field [Total Parts] returns
the correct count....

If anyone has any suggestions, please let me know, Thank You!

~Erica~

 

[Michel Walsh]

I guess that qryPassed has 3 records. Why you need it in the first place?
Try without it::

SELECT tblResults.Q_INC,
tblResults.L_PART_NO,
tblResults.K_PART_NO,
tblResults.PAT_NO,
tblResults.PAT_SIZE,
Count(tblResults.S_ID) AS [Total Parts],
Abs(Sum([P_F]=-1)) AS [Total Passed],
[Total Passed]/[Total Parts] AS [Percentage of Parts Passed]
FROM tblResults ' <==== here
GROUP BY tblResults.Q_INC,
tblResults.L_PART_NO,
tblResults.K_PART_NO,
tblResults.PAT_NO,
tblResults.PAT_SIZE;



Vanderghast, Access MVP

 

[Eka1618]

Thanks Mike! I didn't realize that was still in there...I had tried something
earlier using that query, LOL.

Works like a charm

~Erica~

I guess that qryPassed has 3 records. Why you need it in the first place?
Try without it::

SELECT tblResults.Q_INC,
tblResults.L_PART_NO,
tblResults.K_PART_NO,
tblResults.PAT_NO,
tblResults.PAT_SIZE,
Count(tblResults.S_ID) AS [Total Parts],
Abs(Sum([P_F]=-1)) AS [Total Passed],
[Total Passed]/[Total Parts] AS [Percentage of Parts Passed]
FROM tblResults ' <==== here
GROUP BY tblResults.Q_INC,
tblResults.L_PART_NO,
tblResults.K_PART_NO,
tblResults.PAT_NO,
tblResults.PAT_SIZE;



Vanderghast, Access MVP

Hello,
I am trying to create a query that finds the percentage of 'Yes' values in
the field [P_F]. I have created the following query that seems to kinda
work,
but everything is multiplied by 3 for some reason:

SELECT tblResults.Q_INC, tblResults.L_PART_NO, tblResults.K_PART_NO,
tblResults.PAT_NO, tblResults.PAT_SIZE, Count(tblResults.S_ID) AS [Total
Parts], Abs(Sum([P_F]=-1)) AS [Total Passed], [Total Passed]/[Total Parts]
AS
[Percentage of Parts Passed]
FROM tblResults, qryPassed
GROUP BY tblResults.Q_INC, tblResults.L_PART_NO, tblResults.K_PART_NO,
tblResults.PAT_NO, tblResults.PAT_SIZE;


This is what I see:

Total Passed Total Parts
12 21
6 15



This is what I should see:

Total Passed Total Parts
4 7
2 5


Also, If I take out the field [Total Passed] the field [Total Parts]
returns
the correct count....

If anyone has any suggestions, please let me know, Thank You!

~Erica~