Help figuring out query

[Jdougal via AccessMonster.com]

I have created a form that has 4 drop down menus to input information into a
single table. The records include:
Name, Shift, # People, and Assignment

For assignment I need to have it display only a certain group of assignments
depending on the # people on the shift. Example if there are 4 people on a
shift you can only select the assignments 1,3,5. Where if there are 2 people
you can select assignments 1,2,6.

How do I go about doing this? I've attempted to using the query table but
with no results. Can anyone give me some help in this area?

Thanks

 

[[MVP] S.Clark]

Create a table with this structure and data

People, Assignment
4, 1
4, 3
4, 5
2, 1
2, 2
2, 6

After the number of People is entered/selected, then change the SQL string
of the Assignment combobox to query this table for the allowable values.

 

[Jdougal via AccessMonster.com]

Ok I'm not exactly sure about the SQL string. How would I go about doing that?


So I would create
Tbl Breakdown

and have the following data

People Assignment
4 1, 3, 7
4 2,4
4 5
4 6
3 1,2,4
3 5
3 3,6,7
2 1,5,7
2 2,3,4,6

Create a table with this structure and data

People, Assignment
4, 1
4, 3
4, 5
2, 1
2, 2
2, 6

After the number of People is entered/selected, then change the SQL string
of the Assignment combobox to query this table for the allowable values.

I have created a form that has 4 drop down menus to input information into
a

[quoted text clipped - 12 lines]

Thanks

 

[Jdougal via AccessMonster.com]

Ok here is what I tried and nothing comes up, what do I need to edit?

SELECT Assignment.Assignment, Assignment.[# of people], SignUp.[# People]
FROM Assignment INNER JOIN SignUp ON Assignment.Assignment = SignUp.
Assignment
WHERE ((([Assignment]![# of people])=[SignUp]![# People]));

This comes from the combo box on the orginal form. It is under the row source,
the row source type is table/query.

Ok I'm not exactly sure about the SQL string. How would I go about doing that?

So I would create
Tbl Breakdown

and have the following data

People Assignment
4 1, 3, 7
4 2,4
4 5
4 6
3 1,2,4
3 5
3 3,6,7
2 1,5,7
2 2,3,4,6

Create a table with this structure and data

[quoted text clipped - 14 lines]

 

[Jdougal via AccessMonster.com]

Anyone?

Ok here is what I tried and nothing comes up, what do I need to edit?

SELECT Assignment.Assignment, Assignment.[# of people], SignUp.[# People]
FROM Assignment INNER JOIN SignUp ON Assignment.Assignment = SignUp.
Assignment
WHERE ((([Assignment]![# of people])=[SignUp]![# People]));

This comes from the combo box on the orginal form. It is under the row source,
the row source type is table/query.

Ok I'm not exactly sure about the SQL string. How would I go about doing that?

[quoted text clipped - 21 lines]