Seeking Table Advice Please (1 Viewer)

Hi All,

I've set up a few databases now and feel reasonably confident in their creation. however this project is a little more complex. I would like to set up a database for recording and analysing investigation data based on the attached MEDA document. I'm really not sure what the best structure would be. any advice is gratefully received.

I guess the most important data is the reference number for the investigation such that I can be sure the data is collated correctly for each investigation. I think the thing that is causing me most confusion is the multiple sub categories in each of the sections.

thanks in Advance for any help given.

Steve

think the thing that is causing me most confusion is the multiple sub categories in each of the sections.

Click to expand...

Not enough info to provide a solution but would suggest a single table along the lines of

PK
SectionNo (e.g. Event or maintenance error etc)
QuestionGroup - 1,2 3 etc - might be a FK to another table to provide the description
Event - a) flight delay, b) Cancellation etc

(Or you could have separate tables for each group)


then another table for your results form general information

and a third many to many table to provide the results to the first table

this table may need supplementary fields or perhaps a separate table (for example for length of flight delay, describe the incident etc)


Not clear whether this is a single answer per group or multi choice, either way the above structure should be able to handle both ways - the difference will come in the design of the data entry form. (single answer could be a combo, multi choice a subform)

CJ, many thanks for your reply I really appreciate. I had thought of going this way but wasn't sure whether a table for each group was the right way. There can be multiple selections for each category appropriate to the event so multi choice is the way to go.