# Any Microsoft Access experts around?

**URL:** https://boards.straightdope.com/t/any-microsoft-access-experts-around/603017
**Category:** Factual Questions
**Created:** [November 15, 2011, 4:32pm UTC](https://boards.straightdope.com/t/any-microsoft-access-experts-around/603017 "2011-11-15T16:32:05Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)
#### Post date: [November 15, 2011, 4:32pm UTC](https://boards.straightdope.com/t/any-microsoft-access-experts-around/603017/1 "2011-11-15T16:32:05Z")

</div>

I’m after some help with an Access database that I’m building. I haven’t used Access for many years and I was never an expert in the first place…

I have a table called **Group**. In that is a field called _Group_.

I have another table called **Team**. In that table is a field called \*Group \*which looks up the \*\*Group \*\*table. Then it has a text field called _Team_.

So the content of the \*\*Team \*\*table is kinda like this:  
Group 1 | Team A  
Group 1 | Team B  
Group 1 | Team C  
Group 2 | Team D  
Group 2 | Team E, etc.

I’m building another table which is the main table of the database so far, and it contains a lot of fields. One of the fields in that table is _Group_, and it looks up _group.group_. The next field in that table is _Team_, and it looks up team.team.

I would like the drop-downs in the _Team_ field to only display those teams that belong to the \*Group \*that has been selected. So using my example content above, if I select Group 1, then the only drop-downs available to me in the \*Team \*field are Teams A, B and C.

I am now looking at the table in design view, and I’m looking at the Lookup properties. In the row source field, it has:  
SELECT [Group].[ID], [Group].[Group] FROM [Group] ORDER BY [Group];

I suspect there’s something I need to add here, to tell it to limit the list by whatever was selected in the field column. Is that right and can anyone help me?

---

<div class="post-metadata">

### Author: ![Clothahump](https://avatars.discourse-cdn.com/v4/letter/c/51bf81/32.png) [@Clothahump](https://boards.straightdope.com/u/Clothahump)
#### Post date: [November 16, 2011, 5:14pm UTC](https://boards.straightdope.com/t/any-microsoft-access-experts-around/603017/2 "2011-11-16T17:14:50Z")

</div>

Assuming that you have two combo boxes to select the group and the team: cmbGroup and cmbTeam.

Your select clause for cmbGroup should be:

Select distinct [ID] from [Group] Order By [ID]

Create the Click Event for cmbGroup:

Private Sub cmbGroup\_Click()  
Dim SQL As String  
SQL = “Select Team.Team from [Team] Where Team.Group = '” & cmbGroup.Value & “’ order by Team.Team”  
cmbGroup.RowSource = SQL  
cmbGroup.Requery  
End Sub

What this will do is load the cmbGroup object with all your group ids. As soon as the user clicks on a group, it will take that selected value and use it in a new query to load cmbTeam with only the teams matching the selected group.