I am at the planning stage for a new database which will monitor the quality of work done by staff daily (Assessing applications). There are around 20 error areas in these applications and 3-5 error types for each area.
I designed a database 2 years ago for the same purpose in the format below:
Field Data type
Error Area A Look up error type
Error Area B Look up error type
Error Area C Look up error type
So each error area had its own field which would be set by default to (N/A). This was fine for recording errors, but became very cumbersome for designing dynamic reports.
Ideally, I would do something like this:
Field Data Type Error Area Look up list of error areas Error Type Look list of error types for error area selected above
The problem is there can be more than one error made in each assessment done. I would like to be able to record multiple values for both fields which are linked to each other, example I want to select the following errors on my form:
Error Area Error Type
A A1 (Error type dropdown will list possible areas for error Area A: A1, A2, A3, A4...)
C C4
F F5
Can anyone tell me the best way to set about doing this?