MS Access 07 - Q re lookup column vs many-to-many; Q re checkboxes in many-to-many forms
- by TBinLondon
Hello,
I'm creating a database with Access. This is just a test database, similar to my requirements, so I can get my skills up before creating one for work. I've created a database for a fictional school as this is a good playground and rich data (many students have many subjects have many teachers, etc).
Question 1
What is the difference, if any, between using a Lookup column and a many-to-many associate table?
Example: I have Tables 'Teacher' and 'Subject'. Many teachers have many subjects. I can, and have, created a table 'Teacher_Subject' and run queries with this.
I have then created a lookup column in teachers table with data from subjects. The lookup column seems to take the place of the teacher_subject table. (though the data on relationships is obviously duplicated between lookup table and teacher_subject and may vary). Which one is the 'better' option? Is there a snag with using lookup tables?
(I realize that this is a very 'general' question. Links to other resources and answers saying 'that depends...' are appreciated)
Question 2
What attracts me to lookup tables is the following: When creating a form for entering subjects for teachers, with lookup I can simply create checkboxes and click a subject for a teacher 'on' or 'off'. Each click on/off creates/removes a record in the lookup column (which replaces teacher_subject).
If I use a form from a query from teacher subject with teacher as main form and subject as subform I run into this problem: In the subform I can either select each subject that teacher has in a bombo box, i.e. click, scroll down, select, go to next row, click, scroll down, etc. (takes too long) OR I can create a list box listing all available subjects in each row but allowing me to select only one. (takes up too much space). Is it possible to have a click on/off list box for teacher_subject, creating/removing a record there with each click?
Note - I know zero SQL or VB. If the correct answer is "you need to know SQL for this" then that's cool. I just need to know.
Thanks!
