Quality Database

[Top Fuel Friday]

Hi guys,


New to the forums and I'm looking for some feedback on a database design.

I have a team of auditors that conduct quality audits for various functions in a fulfillment center. We use access to keep track of the audits and run various reports that go out to my fellow department heads and our facility managers. The database was set up by a previous manager many years ago and I'm tasked with creating a new database.

Currently we have 11 audits, which all have various standards we inspect. For example, one record for process 1 will have 5 fields for pass/fail of various aspects for the audit.

Right now each individual audit has it's own table with it's own criteria. So I have 11 tables in this database. Many of the audits share criteria, such as is the item dirty or damaged.

Would it be better to have one table with all the fields, but only have the forms input data in the fields for that audit? Then when we run the querys or reports we're pulling from the same table rather than 11? Or is it better to have the data in separate tables.

Some things that It does not do now, that have to be done in the new database are below:

• List the total amount of audits each auditor does on a daily basis across all audits
• generate an end of shift report that has total ammount of audited product and how many were failures for each of the 11 audits.

[NTC]

well - it depends. Not sure how much work you want to do - and also a major re-design will make it difficult to port in & look at old data.

11 tables at ~5 each - if merged in a horizontal design = ~55 fields and that is problematic generally. While if they are all just checkboxes and therefore 1 character of data it won't blow up. But if they are of any significant data size then you could end up too wide. 2kbyte width.

On the otherhand 11 tables makes you do overall queries, that you seek, 11 times via brute force - which isn't ideal either.

The most normalized approach is to make a 3 column AuditNameList table to be used as lookup source. 3 column: autonumber, AuditName, AuditItem

You fill in those ~55 rows of data (AuditName is how you differentiate the 11 different Audits)

Then you make the AuditData table - and it can be just 4 columns:
autonumber, AuditName, AuditItem, AuditValue

AuditItem is a lookup field of the AuditNameList table as its table field property. AuditName is a convenience field that can auto enter behind the scenes. AuditValue is just what is says - but you can't mix data types - so if some are checkboxes and some are text or number - - then the field is a text type.

You then make 11 different forms, and tweak each to gather the info as needed. ie. You can have checkboxes in the forms that then put in a value into the table.

A bit of work, a bit more elegant, your queries now are on 1 table. Actual number values, that you need to perform math with would need to be recast during queries which can be a hassle. Is it worth it? your call.

hope this helps.

[Top Fuel Friday]

I ended up making it a horizontal database. A lot of the fields for one audit are shared through out all the audits. There ended up only being 11 fields total. I have another table with a list of audits that propagate into an audit type field, and each of the audit categories stores a 0 for a pass and a 1 for a fail.

I made a form for each audit that fills in data for the fields that pertain to each audit and the rest are left blank in the record.