Design problem, two ways to enter data

[zfind]

Hi all,

In our database, we are tracking revenue figures. The required metrics, found in tblRecords, are [Revenue], [Gross revenue] and [Net revenue]. The only field that needs to be entered manually should be [Revenue]. The other two are then calculated from [Revenue] by multiplying by [Share % 1] and [Share % 2] respectively (they are 0.xx decimals). These shares are found in a seperate table, tblPartnersets, assigned to a unique ID that is then assigned to the record containing the revenue figures.

All of this is well and good but in rare cases we have situations where the data entry person would rather just enter the three revenue figures manually without any calculations being done. This has caused a problem for me in that I am not sure how I should structure my tables based on this. In 90% of cases the automatic calculation would be used. So:

1. How can I facilitate using both methods?
2. Should I be calculating [Gross revenue] and [Net revenue] on the fly through queries or should I run a one-off update query that will actually store the values into tblRecords?

Thanks in advance!

 

[Ron_dK]

Basically, you should never store calculated data.
So, I would recommend to set up a query where the user enters the Revenu data and make the [Gross revenue] and [Net revenue] calculated fields.

Your query might be something like this :

SELECT tblRecords.revenu, ([revenu]*0.01) AS grossrev, ([revenu]*0.02) AS netrev
FROM tblRecords;


Hth

 

[zfind]

The problem with that then is, what do we do when the users want to manually enter the Gross and Net revenue? Because there is no field for this to be stored in then it's impossible. Thanks for the input though, I see what you mean about never storing data.

I guess a better question would be, is it possible to have a field in a table that would be automatically calculated every time but if a user manually overwrote the number, we could exclude it from being calculated (and thus overwritten) again?

 

[stopher]

Is the relationship between the three fields always true even when entered manually?

If it is true then I would create an unbound form showing three entry fields. Then the user enters whatever they like.

Then, when they save, either:
They have only entered Revenue so you only need to save only revenue

Or:
you need to check that the relationship is correct and inform the user if not. Then you only store Revenue.


If Then calulation does not always hold then you must store all three value (you can still use the unbound method for different types of input). The calculation becomes just an aid for entry, not a relationship

You will need to write some VBA if you are going to use the unbound form method. Shout if you need help on this as there are plenty of experts here.

hth
Chris

 

[zfind]

Hi Chris, thanks for the helpful reply.

The mathematical relationship between the three is always true, whether Access does the calc or whether the user has already done it on paper and just enters the amount.

I'm not 100% following your example above. The form is a main form and a datasheet subform and it needs to be compact, so I can't really add any more fields. Currently the user sees:

Partner : Country : Week : Revenue : Gross Revenue : Net revenue : others

So are you saying then, that I should write some code that will do the following:

• When "save" or "close form" (or even changing to the next record) is clicked, look at the Gross and Net revenue fields.
• If they are blank, use the shares in the partnerset that is linked to this record to calculate the shares automatically.
• If they are filled, then continue
• Save the values that are now in the field.

Sorry if that's a bit off. It sounds like the above would work. At the moment though, the data is presented in a subform that sits on a form where filtering of the subform is done, and data entry can be done directly into the filtered subform. Is there an event, like moving to the next record, where could perform the above?
edit: OnCurrent looks to be the place to do the macro, but how...

 

[The_Doc_Man]

Where I think we are all confused is why ANYONE in their right mind would want to calculate something for which a fixed numerical relationship exists.

If this data entry person works for you, then TELL him/her to enter the single data field and MOVE ON. Those extra keystrokes are a waste of time. I can't TELL you how many wasted therbligs that represents. (OK, for units of therbligs, look up time and motion studies to find the name Gilbreth, who made his fame by doing work efficiency studies for assembly lines. No, I'm not kidding.)

Does the data entry person think the computer will make a mistake before s/he does? What do you do if the computation says X and the data entry person says Y? If there is a forced mathematical relationship, Y is never correct. If that is true, then it is time to get a new data entry person who isn't quite so arrogant.

If it is NOT true, you are giving us at least a partial impression that doesn't match reality AND it is material to the discussion about whether to compute or save the values. If there is ever a time when X is NOT correct but Y is correct, then you must always save the result. No choice, because in that case the two other fields are not dependent on the [revenue] field. This is therefore actually a NORMALIZATION issue... you NEVER store computable dependent fields if you can help it (and usually you can). You ALWAYS store non-computable independent fields, even if they are SOMETIMES computable and dependent. You don't need anything else on the visible part of the form, but you might want to create an invisible control that indicates whether the data entry person entered an override of the normally computed values. You would do that to prevent the computer from readjusting the dependent fields the next time you opened that form to do data maintenance. To do that, put an event on the two fields you would normally just compute. In a LostFocus event, compute the expected value and compare to the value in the control. By default, your hidden flag says "Use Computed Values" - but - if the data entry person enters a different value, set the flag to say "Do not recompute" - and in that case, you will have a way to remember that an override was used.

Having said all of that, let's get back to the where-to-compute case.

Event "OnCurrent" won't do a thing for you. OnCurrent isn't triggered until you are already at the next record. If new data value has been entered, you have a couple of possibilities. To my view, the best one is the form's "BeforeUpdate" event, since you WILL do an update of the form's underlying record if even one bound field was updated. I'd vote for that one as the place to put any code.

The other place to do it is in the independent field's AfterUpdate event, in which you compute a value for the two other text boxes. If it is true about being able to override with a value different than the computed value, you MUSt use something like this instead of the Form_BeforeUpdate event.

 

[zfind]

Where I think we are all confused is why ANYONE in their right mind would want to calculate something for which a fixed numerical relationship exists.

Because this project covers around 800 different contracts around the world, there are sometimes cases where the percentages used to calculate the revenues need to be different than the default set in the partners table. It's for business reasons, not because I want to do it that way.

If this data entry person works for you, then TELL him/her to enter the single data field and MOVE ON.

I work for them, basically. I need to provide a tool that allows them to enter data how they want to enter it and unfortunately I don't dictate how that should be done. Some want option A, some want option B.

The other place to do it is in the independent field's AfterUpdate event, in which you compute a value for the two other text boxes. If it is true about being able to override with a value different than the computed value, you MUSt use something like this instead of the Form_BeforeUpdate event.

I think this sounds like the best option. That way, I can easily write code that will fill the two other revenue fields when the first is updated. This would allow the other two to be changed manually after that without affecting anything.

The subform where the data entry is done is a datasheet, is it just a matter of going to design view and writing the code in the AfterUpdate event of the first box and then filling the other two from there? I'll need to make the procedure look to a related table to get the percentages (tblPartnersets.Revenue share) based on that record's PartnerID - how can I do this?

Thanks for the reply.

 

[zfind]

Ended up using DLOOKUP to grab the shares based on the partnerID. These were then used to calculate the revenues on the AfterUpdate event of the original revenue field, with a condition to check if there was already data in the secondary fields.

Percentages are also looked up in the Current event of the records subform and sent to text boxes for information.