Carry forward previous balance in Access 2

[krissyKat]

Hi,i have a annoying problem I have over 200 accounts running in Microsoft Access a so called computer guy made the program for us(he cant figure it out) BUT it dosent carry the previous months balance forward and add it to this current month Please anyone know the code pleeeeease it takes sooo long to go through everyones account buy hand and its getting very annoying
Thanks krissykat

 

[HitechUser]

You could help us by better describing the table(s) in the database. Your question needs more detail.

 

[willir]

Normally, totals are not stored on tables. See following, especially the 3rd normal form...

Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Totals by month should be / can be calculated for each month provided the data is stored in a manner where totals can be retrieved by date. For example...

SELECT Sum(YourField) FROM YourTable WHERE
Month(TransactionDate) = 1 and Year(TransactionDate) = 2005;

Using the one date field, I can calculate totals for any month and year.

Having said that, if you have a large amount of data where it is impracticle to calculate totals, then totals can be stored in a Balance table. For example...

tblBalance
BalanceID - primary key
AccountID - foreign key to account table
YearPeriod - text, eg: 200501, 200502...
BalanceAmount

Richard

 

[krissyKat]

Sorry everyone, the accounts information are first put into FORMS and we go to the Reports to print the accounts out and look at what they have brought. I need the totals from the Reports to carry forward each month
thanks krissykat

 

[GingerR]

duplicate post thread703-1000101

KrissyKat-looks like you are inexperienced and might not know what we are asking for/suggesting to you?

Maybe your "computer guy" should be the one posting here?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[krissyKat]

Yes i am inexperienced but i live in a very small country town and have asked and showed all the computer people in town(not many) and they have no idea Especially the guy that made the program, he hasent got a clue.
Please can you guide me through what you need to know, as i can learn
thanks

 

[willir]

Before we can look at the form, we need to understand the data.

Can you provide the names of some of the more important tables along with their fields so we can "see" how the data is stored. From this, we can then see a) if it is possible (since you already have some totals, it should be; b) how to specifically achieve your objective

 

[krissyKat]

Thankyou very much for helping.
The Tables are: CUSTOMERS & TRANSACTIONS. In Customers under field name there is, Customer I.d(auto number), Name(text),Address(text),Suburb(text),Postcode(text),Telephone(text).
In Trasactions under field name there is,Transaction I.d(auto number),Date(date/time),Customer I.d(number),Description(text),Price(currency),Ammount paid(currency)

Krissykat

 

[willir]

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

 

[krissyKat]

Thanks very much Richard for all your help will let you know how it all goes
thanks again krissykat

 

[willir]

Your work very hard Krissy... Hopefully, you understood the gist of what I was trying to explain, and that I was not too technical.

 

[krissyKat]

Hi Richard,i made a query table like you said with all the account information(ammount bought,ammount paid etc)and thats great it tells us alot but
I really do need the accounts balances brought forward from last months account, i was fidelling all day but i cant figure this bit of your letter out,what part do i go in Query or reports etc

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]

In my reports(were our accounts are by the month) hes got
="Summary for"&"Name'="&"("&Count(*)&""&llf(Count(*)=1."detailrecord"'"detail records")&")"
And for the total hes got Sum([Balance])

 

[willir]

Howdy Kat

I really do need the accounts balances brought forward from last months account

As I understand your data for the Transactions table, key fields are ...

Date (Should be changed to TransDate later)
Cusomter I.d
Amount paid

So far, from what you have provided, there is no monthly balance being stored.

I do not see a field call Balances. Is the the Price field involved?? How are the balances tracked??

In the report you have...

Sum([Balance])

Where does this field Balance come from? Is there another table?

Richard

 

[krissyKat]

Sorry Richard to stuff you around ive been away. There isent a field called balances, In the Reports the balance is the total for an Itemised account of what customers have purchased for each Month.

 

[willir]

In the Reports the balance is the total for an Itemised account of what customers have purchased for each Month.

Are you able to show how the balance is calculated?

 

[krissyKat]

Thanks for getting back so quick, were the ammount is calculated he has
="Summary for"&"Name'="&"("&Count(*)&""&llf(Count(*)=1."detailrecord"'"detail records")&")"
And hes got Sum([Balance]) were the ammount appears
is that what you mean?
krissykat