show on form? does this sound right?

[loco_ola]

Hi,

I've begun building a database which contains peoples information, pretty
standard really. I know how i would do this with asp if i was building it
online, however for once im able to just use access as an environment which
should make things quicker (i hope).


Each person can has some pretty standard details, name, address, etc.

Each person can be assigned a role category: Group, Brand, Unit or
Independent

Each Unit has a Brand, and each brand has a group.
Eg: M & B group contains the brand "All Bar One" which contains the Unit
"Notting Hill".

So if a person is assigned a role category of Unit they needs to have a Group
and a brand also assigned, however if the person is Independent they will not
have a group or brand or unit. If the person is assigened a role category of
group it means they work for the group head office rather than at a brand or
unit, so they obviously dont need one of those assigned.

Currently I have in the way of tables.

tblGroups (group_id, group_name)
tblBrands (brand_id, group_id, brand_name)
tblUnits (unit_id, brand_id, unit_name)
tblRoleCats (rolecat_id, rolecat_name) eg groups brands units etc
tblContacts (contact info + roleCat_id, group_id, brand_id, unit_id)

I have built the tripple linked combo box for choosing unit or brands etc.
however i was wondering the best way to lay out the form, there will be bits
which can be hidden (group, brand and unit combo box) if the rolecat is set
to independent, however differetn bits will need completeing if set to unit,
brand or group. What would be the best way to do this? With a sub form? or
header/footer or what?

Many thanks for looking.

 

[Allen Browne]

Interesting one.

You have a tiered structure, where a Group contains Brands, and a Brand
contains Units. I assume that if a person is assigned to a Group, then they
are connected with all Brands in that Group, and so with every Unit in each
Brand of the Group as well.

If that is correct, we have a Bill-of-Materials type structure where
entities are nested within other entities. The simplest way to design this
might be to use a single table to hold all the possible entities. Fields:
- EntityID P.K. A unique name for this entity, e.g. "M & B Group", or
"All Bar One"
- EntityTypeID "Group", "Brand", or "Unit"

You with then have another table that defines how these entities relate to
each other. Fields:
- ParentEntityID relates to EntityID in the first table.
- ChildEntityID relates to EntityID also.
So if the "M & B Group" contains 3 brands, there would be 3 records with "M
& B Group" as the ParentEntityID.

In terms of the interface, this means there is only one combo for the user
to select from. Based on that entry, you can then create a query that gives
you all the Brands and Units the person relates to if a Group was chosen,
and so on.

For further discussion, including a paragraph on nesting and some fruther
references on Bill of Material structures, see:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

Hope that at least sparks some useful thinking.