YTD Totals in Form View and Reports

[nrh0804]

My database includes separate fields for (each) Quarterly Payment Due and Quarterly Payment Amount Paid. I also have fields for YTD Due, YTD Amount Paid and Balance Due for each client record.




I have included the formulas for the YTD and Balance Due fields. In my test record they work fine. However, all client records show the YTD and Balance Due for the test record and not for the individual clients’ records. How do I fix that?


I’m having the same problem when creating a Client Report which should show each client, their YTD Due Amount, their YTD Amount Paid and Balance Due. How do I fix that?


As always, I appreciate any advice you may provide. Thank you.

[jzwp11]

You should not be storing calculated values in your tables. Further, if clients have multiple payments, you should be storing those as records in a related table

tbClients
-pkClientID primary key, autonumber
-txtClientName

tblClientPayments
-pkClientPaymentID primary key, autonumber
-fkClientID foreign key to tblClients
-dtePayment (payment date)
-currAmtPaid

To get a YTD value you would just use a query to add up the payments in tblClientPayments and group by client.

As to the Quarterly payment due, does that value change from quarter-to-quarter or does it stay the same?

[nrh0804]

Thank you for your reply. Quarterly payments may in fact be different.

That said, I appreciate the info you took the time to share. However, I am a self-taught novice who created this database strictly for my own business. I'm sure your reply make perfect sense to a database or computer person. Unfortunately, I'm neither and haven't been able to figure it out.

[jzwp11]

Unfortunately, I'm neither and haven't been able to figure it out.

I was in your position when I first started with Access. It has been a great learning experience.

What I showed in my earlier post is just 2 tables and the fields in those tables. In a relational database like Access, information in one table is related to corresponding data in another table. So for example, tblClients would just hold basic information about a client--name address phone number etc. Each different client would be a record in that table. Now, based on the description of your process, you have client who send you payments on a quarterly basis or in other words many payments over time. So you have to relate the payments made to the client that made them. So tblPayments has information about the payment including a field that tells which client (fkClientID). If you notice in the client table there is a field called pkClientID. The prefix pk just says that this field is the primary key of the table. A primary key field is just a field that holds a unique value. Having a unique value allows Access to unique identify the record. This primary key is used to link/relate to other tables that have data that is related to the client--in other words their payments.

An example might help.

Let's say that we have 2 clients. If you look in the datasheet view of the table you might see something like this:

pkClientID|txtClientName
1|ABC Company
2|CBS Company

Now, let's take a look at the payments made by these companies. From the data below we see that we have five unique records as indicated by the pkClientPaymentID field. This is just an autonumbered value and is unique for each record. Since it is unique, it is the primary key for the table. Now, let's look at the fkClientID values. We see three records with a value of 1 and two records with a value of 2. Since the fkClientID relates back to the client table's primary key value (pkClientID=fkClientID) we can say that ABC Company (pkClientID=fkClientID=1) has 3 payments and that the other two payments are tied to CBS Company (pkClientID=fkClientID=2).


pkClientPaymentID|fkClientID|dtepayment|currAmtPai d
1|1|9/30/2012|500.00
2|1|6/30/2012|300.00
3|2|10/1/2012|100.00
4|2|11/1/2012|100.00
5|1|12/31/2012|500.00

So the pkClientID-->fkClientID JOINS the data in the two tables. When you have two table joined in such a fashion it is called a relationship (the data in the two tables are related).

In relational databases, there are some rules (called normalization). This site has an overview. You basically have to know the rules in order to design a proper relational database.

This site has some good tutorials with someone just starting out with Access and it shows how the rules of normalization are tied into your table structure.