First Pass Yield by Part Number

[Tobes]

Hello all,

I've run into a bit of a problem on an access database that I've created for our quality control team.

Here are the specifics: (Access 2010.)
The database is set up to record the QC person's name, the part number, a bunch of information about the line (line speed, temperature, etc) and finally if the part was good (passes) or if it failed & why it failed.

It then calculates the first pass yield by dividing the number of "GoodParts" (count STRGood) by the "Total Number of Parts" (count of strParts).

The FPY is always visible on the Form and updates after every entry.

Additional information unimportant to this problem:
If a part failed, they have several fail modes that they can choose from, so that we can then track the failure modes, correlate them with the line information we capture and figure out if those variables contribute to recurring failures.


Here's the problem:
Instead of calculating the FPY by just the total count strgood / total count strparts, we'd like to be able to calculate (and show on our form as is updates) the FPY by the CURRENT part number (strpart) being inspected.

I have created reports from the collected data that give the FPY by date and part type, but I'd like to be able to show:

If the current part number running is X. Calculate all of the X's for the current date. Calculate all of the X's that are "Good." Divide the Good X's that were run today, by the total number of X's that were run today.

Then when they change the part number that there are inspecting to Y, it brings up a new FPY for total number of Good Y's run today / total number of Y's today.

1) Does what I'm asking make any sense?
2) Is there someone out there that has some idea of how to do this?
3) How?:banghead:

Thanks in advance for any insight.
-Tobes

 

[jzwp22]

Welcome to the forum!


Could you provide some information on the field names in the tables that are pertinent to your question? I assume that you have a table of parts and within that table you have a part type field and some field to designated whether the part is good or bad.

Out of curiosity, do you check every part produced or do you sample parts at a specific frequency?

 

[Tobes]

JZ,

Thank you for the welcome. At the moment, yes, we are checking each part and recording the results.

I will gladly provide whatever information you request for this.

Form for entering Commercial QC Data:
frmCommQC
The QC personnel will record the following items (listed only the ones involved in this problem):
strPart = records part number or number to strPart in tblCommQCData
lngGood = records -1 in tblCommQCData if selected
lng(typeoffailuremodehere) = records -1 in tblCommQCData if selected. lngGood is left null.
Accept Entry = adds the record to tblCommQCData and adds a time/Date stamp.

A record is created for each part and information stored in the table for the Commercial QC Data:
tblCommQCData

Field names involved:
dtmDateEntered = The Date the record was entered
dtmTimeEntered = The Time the record was entered
strPart = The part name or number
lngGood = When the part passes, selects -1 in the lngGood field. If the part fails, selects a -1 in the one of the failmodes (not listed here - unnecessary for calculations.)

Current query for FPY:
qryCommFPY

Field names involved:
Date = pulled dtmDateEntered from tblCommQCData
strPart = pulled strPart from tblCommQCData
lngGood = pulled lngGood from tblCommQCData
TotalGood = count of lngGood from tblCommQCData
TotalParts = count of strPart from tblCommQCData
FPY = [TotalGood]/[TotalParts]

This shows FPY of all parts. That FPY is visible on the record entry form.

I have been asked to have the FPY that is visible be refreshed when a new part is run for the day. (as described above)

I have created report from this query that totals FPY by Date and Part categories:
Date | Part Number | Total Good produced | Total Parts run | FPY
date | strpart | TotalGood | TotalParts | TotalGood/Total Parts

If there is a way to display the most current entry for this query, that may be an easy fix as well. I just don't know how to do that either.

Thanks for responding and looking at this. I really do appreciate it.

-Tobes

 

[jzwp22]

You said initially that you wanted to pull the information into a form but now you are saying a report. You will have to clarify where you want to do this.

To do it in a form, you can use domain aggregate functions to pull in the count information into a control on your form.

The following would give the count of good parts for the current date


=DCOUNT("*", "tblCommQCData", "strPart='" & [formcontrolnameholdingstrPart] & "' AND dtmDateEntered=#" & date() & "# AND lngGood=-1")

You would use a similar Dcount() in another control to get the total of all parts with that part number whether good or bad. The Dcount() would be the same as above less the AND lngGood=-1 section of the criteria

You would use a third control that divides the values in the other controls.

 

[Tobes]

You said initially that you wanted to pull the information into a form but now you are saying a report. You will have to clarify where you want to do this.

Begging 'pardon. You are correct. I need to clarify this. At the moment I have the FPY value being calculated in a query (as described above) then I have it displayed directly on the frmCommQC form. The same value is also included in some of the reports that they are running. For now the more important location would be the frmCommQC form.

Your response is spot on. Thank you, very much for taking the time to lend a hand. You da man.
Respectfully,
Tobes

 

[jzwp22]

The domain aggregate functions are relatively slow. If you are just viewing (and not editing) records via the form you mention, you might considering bringing in the values via that query rather than using the DCount() expressions I provided.

 

[Tobes]

You are correct. I have it being calculated in a query and merely being displayed on the form as they enter new data. Thanks, again fro the help.

 

[jzwp22]

If you were to combine the calculated values in with the record source for the form, you may make the recordset un-updateable which would prevent users from adding new records, so using the domain aggregate function on the form as you are doing now is the better approach.