Add several new records with specific data with command button

[rocketD]

Hello,

I have a database of oil wells and steam wells. In my form, the user
selects from a drop down box a specific steam well he/she is
interested in. The user then sees a subform that shows, in datasheet
view, the most recent records for all the oil wells that are
associated with a particular steam well. There are often multiple oil
wells associated with steam wells, and when one oil well has its info
updated, all oil wells also associated with that steam well need to be
updated.

What I want to do is create a button on the form that adds a new
record to the table for each oil well shown in the datasheet view, AND
automatically populate the oil-well-ID field with those IDs. For
example, say steam well X is associated with three oil wells X-1, X-2,
X-3. What I want the user to do is click the button "enter new rates"
and have 3 new records appear in a table in the form, one each for
X-1, X-2, and X-3.

Can it be done? The only thing I've come close to finding like this
is to create a macro which adds new blank records, which won't work
because the oil to steam well ratio is dynamic.

Any ideas or directions to resources would be appreciated.
Thanks,
Dara

 

[John W. Vinson]

Hello,

I have a database of oil wells and steam wells. In my form, the user
selects from a drop down box a specific steam well he/she is
interested in. The user then sees a subform that shows, in datasheet
view, the most recent records for all the oil wells that are
associated with a particular steam well. There are often multiple oil
wells associated with steam wells, and when one oil well has its info
updated, all oil wells also associated with that steam well need to be
updated.

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.

What I want to do is create a button on the form that adds a new
record to the table for each oil well shown in the datasheet view, AND
automatically populate the oil-well-ID field with those IDs. For
example, say steam well X is associated with three oil wells X-1, X-2,
X-3. What I want the user to do is click the button "enter new rates"
and have 3 new records appear in a table in the form, one each for
X-1, X-2, and X-3.

Can it be done? The only thing I've come close to finding like this
is to create a macro which adds new blank records, which won't work
because the oil to steam well ratio is dynamic.

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?

 

[rocketD]

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

 

[Larry Linson]

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

 

[rocketD]

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






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- Hide quoted text -

- Show quoted text -

I see I'm just confusing everyone. I was trying to avoid our
terminology because I find it massively confusing, and I wasn't
allowed to have any input on the naming, but here we go.

lkpArea - table containing unique IDs and names of all physical areas
areaID = unique identifier for physical area
area = name of physical area

lkpZone - table containing unique IDs and names of all geologic zones
zoneID = unique identifier for zone name
zone = name of zone

lkpPattern - table containing unique IDs and names of all patterns
(aka oil wells)
patternID = unique identifier for oil well
pattern = name of oil well

lkpLocation - table containing possible location combinations for both
oil wells and injectors
locationID = unique identifier for the combination of location
variables
areaID = unique identifier for physical area
zoneID = unique identifier for geologic zone in which the target is
located; may cross areas and patterns
patternID = unique identifier for oil well; may cross zones

lkpTarget - table containing:
targetID = unique identifier for junction between injector and oil
well
wellName = standard name of injector
wellNum = standard number of injector
locationID = location well in relation to horizontal well and
physical location

tblRateUpdate - table containing records of dates and steam rate
information for each target
changeID = unique identifier for each record
targetID = identifier of the target for which the new steam rate is
being recorded
changeDate = date of the new record
targetRate = new steam rate
remarks = comments pertaining to the new steam rate

Linkage Structure:
- tblRateUpdate links to lkpTarget;
- lkpTarget links to lkpLocation;
- lkpLocation links to lkpPattern, lkpArea, lkpZone.

My form works as follows:
Step 1: Users choose, from a drop-down, an Area they are interested
in managing (think of it like a small county on a map).
Step 2: Users then decide if they want to view the targets in an area
by horizontal oil well (option A), or by vertical steam injector
(option B).
Option A - users choose a well from a drop down list which is
narrowed to show only wells within the Area chosen in Step 1. All
targets associated with the chosen well appear in a datasheet subform,
based on a query that links all of the above tables to the values
chosen on the form.
Option B - users choose an injector from a drop down list which
is narrowed to show only injectors within the Area chosen in Step 1.
All targets associated with the chosen injector appear in a datasheet
subform, based on a query that links all of the above tables to the
values chosen on the form.
Step 3: (This is still a GOAL) Users click a button (next to the
Option A or Option B subform, whichever they are using) that opens
another table or form showing one new blank record for each target
appearing in the subform, which the user can then fill out with the
new steam rates and dates.

I can't really break it down any further, so I'm afraid if this isn't
clear, I'll just have to deal. Thanks again for taking the time to
review my post.

Dara

 

[Larry Linson]

It's amazing how many times trying to simplify actually complicates and
confuses. If you just give the actual information, someone will likely tell
you if it is overwhelming, too complex, or obscure.

If you try to simplify, you may well get an answer that doesn't actually fit
your situation, as I am certain mine did not.

Larry Linson
Microsoft Office Access MVP

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






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- Hide quoted text -

- Show quoted text -

I see I'm just confusing everyone. I was trying to avoid our
terminology because I find it massively confusing, and I wasn't
allowed to have any input on the naming, but here we go.

lkpArea - table containing unique IDs and names of all physical areas
areaID = unique identifier for physical area
area = name of physical area

lkpZone - table containing unique IDs and names of all geologic zones
zoneID = unique identifier for zone name
zone = name of zone

lkpPattern - table containing unique IDs and names of all patterns
(aka oil wells)
patternID = unique identifier for oil well
pattern = name of oil well

lkpLocation - table containing possible location combinations for both
oil wells and injectors
locationID = unique identifier for the combination of location
variables
areaID = unique identifier for physical area
zoneID = unique identifier for geologic zone in which the target is
located; may cross areas and patterns
patternID = unique identifier for oil well; may cross zones

lkpTarget - table containing:
targetID = unique identifier for junction between injector and oil
well
wellName = standard name of injector
wellNum = standard number of injector
locationID = location well in relation to horizontal well and
physical location

tblRateUpdate - table containing records of dates and steam rate
information for each target
changeID = unique identifier for each record
targetID = identifier of the target for which the new steam rate is
being recorded
changeDate = date of the new record
targetRate = new steam rate
remarks = comments pertaining to the new steam rate

Linkage Structure:
- tblRateUpdate links to lkpTarget;
- lkpTarget links to lkpLocation;
- lkpLocation links to lkpPattern, lkpArea, lkpZone.

My form works as follows:
Step 1: Users choose, from a drop-down, an Area they are interested
in managing (think of it like a small county on a map).
Step 2: Users then decide if they want to view the targets in an area
by horizontal oil well (option A), or by vertical steam injector
(option B).
Option A - users choose a well from a drop down list which is
narrowed to show only wells within the Area chosen in Step 1. All
targets associated with the chosen well appear in a datasheet subform,
based on a query that links all of the above tables to the values
chosen on the form.
Option B - users choose an injector from a drop down list which
is narrowed to show only injectors within the Area chosen in Step 1.
All targets associated with the chosen injector appear in a datasheet
subform, based on a query that links all of the above tables to the
values chosen on the form.
Step 3: (This is still a GOAL) Users click a button (next to the
Option A or Option B subform, whichever they are using) that opens
another table or form showing one new blank record for each target
appearing in the subform, which the user can then fill out with the
new steam rates and dates.

I can't really break it down any further, so I'm afraid if this isn't
clear, I'll just have to deal. Thanks again for taking the time to
review my post.

Dara