Good morning,
Okay - I am relatively new to Access, but understand some theory behind relational tables and the principles of good design. However - I would really appreciate some guidance or comments on a project I have been asked to complete.
We have a new project starting within a Local Authority that deals with helping Young People into employment. At the moment, we need to collect basic data on participants in the project to keep for reporting, but I also want to link it to tables that include geographical/demographic data, info on vulnerable groups, and links to other projects that they will be referred to. This will allow for expansion and links to other project data for more flexible reporting.
So - I have attached pics of the tables I envisage being part of the database. I have a Participant table that includes basic data we need to keep, as well as fields that I want to link to other tables. In the District Code and Engagement columns, you will see a number. I though this would link as a secondary key (is that the right terminology?) to the Engagement and District tables (also attached as an example), so that they can be updated and always carry a numerical code that links to the Participant table to avoid duplicating data. This can also be done for other characteristics that would also have their own tables.
One issue I have is that the details in the Pathways table show projects that participants can be referred on to. However, there could be multiple projects. So - is it better to have checkboxes (Yes/No) to give all the possible outcomes of combinations of projects and they all have a number that could then go in the fields in the Participant table?
I hope this is vaguely clear - any comments will help me shape this in more detail and will be very gratefully received.
Best wishes,
Ben