Novice Access user - advice needed

[mairead]

Hi,
I'm creating a database with 4 options - Deferrals, Extension, Year Out, Subject Change. Each one is a table / form with details of students who fall into that particular category. Each form has the following fields in common: First Name, Last Name, ID, Category (D, E, YO or SC). A student could possible be entered in more than one category. Each table has the Student ID as the (only) primary key (is this good practise?)

The database is pretty straightforward but I'm stuck on this: I want to create a button on the entry screen that allows users to enter a student ID number. If the ID number exists on any one of the forms, the query will display the details: First Name, Last Name, ID and Category. Is a parameter query the best way to do this? I'm not an sql user (yet!) so my solution has to be low-tech. Thanks for any assitance.

 

[dcx693]

Sounds like the relationship between your students and categories is many-to-many. Many students can be in multiple categories.

You should have a student table and a category table. After all, why repeat each student's info on each category table? To match up students with categories, you'll need a linking table.

So, if your Student table looks like this:

StudentID Name
1             John
2             Joe
3             Tim

and if your Category table looks like:

CategoryID Category
1              Deferral
2              Extension
3              Year out
4              Subject change

your linking table will look something like:

StudentID  CategoryID
1              4
2              4
1              2
3              1

If you then want to look up a student, you can set up a search/entry form with a combo box listing the student names and IDs. You can then make a selection from the combo box and the form will pull up records for that student. I think you can use the button wizards for that.

 

[mairead]

Thanks for that! I should have emphasised that although a student could possibly be entered in one or more categories, this would be the exception. Most will be entered in one category only. So my main query is what is the best way to search all 4 tables from the entry screen? Would a separate search /entry form be the way to go?

 

[mairead]

will give it a go - thanks for that.