Getting table design right ..??

[Darren \(at work\)]

Hi,

Sorry for the cross post, but I was hoping there may be someone in this
forum that has not already seen it already in 'Tabledesign'. I belive that
it is applicable in both forums. Apologies if it is not.

I have a table (Inspected)

InspectID - autonum(PK)
Partcode - text
Date - date/time
Prod_rej - Yes/No
Incoming - Yes/No
Prod_Result - text
QA_Result - Text
SerialNo - text
-----------------------------

This is linked to another table (Details):

DetailsID - autonumber -
|- (PK)
InspectID - number(FK) -
FaultType - text
FaultCause - text
FaultSize - text
Area - text
-------------------------------

The 'Details' table is there so that numerous entries can be made about a
single entry in the 'Inspected' table.
What I need is a table where I can enter the total number of any given
partcode produced in a given 'WEEK NUMBER' so that I can calculate an
attrition rate for a given partcode on any given week.

My question is how can I structure a table(s) that will allow me to enter in
this 'Production Qty', making sure that it is uniquely identified to the
week number and year, while also making sure that I can relate this to the
date that has been entered into the 'Inspected' table.

Basically I need to find out the distinct count to each Partcode in the
Inspected table for each individual week number and using the Production Qty
for that week, calculate the fault rate/attrition rate.

I hope I have explained my self clearly enough.

Many thanks for any an all help and advice.

Darren

 

[S Jackson]

Hi Darren:

I am certainly not qualified to give you the best answer, but what I can
tell you is that you may want to change the name of the "Date" field in the
Inspected table. I will leave it to the professionals here on this forum
will explain why, but it has something to do with the fact that Access uses
a "Date" function, so it can get confused when you also have a field named
"Date."

As for the table design I am not sure you need another table, if I
understand correctly. Wouldn't you be able to gather the information you
need through a query? Again, I'll let the professionals here give you a
better response.

Good luck. This forum is a great place to get answers.

S. Jackson

 

[Kevin Sprinkel]

Always eager to assist quality management...

To generate an attrition rate, you will need to capture
the quantities rejected of each part. I suggest you add
this field to your Details table so that you can track the
qty rejected per fault type separately if you wish.

I suggest adding a Parts table to hold valid part codes.
Place a combo box on your Inspected table form looking up
the partcode.

Partcode Integer or Text
Description Text

This way, you assure that users can enter only valid part
codes into your Inspected table, data entry is simpler and
faster, and you avoid miscalculations caused by typos.

For similar reasons, consider changing FaultType and
FaultCause to integer fields, and create related "lookup"
tables and combo boxes.

Now create a Production table:

ProductionID AutoNumber
PartCode Integer (foreign key to the Parts table)
ProductionDate Date/Time
QtyProduced Integer(or Single if partials are possible)

Now you've captured the qtys rejected of each part (per
cause), the qtys produced, and time stamped each.

The final part is to create a Totals query that totals the
quantity rejected and quantity produced for each part in a
given week. Select the Parts, Details, and Production
tables, and be sure that the latter two are related to the
Parts table by the Partcode. Choose a Sum for the two
qtys, and Group By the Partcode. Create a calculated
field for the attrition rate, [QtyRejected]/[QtyProduced].

The only remaining piece is grouping these by week. This
isn't difficult, I've seen threads describing it, but as
I've never used it, I can't describe it. Hopefully, this
will solve most of your issues, and a subsequent post or
web search can determine how this is done.

And, I agree with S Jackson--DATE is an Access reserved
word. Using it can generate erroneous results. Use
InspectDate or InspectedOn, etc.

HTH
Kevin Sprinkel
Becker & Frondorf

 

[Darren]

"Kevin Sprinkel" very kindly wrote in reply to my message

Always eager to assist quality management...

Why, thank you. It is a thankless job sometimes

To generate an attrition rate, you will need to capture
the quantities rejected of each part. I suggest you add
this field to your Details table so that you can track the
qty rejected per fault type separately if you wish.

This would be ideal, but when the inspectors are inputting the data for each
part, they do so for each individual part. ie:

1) 50 items returned from production
2) Item 1 sampled to ascertain if process fault or incoming
3) Item details recorded on Inspection Sheet for entry to Db when 50 items
are sampled
4) Repeat steps 2 & 3 for 50 Items
5) Enter data into DB.

At this point the inspector does not know the weekly or even daily
production figures for each item.
These figures will be entered by myself or supervisor when the need to
collalate the weekly attrition rates arises.

I suggest adding a Parts table to hold valid part codes.
Place a combo box on your Inspected table form looking up
the partcode.

This is a bugbear in my dept as MIS will not allow linking to the main
propriatory system, in any shape or form. Though they do allow download in
Excel format, so I will look into having this done on a regular basis and
linking to that.

For similar reasons, consider changing FaultType and
FaultCause to integer fields, and create related "lookup"
tables and combo boxes.

Will do.

Now create a Production table:

ProductionID AutoNumber
PartCode Integer (foreign key to the Parts table)
ProductionDate Date/Time
QtyProduced Integer(or Single if partials are possible)

Now you've captured the qtys rejected of each part (per
cause), the qtys produced, and time stamped each.

I guess my problem is that because of the issue that the Production QTY's
will not be entered when the inspection data is inputted, I cannot see a way
of entering the production QTY's at a later date and still have it related
to each WeekNo for any given YEAR, as this system, hopefully, will be used
for the forseeable future. Well, until something better comes along

Did you ever have an inkling of an idea sitting at the back of your mind,
but be not quite able to pull it into something concrete?
Quite fustrating.

The final part is to create a Totals query that totals the
quantity rejected and quantity produced for each part in a
given week. Select the Parts, Details, and Production
tables, and be sure that the latter two are related to the
Parts table by the Partcode. Choose a Sum for the two
qtys, and Group By the Partcode. Create a calculated
field for the attrition rate, [QtyRejected]/[QtyProduced].

I would agree that this can be achieved in a query, and hopefully I can
figure that out when I get the table design/structure correct.

And, I agree with S Jackson--DATE is an Access reserved
word. Using it can generate erroneous results. Use
InspectDate or InspectedOn, etc.

Thanks for the heads up on that one, Kevin & S.Jackson..

HTH
Kevin Sprinkel
Becker & Frondorf

Thank you so very much for the help and advice on this one, it is must
greatfully appreciated. Maybe if someone has anything additional to add,
that to would be most welcome

Kind Regards
Darren