Help! - Going round in Circles

[Sleepymum]

Hi,
I hope some one can help, as I keep going round in circles

I currently have an excel log that is used to record our non-conformance data.
I am trying to setup an access database to record the above information and I would like the data to be entered using an user form.

I have limited experience using access and so far have messed around with a few examples. Out of this I have a few questions on how to set up the database correctly.

1. What is the best way to set up the tables?

These are the fields:
Non conformance number – Number format
Date Opened
Initiator – Would like this to be selected from list
Defect Owner – Would like this to be selected from list
Shift – Would like this to be selected from list
WorkCentre – Would like this to be selected from list
Major Quality Issue – Yes/No

Prd Number - – Would like this to be selected from list, If a number is picked it would autofill in the next 2 fields
Prd Description – See above
Material Type – See above
Material Defect – This would relate to the material type (again a drop of options)
Defect Description – General comments

Initial Amount – Manual input
Unit of Measure – Select from list
Initial Cost – This would be a calculation of the initial amount by unit cost (this is tied to the prd number field)

Supplier – Select from list
Supplier Details – auto fills from supplier picked

Final Disposition – select from list
Final Amount – manual input
Final Cost This would be a calculation of the final amount by unit cost (again this is tied to the prd number field)
Date Closed


Can anyone give me the best advice to lay this out. Do I need to set up look up tables as well as database like tables?

Thanks in advance for your help!

[jzwp11]

You might check out this thread where I provided some tips to another forum user just starting out with their table structure.

I've added some comments below that might help you flush out your table structure

These are the fields:

Non conformance number – Number format
Initiator – Would like this to be selected from list
Defect Owner – Would like this to be selected from list

I would assume that the above two fields refer to people. In order to select them from a "list" the best approach is to store the people in a table. The table will be your list.

Now since the non-conformance has at least these two people associated with it, it represents a one-to-many relationship one (non-conformance)-to-many (people). This would be handled with a related table that references the nonconformance as well as the associated people.

Date Opened
Date Closed

I assume that you will have many other actions such as opening the non-conformance, that you will be tracking. Again this describes a one (non-conformance)-to-many (actions) relationship which would be handled with a related table.


Shift – Would like this to be selected from list

I'm not sure about this field but again if you want to select something from a list, the list items should be stored in a table and you would just reference/relate it back to the table that holds the basic info about the nonconformance.



WorkCentre – Would like this to be selected from list
This sounds like a reference to a location, so again if you want to select from a list, store the locations in a table.

Major Quality Issue – Yes/No
Will you have other classification for the severity of the issue? If so, you would store a list of those classifications in a table and then reference/relate it back to the nonconformance

Prd Number - – Would like this to be selected from list, If a number is picked it would autofill in the next 2 fields
Prd Description – See above
Material Type – See above

All of the above fields would be put in a table that would hold the basic info about your products.

Now if a non-conformance is related to many products, then you would need a table that relates the two. If a non-conformance only relates to 1 and only 1 product then you would just reference the product in the non-conformance table.

Material Defect – This would relate to the material type (again a drop of options) I'm not sure I understand how a material defect would relate to the material type of a product. You will have to explain that further.

Defect Description – General comments


Supplier – Select from list
Supplier Details – auto fills from supplier picked

Supplier info will be store in a table and referenced back to the product as necessary.


Initial Amount – Manual input
Unit of Measure – Select from list
Initial Cost – This would be a calculation of the initial amount by unit cost (this is tied to the prd number field)



Final Disposition – select from list
Final Amount – manual input
Final Cost This would be a calculation of the final amount by unit cost (again this is tied to the prd number field)

These two sets of information can probably combined into one table and just delineate the whether it is initial or final.

[Sleepymum]

Thanks for the feedback.

Material Type is a breakdown of all the materials we have in use - Material Defect is the breakdown of defects that can occur for each of the material types (not consistent defect types across all material types)

So just confirming some stuff,

- -First I should set up individual tables for the "reference lists" that I need? Initiator,Owner,Shift,WorkCentre, etc....


- - Should I have then have a non-conformance table that has all the fields and then links to the individual tables for the drop down lists?, or do I divide them out?

Sorry for asking stupid questions, but I am really grateful for the help!

[jzwp11]

First I should set up individual tables for the "reference lists" that I need? Initiator,Owner,Shift,WorkCentre, etc....

Yes and no. Since initiator and owner are both people (I assume), you should have a table to hold all people. You will need tables for Shift and WorkCenter as well as one for the basic nonconformance info. As a definition a primary key field is usually a field that holds a unique number for every record in a table. Typically the value has no significance to the user. It is this field that when joined with a comparable field (in database jargon the comparable field is the foreign key field) in a related table, joins the two tables together and thus forms a relationship. I generally recommend having a primary key field that is an autonumber field in every table. (some would argue with me, so it will be ultimately your call)

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblShift
-pkShiftID primary key, autonumber
-txtShift

tblWorkCenters
-pkWorkCenterID primary key, autonumber
-txtWorkCenterName



tblNonConformance
-pkNonConfID primary key, autonumber
-fkWorkCenterID foreign key to tblWorkCenters
-fkShiftID foreign key to tblShift

Now since at least 2 people are associated with a non-conformance that is a one-to-many relationship which should be handled like this:

tblNonConformancePeople
-pkNonConfPeopleID primary key, autonumber
-fkNonConfID foreign key to tblNonConformance
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRole

The fkRoleID defines the role the person plays with respec to the non-conformance i.e. initiator, owner (or other role to be added in the future). So we need a table to hold all possible roles

tblRoles
-pkRoleID primary key, autonumber
-txtRoleName

You also mentioned that you had several material types, so it would be best to have a table to hold those

tblMaterialTypes
-pkMatTypeID primary key, autonumber
-txtMatTypeName

Now a table to hold all products

tblProducts
-pkProductID primary key, autonumber
-ProdNumber
-ProdDesc
-fkMatTypeID foreign key to tblMaterialTypes
-fkSupplierID foreign key to tblSuppliers


I also assume that a product is supplied by a supplier so we need to reference the supplier in the product table above. We'll also need a table to hold the basic supplier info

tblSuppliers
-pkSupplierID primary key, autonumber
-txtSupplierName



I'll stop there and let you digest it for a while. There is still more to work on such as the actions related to a non-conformance and the associated costs.

[Sleepymum]

Thanks a million! – You have been a real help.

I have been working away at this and have set up these tables as follows:

tblShift
- auto number as primary key
- Shift

tblWorkCenter
- auto number as primary key
- WorkCenter

tblProducts
- auto number as primary key
- ProdNumber
- ProdDesc
- UnitCost
Will have to add material type to this table but am unsure how to set up this table as 1 material type can have several types of defect related to it – Should I set up a table for each material type with their associated defects?
tblSuppliers
- auto number as primary key
- SupplierName
- SupplierDetails
I am linking the supplier to the products as yet, as there is a separate body of work going on to put this link together!

As regards the people side of it, the only person being tracked is the person who raises the ncr (initiator). The defect owner “tell” who the ncr is raised against – Ourselves, Supplier, Vendor,etc....
So would these need to be separated out or should I set up like

tblNonConformance
- auto number as primary key
- Date Initiated
- Initiator - Defect Owner - WorkCenterID foreign key to tblWorkCenters
- ShiftID foreign key to tblShift


Also we only track the ncr as being open or closed at the moment, but I think we prob will add in overdue as well!

Again thanks a milliion!

[jzwp11]

Will have to add material type to this table but am unsure how to set up this table as 1 material type can have several types of defect related to it – Should I set up a table for each material type with their associated defects?

Can the same defect be applicable to many material types? I'll assume that it is possible. As such, your structure would look like this:

tblMaterialTypes
-pkMatTypeID primary key, autonumber
-txtMatTypeName

tblDefects
-pkDefectID primary key, autonumber
-txtDefectName


Now associate the material type to its respective defects

tblMaterialTypeDefects
-pkMatTypeDefectID primary key, autonumber
-fkMatTypeID foreign key to tblMaterialTypes
-fkDefectID foreign key to tblDefects

You would then relate pkMatTypeDefectID to the product tied to the non-conformance?

By the way, can a non-conformance cover many products or just 1?

defect owner “tell” who the ncr is raised against – Ourselves, Supplier, Vendor,etc....

Since the defect owner is a company, you might change the supplier table to be a table that holds all companies (yourself, suppliers and vendors). You could then have a field that defines the type or category of company.

tblCompanies
-pkCompanyID primary key, autonumber
-txtCompanyName
-fkCompanyTypeID foreign key to tblCompanyTypes that defines a company as a supplier, vendor, or yourself)

As to your autonumber field names, you might want something more descriptive so that it is easier to see the relationships that need to be established.

[Sleepymum]

Thanks a milion for your help, jzwp11 you have been brilliant, It been a real learning curve for me and I really do apprecaite the time you have given me so far!

Can you have a look at the attached doc and let me know if I am heading in the right direction?

Also, can you clarify the best way to "relate pkMatTypeDefectID to the product "? Not sure if this is a query that I need to set up or the best method for it.

A non- conformance is for only 1 product at a time.

What are your recommendations for the next steps I need to take?

1. Set up the tbl:NonConformance and link in the rest of the tables?
2. Set up relevant queries?
3. Set up forms?
????

[jzwp11]

I assume that your tblProductCode&C..., is your table that defines all each product your company makes or receives, as such, you need to replace MaterialDefectID with a foreign key to tblMaterialType. With that you would not need the Material Type field that you currently have. So the table would look like this:

tblProductCode&C...
-pkProductCode&Co
-ProductNumber
-Description
-UnitCost
-fkMaterialTypeID foreign key to tblMaterialType

Also, I would recommend that you do not use spaces or special characters in your table or field names. The ampersand (&) has special significance in Acces and Visual Basic for Applications (VBA).

I'm not sure if your tblSupplierDetails table is correct. I would assume that a supplier provides products, so shouldn't you reference the supplier in tblProductCode&C... ? We are not yet to the point in defining the defects since that is part of the non-conformance, correct?

I am thinking that you need a table to hold the basic supplier information or as a suggested earlier a table of companies

tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName
-txtAddress

Then in your tblProductCode&C... reference the company that supplies the part

tblProductCode&C...
-pkProductCode&Co
-ProductNumber
-Description
-UnitCost
-fkMaterialTypeID foreign key to tblMaterialType
-fkCompanyID foreign key to tblCompany (the supplier of the part)

Yes, you will need to finish up your table structure before you move on to forms. You will need a table to hold the non-conformance info and a reference in that table to the product that is applicable to that non-conformance. With a specific combination of non-conformance/product, can there be many defects that need to be addressed? If so, that describes a one-to-many relationship. I'll let you try to come up with the appropriate design, please post back with any questions.

[Sleepymum]

What is the best way for me to set up these tables?Set Up 1 cost table?
How do I get it to work out the cost for me?

Initial Amount – Manual input
Unit of Measure – Select from list
Initial Cost – This would be a calculation of the initial amount by unit cost (this is tied to the prd number field)



Final Disposition – select from list
Final Amount – manual input
Final Cost This would be a calculation of the final amount by unit cost (again this is tied to the prd number field)

Also for the main NCMR table, do I link the Primary Key fields from the lookup table? something like:

tblNonConformance
- auto number as primary key
- Date Initiated
- WorkCenterID foreign key to tblWorkCenters
- ShiftID foreign key to tblShift
etc..........

Then the final question I think is a guideline to setting up the form? Do I need to set up queries first and then set up the form?

Thanks again!

[jzwp11]

Since the initial and final costs are calculated, you would not store them in a table, but calculate them on the fly when you need them in forms, queries or reports.


What do initial and final amounts represent? quantities of part impacted? What does Unit of Measure represent?

Also for the main NCMR table, do I link the Primary Key fields from the lookup table? something like:

tblNonConformance
- auto number as primary key
- Date Initiated
- WorkCenterID foreign key to tblWorkCenters
- ShiftID foreign key to tblShift
etc..........

Yes, you are correct in the structure shown above.


In terms of forms, you would base your main nonconformance form on the tblNonConformance and use combo boxes to popluate the WorkCenterID and ShiftID. Since only 1 product is applicable to a non-conformance based on your earlier post, you would use a combo box for that too (you'll need to add the foreign key in your table above).

Once you document the non-conformance, do you capture which particular defects are applicable (one non-conformance to many defects)?

[Sleepymum]

Thanks for the fast reply... Glad to know I am on the right track!

What do initial and final amounts represent? quantities of part impacted? What does Unit of Measure represent?

Initial amount is the amount we put on hold at the time the non-conformance is first found.
The reason we have a final amount is that we may find only one of the batch or 20 more batches etc has the non-conformance so we could have more or less than we origionally taught.

I have looked at the Unit of measure table and this is not being used for anything so I have gotten rid of it.

Once I have set up the main NCMR table do I need to set up a query on it, for use by the form so that the data links? - Can i pick say product code in the form and the description, material type fields are linked to this?


Once you document the non-conformance, do you capture which particular defects are applicable (one non-conformance to many defects)?

A non conformance is one defect to one product at a time!

Thanks again!

[jzwp11]

Since you have two amounts (initial and final) they represent the many side of a one-to-many relationship related to the nonconformance technically speaking. Some would say to just put the two values in the nonconformance table, I'll let you make that call.

tblNonconformanceAmounts
-pkNCAmtID primary, key autonumber
-fkNonconformanceID foreign key to tblNonConformance
-QTY
-field to capture the word initial or final

A non conformance is one defect to one product at a time!

You may be able to substitute the specific product/defect in place of the product since you related the products to the specific defects that might occur in your earlier table or at least I think you were working towards that.