Are If statements possilbe in Queries?

[Iram]

Hello,
I have a subform and in this suform I have a combobox field called
"Category". In this combobox I have a select query that pulls the Category
and TeamName. On the master form I have a floating combobox called Team. When
I choose a team in this field I would like the subform combobox to be
narrowed down a little as such if possible...

If the Team field on the master form is ACCOUNTING I would like the Category
combobox to be limitted to "Accounting" and "Other" team names.

If the Team field on the master form is ESTABLISHMENT I need the Category
combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".

If the Team field on the master form is ENFORCEMENT I need the Category
combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".


If this is not possible how else can I do this?


Thanks.
Iram/mcp

 

[Dorian]

It seems from your description that your tables are not normalized since you
have values in a related table that are dependent on the values in the other
table. I would think carefully about yoour table design.
What tables are yopu dealing with?
Ideally, your possible combinations of main form combo box value and subform
combo box values should be defined in another table.
However, there is no reason why the query in the subform combo box cannot be
dynamically changed when the value in the main form combo box is changed.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Marshall Barton]

I have a subform and in this suform I have a combobox field called
"Category". In this combobox I have a select query that pulls the Category
and TeamName. On the master form I have a floating combobox called Team. When
I choose a team in this field I would like the subform combobox to be
narrowed down a little as such if possible...

If the Team field on the master form is ACCOUNTING I would like the Category
combobox to be limitted to "Accounting" and "Other" team names.

If the Team field on the master form is ESTABLISHMENT I need the Category
combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".

If the Team field on the master form is ENFORCEMENT I need the Category
combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".

Fairly straightforward, once you understand how relational
data bases do things. What you need is three tables to
model the many to many relationship between the categories
and the teams. You probably have a table for teams and a
table for categories, but you should also have a
TeamCategories table with just two fields, one for a foreign
key back to a categories record and the other field for a
foreign key to a related record is the team table.

With that in place, you can use a query as the category
combo box's RowSource that selects only category records
that agree with the team selected in the team combo box:

SELECT CategoryPrimaryKey, CategoryName
FROM Categories INNER JOIN TeamCategories
ON Categories.CategoryPrimaryKey =
TeamCategories.CategoryForeignKey
WHERE TeamCategories.TeamForeignKey =
Forms!yourform.teamcombobox

Then, to sync the category combo box whenever you select a
team on the main form, add a line of code to the team combo
box's AfterUpdate event:
Me.subformcontrol.Form.categorycombobox.Requery

 

[Iram]

Thanks Dorian and Marshall.

Actually what I need is the Category combobox field to be limitted to
certain teams and not so much just a single team.
If I am part of Accounting I want to see categories belonging to Accounting
and Other

If I am part of Enforcment I want to see categories belonging to
Enforcement, Establishment, OP and Other.

If I am part of etc....

This is why I need the If statement to be customized accordinally.


Thanks.
Iram/mcp

 

[PieterLinden via AccessMonster.com]

Actually what I need is the Category combobox field to be limitted to
certain teams and not so much just a single team.
If I am part of Accounting I want to see categories belonging to Accounting
and Other

If I am part of Enforcment I want to see categories belonging to
Enforcement, Establishment, OP and Other.

If I am part of etc....

This is why I need the If statement to be customized accordinally.

Thanks.
Iram/mcp

Sounds like this is what you want:
http://www.mvps.org/access/forms/frm0028.htm

 

[Marshall Barton]

Let me try again to help you understand why you need the
third table. This type of table is called a "junction"
table is the relational database way of representing a many
to many relationship between the other two tables. In your
case, it can be used to join many teams to each category and
many categories to each team. For example, lets say you
have:

tblteams
TeamID AutoNumber Primary Key
TeamName Text
. . .

tblCategories
CategoryID AutoNumber Primary Key
CatName Text
. . .

tblTeamCat
TeamID Long Foreign Key
CatID Long Foreign Key

Then you can populate the tables with something like:

tblteams
1 Accounting
2 Establishment
3 Enforcement
4 Legal

tblCategories
1 Accounting
2 Establishment
3 Enforcement
4 Legal
5 Other

tblTeamCat
1 1
1 5
2 2
2 3
2 4
2 5
3 2
3 3
3 4
3 5
4 3
4 4

With that in place, you can use something like the query I
posted to do what you want.