Relationships

[Bec@Cat]

I am trying to define a relationship. I want them to select from location and
depending upon what they choose would depend on which work area would show
up. How do I do this?

Right Now I have them set up as Value Lists

 

[Larry Linson]

In Access terms "relationships" define how two tables are "related" to one
another. What you describe seems more a user interaction issue, and I'll
proceed on that path.

Without knowing what data you have, and how you have laid it out in tables,
it's not possible to be specific.

However, in Form Design, one of the options for a Combo Box is to select
specific records to display on the Form on which you have placed the Combo
Box. If, for example, you have a table of Locations (which identify Work
Areas), you can populate the Row Source of the Combo Box with the Location
Name (for display to the user) and the Work Area identifier that is in the
Location record, then show on your Form all the records from the Form's
Record Source (another table or query) which match the Work Area identifier.

If that's not clear, try clarifying for us what data you have, how it is
laid out in tables, and what you mean by "which work area would show up",
and perhaps someone can provide a more specific, useful suggestion.

Larry Linson
Microsoft Office Access MVP

 

[Bec@Cat]

Hi larry:

Thank you for the advice. I am not sure what I need to do next. I have to
separate fields that I need to link somehow. I think you are right in that
it is an interaction issue. I have field 1 Location which consists of a
Combo Box with a Value list of Company 1, Company 2, Company 3. Then I have
a second field which is called the Work area and this also has a combo box
with a value list of Zone 1, Zone 2, Zone 3, etc. What I need to do is if
the user selects Company 1 then I only want Zones 2 or 3 to pop up. How do I
define that?

Bec

 

[Ken Sheridan]

Bec:

Your problem stems from the fact that you are using value lists to store
data. It’s a fundamental principle of the database relational model that
data is stored a s values at column positions in rows in tables and in no
other way. In fact it was Codd's Rule 1, 'The Information Rule', when he
first put forward the relational model for databases back in 1970. Value
lists should only be used for list which are fixed in the real world, such as
days of the week, months of the year etc.

What you seem to have here is a many-to-many relationship type between the
Companies and Zones entity types, so you need a table Companies, a table
Zones and a table CompanyZones to model the relationships between them. The
CompanyZones table will have two columns Company and Zone as foreign keys,
each referencing the primary key of the relevant 'referenced' table.
Many-to-many relationship types are modelled in this way, by being resolved
into tw on-to-many relationship types. The primary key of CompanyZones is a
composite one made up of the Company and Zone columns.

So, in the case of you example of Company 1, the CompanyZones table would
have rows:

Company1 Zone 2
Company1 Zone 3

Your companies combo box will have a RowSourceType of table/Query and a
RowSource of:

SELECT Company FROM Companies ORDER BY Company;

Your zones combo box will in its RowSource reference the companies combo box
to restrict the rows listed to those zones applicable to the company selected
in the first combo box, e.g.

SELECT Zone FROM CompanyZones WHERE Company = Form!cboCompany;

Note how the Form property can be used here rather than a full reference to
the form by name.

To get the zones combo box to show only the relevant zones requery it in the
company combo box's AfterUpdate event procedure, e.g.

Me.cboZone.Requery

Your may be wondering why you need a Zones table at all when the zones are
listed in ComanyZones. Without it the data would be at risk of
inconsistencies. By enforcing referential integrity in the relationships
between Companies and CompanyZones, and between Zones and CompanyZones only
valid company or zone names can be inserted into CompanyZones, thus
protecting the integrity of the data.

Ken Sheridan
Stafford, England

 

[Guest]

I am trying to define a relationship. I want them to select from location
and
depending upon what they choose would depend on which work area would show
up. How do I do this?

Right Now I have them set up as Value Lists