Thanks for posting back
Krissy
Transactions
Transaction I.d. - primary key
Date - date / time
Cusomter I.d
Description
Price
Amount paid
Well there is good news here and a bit of bad news.
Bad news first...
The field "Date" uses a "reserved" word. A reserved word is a word used by the system. For example, FORMAT, INT, IF, True, False, etc... and Date.
You can change the name of the Date to something like "TransDate". But you will have to change the name of anything referencing the "Date" field, such as forms and reports and queries from "Date" -> "TransDate". Backup your database before attempting this.
The good news...
Because you do indeed capture the date for a transaction, you have many options on how to report your data including the Previous Blance.
First, you do not really want the balance for the previous months transactions in your example. I suspect you want the total of the transaction instead.
...it dosent carry the previous months balance forward and add it to this current month
Use the Query builder and play with it a bit. The query wizard will help you get started...
1) Start "Create query by using wizard"
2) Select your transaction table
3) Select the CusomterID, Date and Ampount Paid
4) On the next screen, select "Summary"
5) For Sumary Options, select Sum, Avg and Count for the Amount Piad field
6) On the next window, select the Monthly interval
7) Select the "Modify" design and look how the wizard setup the fields to run the query. You can later "play" by entering different criteria and changing the formatting for the date field from month year to year, etc.
8) Run the query
Okay, I previously stated you probably do not need to find the previous month and add to the current total. The reason is that you probably just need to run the the SLECT query with the Sum....
SELECT Sum([Amount Paid]) FROM Transactions WHERE [Customer I.d] = [Enter Your Customer Number]
This will give you the total amount collected from the customer.
On your form, add a text box, and for the ControlSource enter...
=DSum("[Amount Paid]), "Transactions", "[Customer I.d] = " & [Customer I.d])
...assuming that the names you have given are correct, and the name of the Customer ID text box on the form is [Cusomter I.d]
If you get an error, review the correct spelling of the names of the control / text boxes on the form and correct the code provided.
Richard
