Many-to-many.....I think. And (I'm assuming) some VBA.

[mkenal]

Hi everyone. Just want to preface this by saying that I've sort of been thrown into learning Access on the fly for a project at work, and I kind of have the hang of the basics but not completely.

Here's what I'm trying to do. We have a set of locations that we need to track; each location falls into a Zone, and each Zone falls into an Area. There are 4 Areas and 17 Zones in total. Each location record needs to contain references to the Zone and Area in which it falls.

The main goal of my current frustration and confusion is that, for data entry integrity purposes, I want the form we will be using to enter the location data to only allow the user to select a Zone that falls in the selected Area. For example, if the user selects Area 2, they will only be able to choose from Zones 2, 6, or 10 (our numbering system is just like our city's addresses for consistency - even on the east side of the street/city, odd on the west).

I've tried a few suggestions that I've found in various places online, including creating a lookup table and using after-update event procedures and the like... but I can't seem to make it work; I continually end up with a blank combo box for either Area or Zone, and get an error message when trying to enter a test record. Also, changes I make to the field in table design view are not being inherited by the form fields, even though they are set to inherit and even if I close and re-open the form or the entire database.

My tables/fields are as follows:

group_info (location data table)
- area
- zone
zones (zone/area data table)
- primary key
- zone
- area

Here's the way the location relationship with Zones and Areas works in real life:

Area
^.........^


Zone.....^
^.........^
Location

While I realize it would probably be much simpler to just have the user select the Zone and have the Area field auto-populate with the correct Area, we would rather do it this way to minimize mistakes. With 4 Areas, there is a 75% chance of selecting the wrong one, but our practical experience shows this to be very unlikely as the Areas are large geographically, and we can quickly identify the correct one when looking at the location on a map. By contrast, with 17 Zones there is a 94% chance of selecting the wrong one, and our experience shows that we cannot as easily pinpoint the correct Zone on a map due to the much smaller geographical size than the Areas.

I hope I haven't confused anyone, and I hope I've given enough information. Any help that anyone can give would be greatly appreciated (remember, I'm new to all of this!!)

[pdebaets]

First, may I suggest that you name your tables Areas, Zones and Locations? Hope that isn't too confusing. :-)

Here's a suggested table schema:

Table: Areas
PK: AreaID (autonumber)
AreaName

Table: Zones
PK: ZoneID (autonumber)
FK: AreaID
ZoneName

Table: Locations
PK: LocationID (autonumber)
FK: ZoneID
LocationName

You will need to get the schema straightened out before working with the combo boxes and forms.

[mkenal]

Okay, here is what I've got now:

group_info:
PK
[multiple fields not pertinent to this issue]
area
zone

zones:
PK
zone
area

areas:
PK
area

The zones(PK) has a one-to-many relationship with the group_info(zone); the areas(PK) has one-to-many relationships with both zones(area) and group_info(area).

I'm trying to create the query necessary to make what I want to happen work, but none of the tutorials or books I've had access to so far are helping me. They are either too simplistic - designed for running reports but not actively controlling/limiting data selection - or they are so complicated as to make me think I'm reading Perl!! What I want my query to do is find all the Zones whose Area matches the Area listed in the selected record in group_info, and show me those Zones to choose from. The Zone chosen from those given options will be the FK stored in that record on the group_info table.

And of course, once I get the query done I then have to figure out the VBA to make the Zone combo box use it to determine which values to show. Another part I'm having difficulty with (can't find a decent tutorial and it's not covered in the books I have), but one thing at a time and first things first.

Thanks again in advance!

[pdebaets]

Try not to use a multi-field primary key. That can make things really messy. I suggest (strongly suggest) using a long integer autonumber field for each of your table primary keys.

Also, normalization rules would require elimination of duplicate fields on the different tables. You've got "area" on all three tables.

Please have a look at my example schema above. From what you've described, this should be a good starting point for you.

[mkenal]

I don't have any multi-field PKs - those things confuse the heck outta me and I can't see why I would ever want to use one

I think I may be confused then by your example; if I thought I could have two simultaneous relationships between the zones and group_info tables (area-to-area and zone-to-zone) then I would do that, but I can't see how else to get the "relationship triangle" that truly exists. Many locations are in one zone, and many zones are in one area - but also many locations are in one area. Since I need to store Area data in the group_info table as well as Zone, it doesn't make sense to me to not include it. The stored data for that field on that table could be the FK from the areas table if need be, but without the zones table also having an area field, I can't see how the system would know how to find and display the correct Zones for that Area. I could do it as a mutli-value field called zone on the areas table instead, but that's more complicated and still amounts to three tables with duplicate fields.