Prev: Retain combo box order
Next: Is it possible to have If statements in a query?
From: Marshall Barton on 4 May 2010 18:38
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.
--
Marsh
MVP [MS Access]


Iram wrote:
>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.>
>
>
>"Marshall Barton" wrote:
>
>> Iram wrote:
>> >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
First  |  Prev  | 
Pages: 1 2
Prev: Retain combo box order
Next: Is it possible to have If statements in a query?