Automatically fill field based on keywords in another field.

[Darwin]

I think this is a table question, if not please feel free to berate me and move it to the right place, I also think the question has been covered before, but any of the searches I try don't quite give a solution to my issue. (At least not one I can understand.)

I'm trying to complete a database.

It is to manage details of pupils with additional support needs, and plan for the extra arrangements the school will provide for assessments.

It has 2 Tables

tbl-PupilDetails
-ScottishCandidateNumber primary key (Unique number which identifies pupils to the exams board)
-Forename
-Surname
-DOB
-YearGroup
-Class
-NatureOfNeed (memo)
-EvidenceOfNeed (memo)

tbl-SubjectLevelArrangement
-ID Primary key, Autonumber
-Pupil foreign key to tblPupilDetails
-Subject
-Faculty
-Level
-Arrangement

I currently use forms for adding new pupils, and updating pupil subjects/arrangements/levels.

I also have forms to search for specific pupils, and to create lists for faculty heads showing which pupils are taking subjects within their faculty and the arrangements we expect to provide.

I use the forms to run queries, which can then output to reports for printing.

Where I am currently having an issue is the faculty field in the tbl-SubjectLevelArrangements. (If I didn't have to report to faculty heads I would just leave it out, but management will insist.)

Currently I have a form with dropdowns for adding subject, faculty, level and arrangement manually. This is acceptable for the subject, level and arrangement because they are completely interchangeable and dynamic throughout the academic year as pupils may drop down a level, or change the type of arrangement they require.

However as faculties are inextricably linked to subjects, I want to remove the possibility of human error. i.e. when a user (me) chooses either geography, history, or RE, then the faculty will always be Humanities, likewise if the user chooses French, German, or Spanish, then the faculty can only be Modern Languages etc.

I'm convinced there must be a very simple way to ensure that the faculty field prefills based on the limited keywords available in the subject field, but I just cannae figure it out.

Any pointers in the right direction will be much appreciated, but please keep it simple as I am a complete novice and what I have achieved so far has been on a very steep learning curve.

Any more info required just ask.

Thanks in Advance Darwin.

 

[spikepl]

If Faculty is uniquely given by Subject, then Faculty has no business in your table, because it can always be derived from Subject. You need to store the information which Faculty belongs to which Subject in a separate table.

For presentation (in form or report), you just make a query pulling data from the relevant tables.

 

[Darwin]

If Faculty is uniquely given by Subject, then Faculty has no business in your table, because it can always be derived from Subject. You need to store the information which Faculty belongs to which Subject in a separate table.

For presentation (in form or report), you just make a query pulling data from the relevant tables.

Thanks I think?

How would I set up that table? And how would I relate it to the subjects?

I already use lookup tables for subject, level and arrangement, would I need/be able to incorporate the faculty information into the subject lookup. I tried this previously, but got myself tied in knots. (Probably because I wasn't doing it in a logical way)

When it comes to reporting I only use the faculty information to pull together the range of subjects within that faculty, as my reports display the pupil subject information only.

 

[jdraw]

Further to spikepl's comments, you should research Normalization. A great tutorial to take you through the steps of designing your database can be found at
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
This tutorial shows the problem and leads you through all steps. It also shows the solution.

Good luck with your project.

 

[Darwin]

OK, I've read up those suggested links on normalisation and a bunch of others as well.

Now as far as I can tell my tables were almost normalised I just needed some tweaks.

I now have

tbl-PupilDetails
-ScottishCandidateNumber primary key (Unique number which identifies pupils to the exams board)
-Forename
-Surname
-DOB
-YearGroup
-Class
-NatureOfNeed (memo)
-EvidenceOfNeed (memo)

Although YearGroup and Class are both repeating, it actually makes sense to keep them in this table as they link to nothing else so would both end up as tables on their own, and as house never changes, and year just rolls on in August I am happy leaving them like this for now.

tbl-SubjectLevelArrangement
-ID Primary key, Autonumber
-Pupil foreign key to tblPupilDetails
-SubjectID foreign key to tblLookupSubject
-LevelID foreign key to teblLookupLevel
-ArrangementID foreign key to tblLookupArrangement

This table was already pulling Subject, Level and arrangement from lookup tables of the same names, I have just adjusted them to have an autonumber primary key, which means if any of them change at school level (which though rare can happen), the change in the lookup is now reflected in the linked tables.

tbl-LookupArrangements
-ArrangementID Primary Key, Autonumber
-Arrangement

tbl-LookupLevel
-LevelID Primary Key, Autonumber
-Level

tbl-LookupSubject
-SubjectID Primary Key, Autonumber
-Subject
-FacultyID foreign key to tbl-LookupFaculty

tbl-LookupFaculty
-FacultyID Primary key, Autonumber
-Faculty

As you can see I have split Faculty into a table of it's own which links to the Subject Table.

Only trouble is I still don't know how to include the faculty search into my queries.

Simply including the table and trying to return faculty now gives me Ambiguous outer join errors, with suggestion of creating another query to perfom that join first. I have tried this in lots of different ways, but always the same error.

I know this is something which should be simple which I am doing wrong.:banghead:

Any suggestions?

 

[Darwin]

P.S. By all means please tell me if you think I have got normalisation all wrong. I tried both working from a decomposition point of view, but also from an entity relationship point of view.

Both made my head hurt.

 

[Darwin]

Just to let you all know I fixed this in the end.

It seems I just needed to rebuild my queries from scratch to solve the outer joins errors. Looking at the properties of the original query which I modified to take account of new structures, and the completely new new query showed them to be identical, so I have no clue why the original wouldn't work.

Just glad it's all working now. It's been a massive task to get it working just right, but this means that next year all I have to do is enter pupil names and details and then all the related paperwork will be produced at the click of a mouse.