Multiple list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a 'shopping list' style of subform. Here is the situation, within our organization are several positions, within those positions are several levels (1,2,3). You can have more than one postion at different levels. For example, you can be a level 1 in 'bookstore', and a level 3 in 'maintainance.' I've already created a 'shopping list' and given each postion a code, (example bookstore has 3 levels, level 1 is BOO01, level 2 is BOO02, etc...pretty basic). But I dont know how to create a form, subform or whatever I need so that if someone has more than one position it can be indicated, and then when I run my query and ask for everyone in 'bookstore' regardless of the level, it comes up.
 
Hi Kelly,

I'll assume that right now you have two tables:

tblPeople
PeopleID
FirstName
LastName
etc.....

tblPositions
PositionID
PositionName 'Book01, Book02, Maint01, Maint02, etc....

One person can have many positions and one position can be assigned to many people - therefore you need a junction table:

tblPeoplePositions
PeoplePositionID 'PK - autonumber
PeopleID 'FK - foreign key, long integer
PositionID 'FK - foreign key, long integer

Your people form could have a listbox that displays all of the positions associated the selected people record. Your position form could also have a list box that lists all of the people associated with the selected position record. Plus, you can have a form based on the junction table with a listbox that is filtered by the selections made in the People combo or Position combo. On this form, have a "New" command button that is coded to move you to a new record, then you can select from the drop-downs to make the assignments.

Hope this helps.
 
You know what else - you might want to consider having a department table:

tblDepartment
DeptID
DeptName 'Bookstore, Maintenance, etc....

And have your positions table like this:

tblPositions
PositionID
DeptID 'FK - foreign key to link department names for future queries....
PositionName

Basically with this setup, when you're entering positions you are linking the level / position name to the department. It's a more normalized table structure.
 
Ok...so with these two samples, in addition to creating another table, I would need to define the relationship right? wrong? On the junction table would I then build a relationship between PeopleID in the People table and PositionID in the Positions table??

So then when I put this into my 'main' form I would do a drop down menu? How do I select more than one postion for a drop down menu? Do I need to duplicate multiple drop down menus....

I am getting this at all or am I way off???
 
Back
Top