Calculate yield

J

J

I have the following table structures:
tblBatch tblParts
--------- ----------
FrameNum PartID
RunDate CVisual
SpecID AVisual
FinalInspect
FrameID(fk)

I want a query that will enable a user to query by RunDate and be given the
total number of PartID's for each FrameNum and what the Yield was where a good
PartID is one where CVisual, AVisual, and FinalInspect are either Null or
equal to Test. I've got as far as creating a query that will meet the filter
criteria
SELECT tblBatch.Traveler_Num, tblPArts.MEA_Name, tblBatch.ProductSpec,
tblBatch.RunDate, tblBatch.CTime, tblPArts.Cinspect, tblPArts.AInspect,
tblPArts.FinalInspect
FROM INNER JOIN tblPArts ON tblBatch.Traveler_ID = tblPArts.TravelerID
WHERE (((tblBatch.RunDate) Between [start date] And [end date]) AND
((tblPArts.Cinspect) Is Null) AND ((tblPArts.AInspect) Is Null) AND
((tblPArts.FinalInspect) Is Null)) OR (((tblPArts.Cinspect)="test")) OR
(((tblPArts.AInspect)="test")) OR (((tblPArts.FinalInspect)="test"));

but now I'm at a loss for how to calculate the Yield.

Any help is greatly appreciated.
 
J

J

I just realized that maybe what I need first have is a query with an IF
statement that says IF CVisual or AVisual or FinalInspect are not Null or
"Test" then FailedInspect = 1 otherwise FailedInspect = 0. Then a query that
counts the number of Parts for each batch and the number that failed and
calculate the Yield off that. I'm stuck at the IF statement.
 
C

Conan Kelly

J,

How about something like this:

FailedInspect: =iif(and([CVisual] is not null, [CVisual]<>"Test", [AVisual]
is not null, [AVisual]<>"Test", [FinalInspect] is not null,
[FinalInspect]<>"Test", ),1,0)

I hope the syntax is right, I was just wingin' it.

HTH,

Conan Kelly




J said:
I just realized that maybe what I need first have is a query with an IF
statement that says IF CVisual or AVisual or FinalInspect are not Null or
"Test" then FailedInspect = 1 otherwise FailedInspect = 0. Then a query
that
counts the number of Parts for each batch and the number that failed and
calculate the Yield off that. I'm stuck at the IF statement.

J said:
I have the following table structures:
tblBatch tblParts
--------- ----------
FrameNum PartID
RunDate CVisual
SpecID AVisual
FinalInspect
FrameID(fk)

I want a query that will enable a user to query by RunDate and be given
the
total number of PartID's for each FrameNum and what the Yield was where a
good
PartID is one where CVisual, AVisual, and FinalInspect are either Null or
equal to Test. I've got as far as creating a query that will meet the
filter
criteria
SELECT tblBatch.Traveler_Num, tblPArts.MEA_Name, tblBatch.ProductSpec,
tblBatch.RunDate, tblBatch.CTime, tblPArts.Cinspect, tblPArts.AInspect,
tblPArts.FinalInspect
FROM INNER JOIN tblPArts ON tblBatch.Traveler_ID = tblPArts.TravelerID
WHERE (((tblBatch.RunDate) Between [start date] And [end date]) AND
((tblPArts.Cinspect) Is Null) AND ((tblPArts.AInspect) Is Null) AND
((tblPArts.FinalInspect) Is Null)) OR (((tblPArts.Cinspect)="test")) OR
(((tblPArts.AInspect)="test")) OR (((tblPArts.FinalInspect)="test"));

but now I'm at a loss for how to calculate the Yield.

Any help is greatly appreciated.
Click to expand...
Click to expand...
 
J

John Spencer

Well, you posted table structure and your posted query don't seem to have
the same field names. Using the posted query, I think you want something
like

SELECT tblBatch.Traveler_Num
, tblBatch.ProductSpec
, tblBatch.RunDate
, tblBatch.CTime
, Count(tblParts.TravelerID) as NumParts
, Abs(Sum(Nz(CVisual,"Test")<>"Test" AND Nz(AVisual,"Test")<>"Test" AND
Nz(FinalInspect,"Test")<>"Test")) as CountYield

FROM tblBatch INNER JOIN tblPArts ON tblBatch.Traveler_ID =
tblPArts.TravelerID
WHERE tblBatch.RunDate Between [start date] And [end date]

GROUP BY tblBatch.Traveler_Num
, tblBatch.ProductSpec
, tblBatch.RunDate
, tblBatch.CTime

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top