Queries within a query question.

  • Thread starter Thread starter Darren \(at work\)
  • Start date Start date
D

Darren \(at work\)

Hi,
(Already posted in 'Table Design', then I found this one, apologies.)

I am trying to work out the following equation:

----------------------------------------------------------------------------
Attrition Rate = ((Production Faults - faults verified)/Production Qty)*100
----------------------------------------------------------------------------

SQL for 'Production Faults' =
--------------------------
SELECT Inspection.Partcode, (Count(*)/[Production Input]) AS Fault_Rate
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True))
GROUP BY Inspection.Partcode, Inspection.QA_Result
HAVING (((Inspection.Partcode)=[Enter PartCode]) AND
((Inspection.QA_Result)="P1" Or (Inspection.QA_Result)="P2"));
-------------------------

SQL for 'faults verified' =
-------------------------
SELECT Inspection.Partcode, (Count(*)/[Production Input]) AS Faults_Verified
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True) AND ((Inspection.QA_Result)="Pass"
Or (Inspection.QA_Result)="Retouched"))
GROUP BY Inspection.Partcode
HAVING (((Inspection.Partcode)=[Enter PartCode]));
-------------------------

Both these queries work individually as intended.
My question is how do I combine these two queries to give me the results as
specified in the initial equation?
What I have tried so far is far from being pretty, and does not give the
correct result. I was hoping someone could cast a fresh, and more
experienced set of eyes over what I have so far and give a helping hand?

Final query so far: ( I said it wasn't pretty :-) )
----------------------------
SELECT Inspection.Partcode, ((
SELECT Count(*) AS ProdFaults
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True))
GROUP BY Inspection.Partcode
HAVING (((Inspection.Partcode)=[Enter PartCode]))
)
-
((
SELECT Count(*) AS ProdfaultsMinus
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True))
GROUP BY Inspection.Partcode, Inspection.QA_Result
HAVING (((Inspection.Partcode)=[Enter PartCode]) AND
((Inspection.QA_Result)="Pass" Or (Inspection.QA_Result)="Retouched"))
))
/
[Production Input]) AS Attrition_Rate
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True) AND ((Inspection.QA_Result)="Pass"
Or (Inspection.QA_Result)="Retouched"))
GROUP BY Inspection.Partcode
HAVING (((Inspection.Partcode)=[Enter PartCode]));
 
Make a third query and add the first two queries to it:

make a calculated field in the query called

AttritionRate: (([Production Faults] - [faults verified])/[Production
Qty])*100

assuming the fields in the expression are in the first two queries you added
to this 3rd query.




Darren (at work) said:
Hi,
(Already posted in 'Table Design', then I found this one, apologies.)

I am trying to work out the following equation:

-------------------------------------------------------------------------- --
Attrition Rate = ((Production Faults - faults verified)/Production Qty)*100
--------------------------------------------------------------------------
Click to expand...
--

SQL for 'Production Faults' =
--------------------------
SELECT Inspection.Partcode, (Count(*)/[Production Input]) AS Fault_Rate
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True))
GROUP BY Inspection.Partcode, Inspection.QA_Result
HAVING (((Inspection.Partcode)=[Enter PartCode]) AND
((Inspection.QA_Result)="P1" Or (Inspection.QA_Result)="P2"));
-------------------------

SQL for 'faults verified' =
-------------------------
SELECT Inspection.Partcode, (Count(*)/[Production Input]) AS Faults_Verified
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True) AND ((Inspection.QA_Result)="Pass"
Or (Inspection.QA_Result)="Retouched"))
GROUP BY Inspection.Partcode
HAVING (((Inspection.Partcode)=[Enter PartCode]));
-------------------------

Both these queries work individually as intended.
My question is how do I combine these two queries to give me the results as
specified in the initial equation?
What I have tried so far is far from being pretty, and does not give the
correct result. I was hoping someone could cast a fresh, and more
experienced set of eyes over what I have so far and give a helping hand?

Final query so far: ( I said it wasn't pretty :-) )
----------------------------
SELECT Inspection.Partcode, ((
SELECT Count(*) AS ProdFaults
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True))
GROUP BY Inspection.Partcode
HAVING (((Inspection.Partcode)=[Enter PartCode]))
)
-
((
SELECT Count(*) AS ProdfaultsMinus
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True))
GROUP BY Inspection.Partcode, Inspection.QA_Result
HAVING (((Inspection.Partcode)=[Enter PartCode]) AND
((Inspection.QA_Result)="Pass" Or (Inspection.QA_Result)="Retouched"))
))
/
[Production Input]) AS Attrition_Rate
FROM Inspection
WHERE (((Inspection.Date) Between [Enter first date] And [Enter second
date]) AND ((Inspection.Prod_Rej)=True) AND ((Inspection.QA_Result)="Pass"
Or (Inspection.QA_Result)="Retouched"))
GROUP BY Inspection.Partcode
HAVING (((Inspection.Partcode)=[Enter PartCode]));
Click to expand...
 
| Make a third query and add the first two queries to it:
|
| make a calculated field in the query called
|
| AttritionRate: (([Production Faults] - [faults verified])/[Production
| Qty])*100
|
| assuming the fields in the expression are in the first two queries you
added
| to this 3rd query.
|

Many thanks Bill. That did the trick.

Darren
 
Back
Top