Assigning a Value

[jk]

I have a DB which has deposits and withdrawls for customer accounts.Some of
these accounts are given a set percentage of 3 or 2 percentage added on to
their deposits.I would like to assign this percentage to their account number
so each time they make a deposit, it would be done automatically. Can this be
done?

 

[Ken Snell \(MVP\)]

Yes, and it would best be done via the form that you use for entering the
data for deposits. Add a field to your customers table (or create a separate
table if you might have more than one percentage value to apply to a
customer -- say, one percentage for deposits, one for withdrawals, etc.)).
Then, in your form, use programming in the form's BeforeUpdate event to
calculate the additional dollars and to modify the data before they're saved
to the table.

 

[jk]

The assignment of a percentage would only apply to a deposit not withdrawals.
It is the accounts that need to be tied to their respective discount since i
have about 7 accounts and some have three percent and some have two percent.
For instance account KB10013=DEPOSIT + 3%. How would i set that up in the
forms if i have seven accounts and they dont have the same discount?
Thanks

 

[Ken Snell \(MVP\)]

You haven't given us any details about your table setup, so I can only give
some generic advice.

Assuming you have a table for storing accounts, you'd add a field to that
table to hold the percentage value for that account.

Then, in the form's BeforeUpdate event, you can run a step of code to
calculate the new value (deposit * (1 + PercentageValue)) and store it in
the field that will hold that new value.

 

[jk]

Hello Ken,
I do have an account table as below:
AccountID--PK
AccountNumber
AccountName
AccountType
What additional field or data type do you suggest and how would it tie into
providing the discount for each different account?

 

[Ken Snell \(MVP\)]

Based on my understanding so far, you could add a field to this table. Name
the field AccountDiscount, and make it a Double Numeric data type. You then
can put a single discount percentage value into that field for each
AccountNumber record.

 

[jk]

I have not used a double numeric data type. Are you referring to selecting
the number data type and field size as double? How would the assignment of
the discount be done...through a macro? Thanks for the support!

 

[Ken Snell \(MVP\)]

I have not used a double numeric data type. Are you referring to selecting
the number data type and field size as double?
Yes.



How would the assignment of
the discount be done...through a macro?

This is completely dependent upon your datbase design. It can be done on
your form at the time of entry of a new record (via a macro or via a
calculation in a Control Source of an unbound textbox); it also depends upon
whether you need to store the resulting calculation(s) in a table, or
whether you will calculate the result via queries when you run reports, etc.
Overall, there are many ways to use the discount value; it depends upon your
needs, uses, purposes, etc.

Thanks for the support!