Trying hard to do a simple thing !

[adevine]

This is my first data base. I am tracking production machine settings and trying to only report setup parameters that have passed yielded product that has passed QA.
Each product runs on 1 of 3 machines. Each run has a lot number. Product numbers can run on any machine. I know this is probbaly hard to understand. I will be glad to email the database in its current state if anyone (will) can help. I will understand if this is too big to attack...
Thanks in advance
Andy
adevine19@yahoo.com
Current method of collecting data
1) At product start up the operator fills out a paper form with the product specs and tooling.
2) The operator then goes to the filing cabinet and pulls a file for that product number. These files contain a Product Data Form from each run. The operators fill out this form once at the end of each shift. On these forms there is a rating system 1 to 5. The lower the number the worse the control on that run. Currently this number is assigned based on the operators personal feeling of the run for his shift with SPC charts in mind.
3) Operator then looks back on the last four or five runs. They are looking for what tooling to use as well what specific machine settings to begin at. Completely subjective.
4) Machine is started and tune in begins.

database mission statement:


This database will collect all data from runs (every eight hours). This data will be used to predict future machine and tooling setups based on lot numbers that have passed QA. The drawing operators will record the data via laptop computer. The database output will be introduced to a printable form for the operators to use to start drawing machines. This data will also be available to designated associates. The forms will be customized to the needs of the department.

[jzwp11]

Could you please provide more information on your table structure? An image of your relationship window would work or you could just type it out long hand like the following:

tblProducts
-pkProductID primary key, autonumber
-txtProductNumber

tblMachines
-pkMachineID primary key, autonumber
-txtMachineName

tblProductLots
-pkProdLotID primary key, autonumber
-fkProductID foreign key to tblProducts
-fkMachineID foreign key to tblMachines
-txtLotNo
-dteLot (date of the lot)

tblProductLotSettings
-pkProdLotSetID primary key autonumber
-fkProdLotID foreign key to tblProductLots
?

[adevine]

I have attached my relationships screen. (I think). I could also email the entire db if it would be easyer for you to understand. I want to thank you in advance for taking time to respond. I also want to say i am in no way asking for you to do my work for me. Just give me a push.

[jzwp11]

I see several issues with your tables that need to be addressed first. First, I see field names that occur in more than one table. Like data should only be stored in 1 table. You do not show any relationships between the tables, so we will have to work on that as well.

Let's start with the product and product type tables. Are the fields in the product type table actual product types? If so, the actual product types should be records in the table not fields Also, why would you have lot number as a product type? Could you please give some examples of your products and what type(s) describe them?

[adevine]

Yes the fields in the product type are actual product types. I understand now that the lot number should not be a product type. I did not see that before. A typical product would be (4240) 4 digit..... alot number would be digit alpha 3 digits. Any product number can run on machine#1,2 or 3. there is no relationships between tables only because I have tried several things and it has not worked out yet. Does this help any?

[jzwp11]

OK, if only 1 product type can be associated with a product then you would have this structure

tblProducts
-pkProductID primary key, autonumber
-ProductNumber
-fkProductTypeID foreign key to tblProductTypes


tblProductTypes (each type would be a record in this table)
-pkProductTypeID primary key, autonumber
-txtProductType

Now, I assume that a product can have many lots which describes a one-to-many relationship. Now, can a lot of a product only be produced on 1 machine or many? Do you have a table that holds the machine info (i.e. model number, serial number etc.)? Each machine would be a record in this table.

[adevine]

You are correct lot of a product only be produced on 1 machine. The table will only hold the machine number that is all.

[jzwp11]

Expanding on the structure:

tblProducts
-pkProductID primary key, autonumber
-ProductNumber
-fkProductTypeID foreign key to tblProductTypes


tblProductTypes (each type would be a record in this table)
-pkProductTypeID primary key, autonumber
-txtProductType

tblMachines
-pkMachineID primary key, autonumber
-txtMachineName

tblProductLots
-pkProdLotID primary key, autonumber
-fkProductID foreign key to tblProducts
-fkMachineID foreign key to tblMachines
-LotNumber

Now, you will have to tell us more about the data associated with each product/machine combination that you want to capture.

[adevine]

I need to an average of the following entrys from SETUP DATA.

Cone ht,cup temp, rs,rpm.Gas,oxy,span,bottom size,cone size, and did it pass or fail QA. I will work on making the changes you have suggested for a new screen shot. If I can just get the propper structure in my mind I can run with it....I can't thank you enough for your time and help....

[jzwp11]

I need to an average of the following entrys from SETUP DATA.

We're not there yet, we still need to properly structure the setup data table

I assume that there are various setup parameters that you want to track for each lot. What are those setup parameters. The parameters should be records in a table and then we have to relate each to each lot with the associated value (I assume). Something like this:

We would carry the previous tables forward:

tblProducts
-pkProductID primary key, autonumber
-ProductNumber
-fkProductTypeID foreign key to tblProductTypes


tblProductTypes (each type would be a record in this table)
-pkProductTypeID primary key, autonumber
-txtProductType

tblMachines
-pkMachineID primary key, autonumber
-txtMachineName

tblProductLots
-pkProdLotID primary key, autonumber
-fkProductID foreign key to tblProducts
-fkMachineID foreign key to tblMachines
-LotNumber


...and now we need tables for the setup parameters

tblSetupParameters (I assume you will need records for these: Cone ht,cup temp, rs,rpm.Gas,oxy,span,bottom size,cone size)
-pkSetupParameterID primary key, autonumber
-txtSetupParameter

...and a table that holds the values for those parameters for each product lot

tblProductLotParameters
-pkProdLotParaID primary key, autonumber
-fkProdLotID foreign key to tblProductLots
-fkSetupParameterID foreign key to tblSetupParameters
-measuredvalueofsomesort

[adevine]

I will try to digest this today and hopefully give you a answer you can understand tomorrow. I know have said it several times but THANK YOU.....what part of the US are you in? Im in St louis Mo.....

[jzwp11]

You're welcome. I'm in the Dayton, OH.

[adevine]

The setup parameters that I need to track are now in... tblSetupParameters
Here is a list of machine settings
Cone ht.,cup temp,Rs,RPM

And tooling will be
cup size,cone size,cup gas,cup oxy, cup span

these settings will be sent to a report that will only use the setups that passed QA and then averaged fo a start up point. I think I still need to work on my foreigh keys....

[adevine]

Here is a shot of my work....

[adevine]

Here is the screen shot