Calculate yield

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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,

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...
 
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
..
 
Back
Top