Quality Check Database Design

[lone_rider15]

Hello Everyone,

I have a table with following fields.
ID, ProfileName, ProfileId, NumberOfEvents, EntityId, RequeueReason, Notes, ReviewedBy, ReviewDate
I want to develop a process for quality check. Where I would need following fields.
ProfileName, ProfileId,NumberOfEvents, ReviewedBy, ReviewDate, Audited, Error, AuditedBy, AuditDate

There are 9 types of errors with different weight. A record can have multiple errors.

Now what we do is copy a record from the first table and paste it to an excel. Then we list errors if there are any, who and when audited.
Should I go for another table for the quality check or include the fields in the existing table as I did in the attached sample database. I have attached an Excel file as well.

Hope someone can look into my database and the Excel file and point me to the right direction.

Thanks in advance.

 

[static]

Should I go for another table for the quality check or include the fields in the existing table as I did in the attached sample database.

Tables should be split into types of data.
Here you have a profile, audit information, error information, general notes (never include memo fields in your main tables), and possibly other stuff.
So, that's at least 4 tables.
If you have more than 10 fields in a table, you probably want to review your design.

I know your sample contains example data but I would expect ProfileID to be the Primary Key and contain unique values.

 

[lone_rider15]

Tables should be split into types of data.
Here you have a profile, audit information, error information, general notes (never include memo fields in your main tables), and possibly other stuff.
So, that's at least 4 tables.
If you have more than 10 fields in a table, you probably want to review your design.

I know your sample contains example data but I would expect ProfileID to be the Primary Key and contain unique values.

This table is actually a log users keep what they work everyday. They get below fields from somewhere else.
ProfileName, ProfileId, NumberOfEvents, EntityId, RequeueReason, Notes

ReviewedBy and ReviewDate are for tracking who and when entered the data into this database.

ProfileId-- can't be unique because a profile can be worked multiple times by multiple users on multiple day.

Audit information will be only a Yes/No field. If I separate Error information in another table won't I have to add ProfileName, ProfileId, NumberOfEvents, ReviewedBy and ReviewDate into that table again?

Thanks for your valuable input.

 

[static]

If I separate Error information in another table won't I have to add ProfileName, ProfileId, NumberOfEvents, ReviewedBy and ReviewDate into that table again?

You link multiple tables together in a Query and select the fields you want to view.

Tables are joined together by unique identifiers. That's why Access likes to create Autonumber ID columns for you.

http://webs.wofford.edu/whisnantdm/Courses/CS101/PDF/Database/Relational_database_concepts.pdf

 

[lone_rider15]

I have added another table for Error Details. Created a relationship

Created a from with master and child

Child ProfileId does not update and the record set is not updateable as well. Can anyone please take a look into my database?
Thanks in advance.

 

[static]

For a recordset to be updateable the tables need to be linked on primary keys.

So, you need to add your Review.ID to Audit and Audit.ReviewID and ProfileID would be the primary keys to match Review.

 

[lone_rider15]

For a recordset to be updateable the tables need to be linked on primary keys.

So, you need to add your Review.ID to Audit and Audit.ReviewID and ProfileID would be the primary keys to match Review.

Thanks! Problem solved.