Help with Safety DB set up

[dluga20]

Hello all,

This is my first post on the forum and new to access so bare with me..

We are making a safety database at my company. It will track our audits and open safety issues. Our audits are basic check lists (pass/fail) that we would like to convert to forms and reports within access rather than doing them by hand with hard copies. We want to have forms and reports for each type of audit, have one master list of all audits where there were "fails", and have a master list of all audits where we can track them, give due dates, assign to people, etc.



I know I can make forms and reports for each audit but I'm not sure if this is the right way to do it. I feel like each type of audit should have its own table, query, form and report but i don't know how to then make a master list containing all audit types so we can look at the audits that have fails.

Any suggestions are advice on how to set this DB up?

Thanks

[ItsMe]

My suggestion is to think of Access as two different things. Access is a Rapid Application Development Tool. Access is also an RDBMS. Because people are familiar with the forms and reports, they make an easy association with the application side of things. What drives the application is a database and it is important to understand how to interact with an RDBMS before developing the application.

Starting here should help
https://www.youtube.com/watch?v=-fQ-bRllhXc

[dluga20]

I think for the most part I understand the basics of DB side of access. I am not completely new to access or databases. The problem I am running into with this is like you said...the underlying DB set up. I will watch your video but i have watched many tutorials and I feel like the information out there on access and databases is either at the beginner level or the expert level. I was hoping I could close that gap by posting questions on this forum so thank you for your help.

[ItsMe]

...I know I can make forms and reports for each audit but I'm not sure if this is the right way to do it. I feel like each type of audit should have its own table...

If I am to consider this, I would say you can use forms and reports for each type of audit but I would not create a different report object every time an audit occurred. Also, one form may be able to accommodate many types of audits. There should be forms for entering data and there should be other forms for viewing data. Reports should be used for exporting data. Maybe you need to provide a summary of actions or store an image of your data from a specific point in time. A report is good for snapshots of your data at a specific point in time.

It is doubtful each type of audit will require its own table. However, there may be many fields that are unique for a specific audit. This may mandate a table for specific types of audits. However, I may be describing something you are not considering or you may not need.

The video I linked to is not my own. However, I have viewed it in its entirety and will link to it when I believe it is relevant. May I ask if you consider the information in the video informative or is it better to describe it as 'review material'?

[dluga20]

Each audit type will have different fields as well as different number of fields. I think a table would be needed for each audit type, but how would I then merge those different audit types into one list?

[ItsMe]

If you can get it all into one table, you will be going against the Rules of Normalization but you will avoid the need for a Composite Key. If I understand things correctly, you will need a Compound Key. Then, you will require a composite key when merging the different audit types. The composite key will be used when referencing that entity that has many audit types and many audit dates.

What makes your scenario unique is the audit types having attributes unique from one and another. Otherwise, a junction table would suffice to create a many to many relationship.

I would have to reference the project using an ERD to be sure. But, this is my best guess at this point.

[dluga20]

Maybe this will help you get an understanding for what I am doing/trying to do. Here is a sample table that would house the records for - in this example - all fire extinguisher audits. I would have multiple other tables for the other types of audits (eg fork lift, guarding, alarm system, etc). I would really like one master list that somehow shows me all of the audits that have a "fail" (an unchecked box). Obviously i can do this with a query for each individual audit type but I would like to have everything in one master list if possible.

[ItsMe]

I am familiar with audits of a single entity that are audited by various regulatory agencies. I am also familiar with the compliance requirements for some of these scenarios. I have built applications to manage such compliance issues. Depending on how many regulatory agencies are involved and the level of reporting/compliance necessary, you will need to have moderate to advanced database engineering skills.

[dluga20]

The parameters that I was given are that I need a way to do the audit check lists via MS access, document and keep a record of the audits, have some reporting functions, and have the ability to maintain a list of audits that report issues so we can follow up with corrective action. I didn't think this would be such a difficult task.

[ItsMe]

The parameters that I was given are that I need a way to do the audit check lists via MS access, document and keep a record of the audits, have some reporting functions, and have the ability to maintain a list of audits that report issues so we can follow up with corrective action. I didn't think this would be such a difficult task.

That's a good start. But, it is only a start. If I was handed that, I would proceed to interview the people involved (Users) to get an understanding of the entities and possibly some operations. With an ERD in hand, I would revisit operations to test the ERD. After doing some revisions, I would start creating tables and queries. I would then get some test data into the DB and see if my queries are functioning and see if the ERD needs to be revised. More interviews with the users and operations. After a few revisions to the the database, I would proceed with building the application.

Perhaps it would be beneficial to take a step back and create an ERD. It does not have to be very detailed. You can draw simple lines to connect your entities. Writing it down and thinking it through may help you to understand the relationships better. Then, start another thread in this forum. With your ERD in hand. It will be easier for others to help. Plus, you may have a better understanding about how the DB might/should be structured.

[dluga20]

I will try that. Thank you.

[ItsMe]

Just keep it simple. Maybe start with separate lists of all the different compliance topics, per agency. Consider those as separate tables and then as one table combined.

[Micron]

As a former compliance auditor and occasional application builder, I think what you have before you is no small task if the application is going to be robust. All the advice you've been given looks good to me, and if I may add, I will share what I've found to be helpful in the past.

[I think a table would be needed for each audit type]
To me, this means I would find most, if not all, of the same fields in every audit table - not the way to go IMHO.

One idea is to use a spreadsheet that shows for each table name you propose, the field names, types, data sizes, etc. (in columns) before you build any. This will assist in seeing the relationships between the fields and assist in ensuring you do not create incompatible data types such as text and number that you try to join. I used the table property sheet as a guide to see what colums were needed (such as indexed Y/N, PK, FK, dupes Y/N etc.).
Another is to try to think of data as compartmentalized information in tables (in other words, practice normalization).
What jumped out at me in your example and description of the goal was that maybe I would create a table of audit types and an AuditType field in your AuditID | Duedate | AuditDate | pass/fail | auditor | NCcount table to link them. Maybe an NC table to list the AuditID and the count of level1 | level2, etc. nc's? I remember one db (I didn't create it ) also had a list of questions in a table, which was printed out as a report because the audit answers were kept on paper. Not sure why all the checkbox fields in your sample, but I suspect you are suggesting to keep data in columns that should be in rows.
*by NC I mean nonconformace.

[dluga20]

From what I am hearing, I may be in over my head right now. I will just have to keep learning about DB set up and Access while posting questions as they come about. Thank you for all of your input. I am sure you will be seeing posts from me in the future.