Need dependency between lookup fields

[djl0525]

T-Training_Sessions contains training data. Fields include class name, class date, and class instructor. There are about a dozen instructors in the database and each class can have two; Instructor 1 and Instructor 2.

I have created my table. It looks good. I have a dropdown for the instructors.

The issue I am facing is, how to ensure that the same instructor is not selected twice for a class.

How can I make sure that "Bob" is not selected as Instructor 2 if he has already been selected as Instructor 1?

Do I need VBA to do this?

Thank you in advance!

DJ

 

[Micron]

I'd ditch the lookup table fields partly because it's generally considered bad practice (I do not use them)

The Access Web - The Evils of Lookup Fields in Tables

and partly because of the issues they can raise. What's stored in a hidden table is the index value of the option you've chosen. When trying to get at this data, you'll often have issues because the underlying related field is numeric. Lastly, users should not be editing tables directly anyway. That's what forms are for. To attempt to answer your question I'd say make the field indexed and set dupes allowed property to No. I have to assume you can do that, same as for any normal indexed field, because as noted I don't use lookup fields in tables.

 

[djl0525]

Thank you for your quick reply and the link. I'll check it out!

Clarification: I don't intend to edit the data in the tables. I haven't created any forms yet so I grabbed a screenshot of the table.

Can I prevent the fields from matching in forms?

 

[Micron]

I took a closer look at the pic this time, having more or less ignored the fields when I saw the table lookup. What you have there is known as repeating fields and that's going to cause you issues as well. This means you have 2 or more fields for the same "thing" or attribute - the chosen instructor, because you're designing this like a spreadsheet, which is not good. One of the worst complications of this is what you'll need to do if you ever need to add a 3rd field for like data, i.e. alter design for every dependent form, query, report or code. You may think there will never be 3rd instructor but you cannot rule that out. However, it really doesn't matter if it would ever happen as much as if it could ever happen. Your instructors should be in their own table, and your Session_Instructors should be a junction table between tblInstructors and tblSessions because you have a many to many relationship. Many instructors can be assigned to many sessions. I'd advise you to research and understand db normalization. Find references you like if these don't help:
Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

I don't intend to edit the data in the tables.

Then you definitely don't want table lookup fields seeing as they exist only to avoid normalization and to work directly in tables.

Can I prevent the fields from matching in forms?

Yes, but what I said about making the fields indexed with no dupes would ideally apply to one field once you have the tables set up correctly. If you stick with what you have, I'd use cascading combos (one combo on the form per field) and eliminate from the 2nd list whatever was chosen in the first combo.