Because they are both "wells" does not mean that these are similar entities,
so there is little probability that they should be in the same table.
It would appear that you need at least two, and likely three or more,
tables. Rather than confuse the issue, call the steam wells "injectors",
call the oil wells "wells", and call the points where they meet "targets".
These will be linked/joined via additional tables called "intersection
tables" (they are also called "junction tables" but that, too, could confuse
the issue, so I won't use that terminology.
tblInjectors -- data table about the injectors (or steam wells)
InjectorID
... other Injector Information
tblLocations -- data table about the points where injectors meet wells
TargetID
... other Target Information
tblWells -- data table about oil wells
WellID
... other Well Information
tblInjectorsLocations -- intersection table relating Injectors and Targets
InjectorID -- foreign key pointing to a record in tblInjectors
TargetID -- foreign key pointing to a record in tblTargets
tblWellsLocations -- intersection table relating Wells and Targets
WellID -- foreign key pointing to a record in tblWells
TargetID -- foreign key pointing to a record in tblTargets
using queries with the data tables and one or more intersection tables, you
can look at all the items in one table that relate to the item in another...
a good visual approach to this is a Form in which you select the one item,
and a Form embedded in a Subform Control showing the multiple items related
to the one currently selected in the main Form.
From your description, I can't tell how you are using "rates", so I don't
know whether that is an attribute in another table or a separate table; nor
did I see whether a well may have multiple locations served by different
injectors (in which case, I don't know how you would determine a steam rate
for the well without a calculation).
If you have additional questions, post back and perhaps someone will have
other suggestions.
Larry Linson
Microsoft Office Access MVP
WHOA.
That *REALLY* sounds like you have a normalization problem. If you're
storing
the same information in two different tables, You Have A Problem.
It can be done (using an append query). I'm almost certain that in a
properly
normalized database it would not *need* to be done. How are your tables
related? Can you not use a Query to look up the rate (from the steam well
table) given a oilwell ID?
Perhaps you are right. Explaining how the tables are currently
"normalized" might take a while, though. To try to condense it - there
are steam wells, called injectors, each of which can support multiple
oil wells. They are vertical in the earth. The oil wells run
horizontally in the earth, and so one oil well is often supported by
multiple injectors. Each oil well and each injector have unique
identifiers, and the junctions where they meet (where steam reaches a
specific part of the oil well) are called targets - targets also have
unique identifiers. Based on the way the company keeps and has always
kept records, the combination of injector ID, oil well ID, and
physical location (defined by a location ID) of the junction is what
makes up the unique target ID. Finally, each time the rate of steam
allocation is changed for a particular target, a new record is
generated, containing date, new rate, and targetID, all uniquely
identified by the changeID. If you look at the changeID table, it's
meaningless; you have to use a query to show the info that the
engineers understand, because it's all based on unique IDs.
Now. Engineers have to manage their wells by groupings, and they
sometimes want to manage by an entire oil well, or by an entire
injector. If the engineer wants to look at their steam allocation by
injector, they have a portion of the form where they can do that;
choosing an injector will show steam rates for all oil wells
associated with that injector (each record is for a target). Vice
versa for the oil wells - choosing an oil well on a second portion of
the form shows all injectors associated with that oil well (again,
each record is a target).
Changing the rate for one target nearly always affects others, so
those others must be updated at the same time. (For example, X amount
of steam from an injector is divided over 3 wells, so changing the
amount going to one well changes the amount going to the other two.)
In the past, when engineers add one record for amount of steam
injection (for a unique target) they have frequently forgotten to
adjust the other related wells at the same time, causing users to
assume LOCF, which is rarely the case. This is why we want to have a
button that creates new records for all of the targets shown in the
subform the engineer is using, rather than having him/her enter just
one at a time. The subforms show the same info in different ways
depending on the needs of the engineers, but when they choose to
update their steam rates in either manner, the records will all be
going into the table as unique records for each target (again,
uniquely identified by the changeID).
Does that clear things up?
Thanks,
Dara