linking normalized tables in forms

[rocketD]

Hello,

I'm working on a database tracking subparts of oil wells in
production. The subparts, which we call targets, are identified by a
combination of fields including an overall well name and a set of
physical location identifiers. The physical location is denoted by
area + pattern + zone. Zones belong to patterns, which belong to
areas, which belong to systems, but zones have a many-many
relationship with both area and pattern, so all three are required to
make any sense. I have the tables normalized as follows:

lkpWellname: wellID, wellname
lkpSystem: systemID, system
lkpArea: areaID, area
lkpPattern: patternID, pattern
lkpZone: zoneID, zone
lkpLocation: locationID, systemID, areaID, patternID, zoneID (note
that this is a bridge table)
tblTargets: targetID, wellID, locationID

Now. I'm trying to make a form where targets can be viewed and new
targets can be added, so I want to be able to pull up wellname,
system, area, pattern, and zone, because the people looking at this
aren't going to understand the Access ID numbers. I want to show the
the physical location fields (system through zone) based on the
locationID that's recorded for target in the record displayed by the
form, but I don't know how to do that. I tried defining a query for
each physical location field in the properties, but it requires the
user to select from a limited list rather than autopopulating. At the
same time, I need that limited list in the event that a new target is
being added.

For example, say I'm looking at target 37ZBH, system 1, area Z,
pattern ZH, zone Green. The record in the tblTargets has values
targetID 12, wellID 3, locationID 2, but what I want to see on the
form are the 37ZBH etc. values.

Is this possible? Am I explaining it well enough?

Thanks,
Dara

 

[Albert D. Kallal]

How these things tend to work is you simply start at the top most table in
the relationship.

So, the person might search for a particular well, and then you simply
"display" the list of wells.

Some ideas on searching, and presenting a hit list to view is here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

In most cases in ms-access, to display related information, you use a
sub-form.

And, you are 100% right on about users never seeing or needing to know the
"id" of records...that something for your internal use...not for users to
see.

I would not worry about the "many to many" relationship here. In fact, if
you look close at your relationship tables, you see that hits table belongs
to some other table. in concept, you can call something a bridge, or
junction table, but in real use it simply a child table of something else.

I can have a database of my friends, and each friend can have "many"
favorite colors. I could argue that we need a bridge table to allow each
person to have more then one color, but it best to simply state that I need
a table called tblListOfTheirFavorateColors

so we get:
tblfriends - list of my friends
----> tblListOfTheirFavorateColors
----->tblColors - this is the list of colors they can select from

So, it is a bridge table, but a MUCH better term is that I simply have a
table that lists each persons favorite color, and it is related to the
tblFriends table. It is **so** much simpler to define the problem in these
types of terms as opposed to saying we need a bridge table (what we need is
a simple table that lists the persons favorite color!).

Anyway, take a look at the above link, and take a look at the following
screen shots:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

If you look at the VERY last screen shot, this is a classic "distribution"
of amounts (donations) to several accounts. that means for each weekend (we
have lots of those), you have many people, and they give donations, and each
donation needs to be distributed to many accounts. (that form does down 3
levels deep.

Event/weekend--->many persons-->donation->many accounts

So, define your problem from the "top", and work your way down. In that last
screen shot, as you move through the "many" persons on the left side sub
form, the "many" accounts their donation is distributed to is shown on the
right side sub-form.

So, you can as rule use sub-forms to display the additional information.

It just a question of do you launch a new form to show the additional
details, and then at what point do you not go any deeper down though the
tables.

So, figure out what going to be your "top most" display..and work from that.
In most cases, you not build queries that joins the tables.

However, in my above example, I might want to display the "color", and not
the color id. In that cases, if you have 2, 3, 4 or even more fields that
are the "id" of some related table, but want to display the "description
text" of that id from that related table then do the following:

Simply use the query builder and drop in your main table. You then drop each
of the additional tables which have those text descriptions. You might only
have to drop in one extra table, or perhaps 8 extra tables. Just draw the
join lines from that "main" table to those child tables with the text
descriptions. Make sure you double click on the join line and make it a left
join -- you MUST do this when you have more then one table. Now, simply drop
in the fields from the main table into the query grid + all of the fields
from those other tables with the text descriptions you need.

So this whole process is a simple drag + drop operation. You can now use
this query for reports, or even search forms and you thus can display the
"description" field from the related tables and not use have display/show
the "id" field. And, this means you can also search by the description field
in that form.

 

[rocketD]

Albert,

Thank you so much for your detailed answer. You've made it very clear
and given me some great resources to draw upon now and in the future.
I really appreciate your taking the time for someone you don't even
know.

Best,
Dara