Need help with formula and data records

[ChingghisKhan]

Hi,

Can someone please help me with the following two issues:

1.) I need the formula to calculate 15% in a new field from an an existing field.
Something like: =([15%] of [price])

2.) How can I get some fields to show automatically on the next record?

Thanks for all your help!

Robert

 

[Vassago]

Hi,

Can someone please help me with the following two issues:

1.) I need the formula to calculate 15% in a new field from an an existing field.
Something like: =([15%] of [price])

2.) How can I get some fields to show automatically on the next record?

Thanks for all your help!

Robert

Multiplication?

=[price]*.15

I don't know what you mean on the 2nd part. Can you elaborate?

 

[ChingghisKhan]

Dear Vassago,

Thank you very much for your lightening fast help

In record #1, I have a credit balance.
Upon creating a new record, I would like this value to be shown automatically.
In other words, I need the field auto populated from the previous record when creating a new record.

I hope you can understand what I try to do.

I can't post images because I am new here, got to have 10 posts

 

[bob fitz]

Perhaps the following link will help: http://allenbrowne.com/ser-24.html

 

[ChingghisKhan]

Hi Bob & Vassago,

Bob's link explained how to carry ALL fields over with the possibility of excluding some.
Since I have about 100 fields and only need to carry over 4 fields, I thin I need another approach.

I tried to insert screenshot, no go.

The following fields should show up in the new record:
OLD SIMPSON Credit Balance:
NEW SIMPSON Credit Balance:
Add CREDIT to SIMPSON:
Credit BALANCE after adding:


Once I create a new Record: "OLD SIMPSON Credit Balance" should be auto populated from the previous record"s field: "NEW SIMPSON Credit Balance" since the cost of the purchase would have been deducted already.
"Add CREDIT to SIMPSON" is done by manual entry when I send them money.


I hope I could clarify the issue.
Thanks

 

[ChingghisKhan]

Well, I have to get to 10 posts before images can be added.

 

[ChingghisKhan]

Here we go:

SIMPSON Commission: SIMPSON Commission:

Exp/Trans Cost to SIMPSON: =(+[Export Cost:]+[Transport Cost:])

Total with Exp/Trans/Comm: =(+[Price we paid:]+[Exp/Trans Cost to SIMPSON:]+[SIMPSON Commission:])

OLD SIMPSON Credit Balance: OLD SIMPSON Credit Balance:

NEW SIMPSON Credit Balance: =([OLD SIMPSON Credit Balance:]-[Price we paid:]-[Exp/Trans Cost to SIMPSON:]-[SIMPSON Commission:])

Add CREDIT to SIMPSON: Add CREDIT to SIMPSON:

Credit BALANCE after adding: =([NEW SIMPSON Credit Balance:]+[Add Credit to SIMPSON:])

 

[ChingghisKhan]

Only the following 4 fields should auto populate into the next record:

OLD SIMPSON Credit Balance: OLD SIMPSON Credit Balance:

NEW SIMPSON Credit Balance: =([OLD SIMPSON Credit Balance:]-[Price we paid:]-[Exp/Trans Cost to SIMPSON:]-[SIMPSON Commission:])

Add CREDIT to SIMPSON: Add CREDIT to SIMPSON:

Credit BALANCE after adding: =([NEW SIMPSON Credit Balance:]+[Add Credit to SIMPSON:])

 

[ChingghisKhan]

As a general rule, we should NOT store a calculated value. Lets take a look at this. Lets say we add a transaction today. The closing balance on the previous record was $123. The current transaction was $23, leaving a balance of $100. Now we carry this forward and at the end of the week and several transactions we now have a balance of $232. Then we realize we made a mistake and today's transaction was $22, not $23. Now we have to fix that record and all the subsequent records.

That's why we don't store a calculation. Balance is a calculation arrived at by adding all credits and subtracting the debits. So if a value changes, we just need to change that record and everything automatically recalculates.

So can anyone please help me to get this done accordingly?

Thanks

Robert

 

[ChingghisKhan]

Ok, I only need three fields I just realized.
"Add CREDIT to SIMPSON:" is not needed

 

[ChingghisKhan]

I am way over my head here - Newbie

Can anyone please put the exact expression together for me please?

 

[ChingghisKhan]

These are the three fields in question:

OLD SIMPSON Credit Balance: =DSum("credit - debit", "transactionstable", "customer = '" & customer & " And transactiondate < " & Format(transaction date, "\#yyyy-m-d\#")

NEW SIMPSON Credit Balance: =([OLD SIMPSON Credit Balance:]-[Price we paid:]-[Exp/Trans Cost to SIMPSON:]-[SIMPSON Commission:])


Credit BALANCE after adding: =([NEW SIMPSON Credit Balance:]+[Add Credit to SIMPSON:])

 

[Vassago]

Don't store the calculated fields, just use queries to display the calculations in your form, but keep a table of transactions handy to calculate those balances. You'll find trouble keeping those calculated fields in the form. What if you miscalculate on a previous record? Then all subsequent records will have an invalid balance. By separating out transactions and calculated fields to display on form, you can avoid this.