Updating table field from expression (1 Viewer)

[karmaimages]

Hi,

I've created a database to keep track of repayments from customers, which I've attached to the post for easier viewing. I'm not sure if I can post images yet which isn't helpful trying to explain this.

From the form I have a running total of what is left to pay using the stating amount - amount paid - previous final figure which works great

What I'd like to do is update the remaining balance figure at the top of the form from the running total but keep that value stored in the database table for reporting purposes and to be able to export it at a later date.

Any idea how I could go about this? Is it easy? I'm quite the beginner at Access but trying to learn as i go along.

Many thanks in advance

 

[MarkK]

I would avoid storing a balance, since a balance is date sensitive. I would typically write a function that receives a date, and calculates the balance from the raw data up to the given date.

This is the same with someone's age. We store the date of birth, but the age (like a balance) is always subject to change over time. Some data we always store, some data we always calculate.

 

[karmaimages]

I would avoid storing a balance, since a balance is date sensitive. I would typically write a function that receives a date, and calculates the balance from the raw data up to the given date.

This is the same with someone's age. We store the date of birth, but the age (like a balance) is always subject to change over time. Some data we always store, some data we always calculate.

Thanks for the reply, unfortunately I need the balance stored to run some reports from the data.

It's currently held on paper file manually calculated and updated - so really need the running total to update the remaining balance in the table

 

[pbaldy]

+1 if you're looking for votes; generally we store transactions, calculate balances from them. Further, your PA1... and PD1... fields look like a normalization problem:

http://www.r937.com/Relational.html

Not sure what they are to contain, but likely you'd have a related table with customer number, date and amount of payment. If I make 5 payments I have 5 records in that table.

 

[karmaimages]

Also have a query on the total remaining running total, why it's displaying #Type! ?

 

[plog]

You need to normalize your tables (https://en.wikipedia.org/wiki/Database_normalization). All that data that is to go in those PD and PA fields needs to be in their own table(s). Tables should grow vertically (with more rows) and not horizontally (with more columns).

My guess is they are payments--PA=amount and PD=date. If that is correct you need a Payments table structured like so:

Payments
payment_ID, autonumber, primary key
ID_PCData, number, foreign key to P_C_Data.ID
Payment_Number, number, will hold suffix of current PA fields names (i.e. PA1->1, PA2->2)
Payment_Amount, currency, will hold the value of PA fields
Payment_Date, date, will hold value of PD fields

That's it. That table will now accomodate those 96 fields (48 PA & 48 PD). It accomodates them with 48 rows, not 96 columns.

Once you have your data like that, finding a balance is simple--you run a simple totals query (https://support.office.com/en-us/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a). For now though, you need to read up on normalization and structure your tables properly.

 

[karmaimages]

You need to normalize your tables (https://en.wikipedia.org/wiki/Database_normalization). All that data that is to go in those PD and PA fields needs to be in their own table(s). Tables should grow vertically (with more rows) and not horizontally (with more columns).

My guess is they are payments--PA=amount and PD=date. If that is correct you need a Payments table structured like so:

Payments
payment_ID, autonumber, primary key
ID_PCData, number, foreign key to P_C_Data.ID
Payment_Number, number, will hold suffix of current PA fields names (i.e. PA1->1, PA2->2)
Payment_Amount, currency, will hold the value of PA fields
Payment_Date, date, will hold value of PD fields

That's it. That table will now accomodate those 96 fields (48 PA & 48 PD). It accomodates them with 48 rows, not 96 columns.

Once you have your data like that, finding a balance is simple--you run a simple totals query (https://support.office.com/en-us/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a). For now though, you need to read up on normalization and structure your tables properly.

While that sounds great, unfortunately I know very basic access and need to get this up and running ASAP so don't have the time to learn about "normalization " or anything complicated like that.

Any suggestions on how I can do this, as is will be great

 

[MarkK]

I solve people's data problems for money, but I post here because I'm enthusiastic about helping people who are enthusiastic about learning. If you "need to get this up and running ASAP [and you] don't have the time to learn about 'normalization,' or anything complicated like that," then with all due respect, I recommend that you hire someone like me.

 

[plog]

I'm at a loss for words. Actually I'm not, its just none of them would be constructive, so let me just say:

Hire someone.

 

[karmaimages]

I solve people's data problems for money, but I post here because I'm enthusiastic about helping people who are enthusiastic about learning. If you "need to get this up and running ASAP [and you] don't have the time to learn about 'normalization,' or anything complicated like that," then with all due respect, I recommend that you hire someone like me.

will try and PM you if I can markk

 

[sneuberg]

While that sounds great, unfortunately I know very basic access and need to get this up and running ASAP so don't have the time to learn about "normalization " or anything complicated like that.

Normalization Lesson 1: Have the payments dates and payment amounts repeating in the same table as the customer is a violation of first normal form. That's all you need to know for this project. Now that didn't take long, did it?

If you really want a system that works you are going to have to put this in normal form. Your existing design has problems that will just get worse as time goes on. For example

• If you forgot to enter the first payment after you entered five others. As it is you have no way to insert a payment like that baring reentering everything.
  
• This remaining balance calculation would require summing over a indeterminate number of columns. No sane man would want to write the code to do that,
  
• You shouldn't be limiting to adding 48 payments even if that is a lot.

The attached database contains a modified version of your database that's in normal form. It's not complete and I offer it only as a mechanism for discussion.

Please look first at structure of the tables PCData and Payments in the attached database. You will see that PCDate is just your P_C_Data table without the payment fields. These have been moved to the Payments table. In the Payments table along with the Payment Date and Amount you see the Customer Number. This is whats called a foreign key is allowed to (needs to) repeat in a relational database system. If you look at the data in the payments table you will see the Customer Number 111 repeat for each payment.

Now please go to DATABASE TOOLS, Relationships. Here you will see a graphic representation of the relation (one-to-many). For any customer there can be many payments. By storing the payment information in a separate table there is no limit to the number of payments and you don't have to create a table with these repeating fields. The system will glue these table together wherever they need to be combined. I hope about now you are think,"Wow, this is way cool".

Now open the New Payment Card form. The top part of this form is virtually identical to the Payment Card form. In the lower part what you see is a subform which is base on and contains the data from the Payments table. It is linked to the main form on the Customer Number. But since this has the calculated field Total Remaining its record source is the query Payments
Query. You can see here that I get the sum of payments with the expression

SumPrevious: DSum("PaymentAmount","Payments","[Customer Number] = '" & [Customer Number] & "' AND PaymentDate <= #" & [PaymentDate] & "#")

and the calcualate the total remaining with

TotalRemaining: [Forms]![New Payment Card]![StartingBalance]-[SumPrevious]

These expressions could be combined. This doesn't work well when there are payments on the same date, a design problem I'll let you solve. You could add the time or maybe distinguish between payments on the same date with the ID.

Back to the New Payment Card form and addressing your original question the Remaining balance is calculate with a function I put in the Remaining Balance module and is:

Public Function GetRemainingBalance(strCustomerNumber)

GetRemainingBalance = Nz(DLookup("[Starting Balance]", "[PCData]", "[Customer Number] = '" & strCustomerNumber & "'")) - _
    Nz(DSum("[PaymentAmount]", "[Payments]", "[Customer Number] = '" & strCustomerNumber & "'"))

End Function

I just put a call to this function in the control source of the text box for Remaining Balance so it doesn't get stored in the table. I'm not going to help you put it in the table because I won't be a party to evil. I'm fairly certain that anywhere you need this value I can show you how to get it.

That's it for now. Let me know if you have any questions.

 

[sneuberg]

Oh by the way I said the attached database wasn't complete. One of the things I didn't get around to is putting requeries and refreshes in the appropriate places. So to see the form text boxes updated you may have to close and reopen the form. Of course you could add the requeries and refreshes.