Org ComboBox Suggestion

[Brennan]

I know there is a simple answer here, but it escapes me. I have an org
structure that I would like to recreate in a combo box. For example, I have
one sales leader, two sales vp, and 2 sales reps under each sales vp. The
sample structure is below:

Sales Leader
Sales VP - Region 1
Sales Rep 1
Sales Rep 2
Sales VP - Region 2
Sales Rep 1
Sales Rep 2

I would like a combo box with these values that will become the criteria for
a query tied to the combobox. When I select the sales leader, I would like
for the query to display the results for the whole group. When I select the
Sales VP - region 1, I would the query to display the results for that region
and if I selected the results for the sales rep 1, the results for that
particular sales rep.

Can someone please let me know how I can accomplish this. Thanks so much!!!

Brennan

 

[Allen Browne]

Hi Brennan. The crucial thing here will be setting up a good relational
structure.

At the most basic level, you might have these tables:
a) Staff table, with StaffID primary key.
One record for each person.

b) Region table, with RegionID primary key.
One record for each region.

c) Role table, with RoleID primary key.
One record for each role, e.g. 'Sales Leader', 'VP', 'Rep'

d) Team table
RegionID relates to Region.RegionID
StaffID relates to Staff.StaffID
RoleID relates to Role.RoleID

This may not end up being the best solution, but it does allow you to assign
people to a specific role in a region. Example records:
In Region 1, Jo Smith is a 'Sales Leader'
In Region 1, Alf Banyon is a 'Rep'

You can now very easily load the combo with the people for one region.

One of the basic principles of normalization is never to use repeating
fields (such as Region1, Region2, or Rep1, Rep2.) Instead, use a related
table (like the Team table above), where one region can have multile staff
and so on.

The above is really easy to interface too. You could create a staff form,
with a subform bound to the Team table, so it shows the teams the person is
on. You could also create a Region table, with a subform bound to the Team
table, so it shows the people in that region. The subforms can use combos
(e.g. for selecting the Role.)

 

[Brennan]

Hi Allen,

You have been very helpful to me over the past few days. I appreciate it
very much. Thank you. With regard to your advice, I don't think that I
explained myself well. Ideally, what I am looking for is the ability to
build one combobox that I can use to filter the results by total, region, and
individual sales rep. So the org combo would look something like this:

Sales Leader
Sales VP - Region 1
Sales Rep 1 - Region 1
Sales Rep 2 - Region 1
Sales VP - Region 2
Sales Rep 1 - Region 2
Sales Rep 2 - Region 2

So when I select the sales leader, it would filter the results of the query
to give the total results. When I select Sales VP - Region 2 it would only
show the total results for those two sales reps in that region. When I
select Sales Rep 1 - Region 1, it would only show the results for that rep.
Can you help me understand how to construct this type of combobox and have it
tie to a DSUM function or query? Thanks again for your help.

Brennan

 

[Allen Browne]

Given the data structure you have, I'm not sure what to suggest.

If every record has a Sales VP - Region 1 person, it seems you could create
a Totals query to GROUP BY that field. But I'm not sure that's what you
want. I think it will be crucially important to get a true relational data
structure here if you need to get queryable results. In the example I
suggested, there ends up being only *one* Amount field you need to sum - not
spread across multiple fields that might need to morph into more fields in
the future when you need more regions or more reps per region.