JeanW,
Thanks for the reply. I am really relieved to get some help.
Now that I've thought through the whole system for the 500th time, I think I have a good idea for how to do data entry. Here it is... first some background...
The
database, as it is now, has a test "batch" of checks that I have successfully created. Go to layout "Owners" and view as form. That is what I want to create with every month's batch of data entry.
Now go to layout "Join." It doesn't look like much, but notice the number of records (799). I think that this is where I should begin with data entry. Please allow me to explain...
Table [Join] accounts for the relationships that every owner has with every well. More specifically, every owner has some portion of an interest in every well. Put another way, one well might be owned by 20 different owners, and one owner might own interests in 20 different wells. I picked the number 20 at random just for example sake.
Back to the data entry issue... Let's say that you go to a data input screen. On this screen you pick a month from a drop down value list for the new month for which you have to enter data. Let's say you pick "May."
When you pick "May," a script runs to create a copy of the values from "April" as the new values for "May," because all of the values are mostly the same except for [Production].[McfId].
- - - - - - - - - - - - - - - - - - - - - - - - - - -
Regarding the relationship graph...
I imagine that my relationship graph may be confusing for you or anyone else not so familiar with this database. Please allow me to explain, and maybe you'll agree that the way I have the relationship graph arranged now is not so confusing after all. On the other hand, if you cannot help me unless I do rearrange it I will.
Of the five most important tables, which are [Wells], [Production], [Join], [Checks], and [Owners], the two tables [Wells] and [Owners] seldom change, so I put them on the "outside," furthest on the left and right sides of the graph.
I put the remaining three "most important" tables [Production], [Join], and [Checks] in the middle of the relationship graph because they join tables [Wells] and [Owners].
Now let me speak to the little "ancillary" tables underneath the main tables.
Under table [Production], tables [Years], [GasPrices], and [TaxRates] have one to many relationships with their parent table [Production]. These three child tables of [Production] cannot be written to via their relationships with [Productioun].
Under table [Join], child tables [Mcfs] and [Interests], likewise, have one to many relationships with their parent table [Join] and cannot be written to via their relationships with [Join].
Finally under [Owners], tables [Cities], [States], and [Zips], have one to many relationships with their parent table [Owners] and cannot be written to via their relationships with [Owners].
Again, in general I have the tables arranged as I do to imply that the tables [Wells] and [Owners] are pretty much set, that is, when new data is entered on a monthly basis, the data in tables [Wells] and [Owners] does not normally change. On the occasion where, say, an owner's contact information changed, then the appropriate data would be changed in table [Owners] or in the child tables of table [Owners].
As for occasions where data relevant to a well would have to be changed, data in table [Wells] would be changed.
If the data normally does not change in tables [Wells], [Owners], and the child tables of [Owners], yet data has to be entered on a monthly basis, then where does the data get entered, or, more exactly, the data of which tables gets changed?
Thus, the real data entry problem. I will try to be as exact as possible. When monthly data is entered, here are the tables, and the fields within them, that will change:
[Production]
.[ProductionId] A new record gets created each month.
.[WellId] A specific well is associated with each record. Because a new record is created every month, each well will occur many times. Thus the one to many relationship.
.[ProductionMonth] Autocreated upon data entry
.[Year] A specific year for each record
.[Gross] Calculated field
.[Tax] Calculated field
.[Net] Calculated field
.[NetInterest] Calculated field
.[GasPriceId] A specific gas price for each record
.[TaxRateId] A specific tax rate for each record
.[InterestTotal] Calculated field
[Join]
.[ProductionId] Each record in table [Production] contains unique information. Most wells will produce a unique quantity of gas each month, but some wells will product the same quantity of gas as other wells. Thus, the one to many relationship.
.[CheckId] Each owner owns interest in many wells. The production information for the many wells is contained on one check stub. Thus the many to one relationship.
.[McfId] Each well produces a certain amount of gas (MCF), or thousands of cubic feet. Again, most wells will produce a unique quantity of gas each month, but some wells will product the same quantity of gas as other wells. Thus, the many to one relationship.
.[InterestId] Each owner owns a specific interest in each well, which is the same in some instances and unique in other instances.
[Checks]
.[CheckId] A unique number for each check. Each owner gets one monthly check.
.[OwnerId] Again, each owner get one monthly check.
.[CheckDate] Autogenerated upon date of data entry
.[Amount] Calculated field
.[Text] Calculated field
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Getting back to your post... Now that I've provided the logic behind my design, I'll address the desired process.
The end goal is to generate checks. If you downloaded my
database, go to layout "Owners" and view as form to see the 332 checks I have generated for each of the 332 owners.
So if generating checks is the ultimate process goal, then the intermediate goal is getting the right data in to the right fields without corrupting anything.
Let's take a step back...
It all starts with production. Every month, each well produces a certain quantity of gas, which is stored in [Production].[McfId].
Sometimes gas prices change. Sometimes, tax rates change, and the date is always new, but the only data that really changes every month is the MCF.
The data input screen should allow data entry in tables [Production], [Join], and [Checks].
The data input screen should allow data entry for table [Production] for the following fields:
[Production]
.[ProductionId] (autogenerate, hidden)
.[WellId] (portal)
.[ProductionMonth] (value list)
.[Year] (value list)
.[Gross] (cacluated, hidden)
.[Tax] (cacluated, hidden)
.[Net] (cacluated, hidden)
.[NetInterest] (cacluated, hidden)
.[GasPriceId] (value list)
.[TaxRateId] (value list)
Likewise, the data input screen should allow data entry for table [Join] for the following fields:
[Join]
.[ProductionId] (spawned from [Production].[ProductionId], hidden)
.CheckId (autogenerated, hidden)
.McfId (value list, very crucial)
.[InterestId] (extremely crucial, can make or break the whole system)
.[InterestType] (value list)
Last, the data input screen should allow data entry for table [Checks] for the following fields:
[Checks]
.[CheckId]
.[OwnerId]
.[CheckDate]
.[Amount]
.[Text]
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
As I started out at the first of the post, I think that the answer may best be found thus:
Have a data input screen with a month drop down control. If you pick, say, "May," a script runs to create a copy of the values from "April." A
new set of 799 records gets created in [Join], a
new set of 333 new records in [Checks], and a
new set of 57 new records in [Production].
Then the main job would be to go through and set the values for [Production].[McfId] correctly, which would cause new calculated values and new checks to get created too.
What do you think?