Form with multiple dropdown filters

[temple owls]

Yea, so its only one preference per appt type per employee per location

so essentially its when "Employee 1" works at "Location 1" with "appt type 1", he/she prefers "preference 1"

 

[temple owls]

So I guess my question is, if I already have that junction table with the employees at their respective locations, how do I Cartesian that with every appt type?

Because you're right, that's definitely ideal in this scenario. I just don't know how to do that

 

[MajP]

Create a new junction table with nice naming convention. I would have fields
EmployeeID_FK
LocationID_FK
ApptTypeID_FK
Preference ' text field

In the appointment table rename it to tblAppointmentTypes. Change the PK name to AppointTypeID_PK.

Now build a query. Drop your old junction table and the appointment table in the query, without any joins. Pick the emp id, location id from the old junction table and the appointmentType ID from the appointment type table. See if you now have a record showing all appointment types per person per location.
Now in the query design go up to the top and select "append" (plus sign). You want to append these records to your new junction table. Just match the fields up.

 

[temple owls]

Got it!! Sweet!! You're legit teaching me so much lol

Now that I have a workable junc table and the naming conventions are accurate, how do I go about creating that glorious main form with sub forms from a few replies back?

That way I can start manually adding in the preferences

 

[MajP]

If you post an updated db, I can do a quick demo. It will not be aesthetically pleasing (you can do that), but I can quickly demo the capability. None of it is real hard, but would probably take me 5 times longer to explain the steps then show it to you. if there is data such as people names that you do not want shared then post the tables with a few dummy records.

 

[temple owls]

Attached! Thanks!

 

[MajP]

Here is the demo. Still needs lots of work. I added another junction table for employees and locations, but in truth you could do without it. However, you are going to need some code when you add/delete an employee to a location. You need to insert a record for every appt type for that emp for that location. And would need to delete the same when you remove an employee or remove employee from a location. I had to modify some of the datatypes as well.

 

[temple owls]

Thank you so much!

While users will be using the forms, I will be the one managing the database. While I'd like to make all changes using forms, what's your thoughts on just manually adding / deleting employees (and their subsequent appt types) in the tables themselves for now?

 

[temple owls]

And when adding employees, what if there was a form that had you input the name of the employee, select the locations from a list box, and lastly, all the appt types are listed out with a text box next to each one where you would type the preference?

 

[MajP]

I did not get around to it in the demo, but it should be easy when you add a user to a location to add all the appt types through an append for that user for that location. Same thing when you remove a user from a location, it deletes all records for that user for that location. So doing what you ask is basically a slight modification of the original form, by just swapping the employee as the main form and the locations as a subform and then the other subform with the preferences. Or you could just have an employee misinform, and a multi select listbox for each location. The preference form could just list all the locations and all the preferences for that employee sorted by each location. I am just away from any computer that has access so I cannot demo anything until next week. The big feature to add for any of these forms is to run the append or delete queries when adding or deleting locations. This is not involved. You want to google how to create a composite index. You want to put that on the junction table so that you cannot get duplicate combinations. Each combination of empID, location id, and appt type ID is unique.

 

[temple owls]

Do you know how I could get the address for the location to pop up on the main form when that location gets selected? I've tried to add it as another column and have a text box based on that but I can't seem to figure out the exact steps