Update query to keep totals

[Groundrush]

It's me again, still cant work out how to carry over the Balance from a previous week to the start of the next

My latest attempt was to create an update qry to keep a running total but I still cant get it right.

week 1 should have £450 o/s
and week 2 should start on £450 and end on £400

the report that I am using promtps you for a week no
and when you type in week 2 it should have £450 "cash in hand from Previous week"

Please can someone check check the attachment and tell me where I am going wrong and prevent me from smashing my PC up against the wall

thankx

 

[BLeslie88]

Modified your query....

Here ya go... Try this sql statement.....

SELECT PettyCash.[Week No], Calender.[Week Ending], Sum(PettyCash.Debit) as [Total Debit], sum(PettyCash.Credit) as [Total Credit], sum(PettyCash.[Credit]-PettyCash.[Debit]) AS Balance
FROM DescriptionOfGoods INNER JOIN (PettyCashUsers INNER JOIN (PettyCash INNER JOIN Calender ON PettyCash.[Week No] = Calender.[Week No]) ON PettyCashUsers.[Petty Cash Users] = PettyCash.Name) ON DescriptionOfGoods.DESCRIPTION = PettyCash.Description
WHERE (((PettyCash.[Week No])=[ENTER WEEK 1 or 2]))
Group By PettyCash.[Week No],Calender.[Week Ending];

Regards,
Brian

 

[DBL]

Groundrush. I think your main problem with this is the fact that Access isn't designed to store dynamic calculations. You don't need to save the petty cash available to the base table because you store all the information that makes up that calculation. You store the cash that comes in and the cash that goes out. Using that you can calculate the current balance available at the current time, by taking the cash out away from the cash in. That figure would always be as up to date as the information entered in to the database. If you stored the date when the cash goes in or comes out you could also use that to show you the available cash on a given date.

Normally the only time you would break the rule is when you would store the selling price of an item at the time of sale to a customer's order - so that if the product price goes up in the future, you've always got the exact price at the time of sale.

 

[BLeslie88]

Sorry try this...

I misunderstood...

Here I made changes to your db, I added a sub-report and one table to hold current week... Hope this helps, if you have any questions, write me....

Regards,
Brian K Leslie

 

[Groundrush]

Thank you both for your reply and Brian thanks for taking the effort to work it out on the database.

I will have to get a new PC to replace the one that I smashed up against the wall to see how your solution works

I will let you know how I get on

Regards

 

[RV]

I agree with DBL :

>You don't need to save the petty cash available to the base table because you store all the information that makes up that calculation<

I adapted the query your report is based on just a little bit (Access97)...

HTH, tell us when you finally have succeeded


RV

 

[Groundrush]

Thanks RV

I see what you have done and it makes sense now, although it only works for the 2nd week, when you start the next week it doesn't carry over the Balance.

I changed the data to make it more simple to understand.
qry should read:
week 1 £500 credit bal £500
week 2 £ 100 debit bal £400
week 3 £ 400 debit bal £0

when you run the query for week 1 you get no results
which I understand why, as you need a starting balance.
week 2 has bal b/f £500 - £100 bal left £400 "WORKS WELL"
week 3 has bal b/f -£100 -£400 bal left -£500

I have also amended the report to show your results

thanks for your time.

 

[RV]

My basic proposal was meant to help you on your way, glad to know you "got the message".

See you,

RV

 

[Groundrush]

Sorry RV
misread your reply, didn't expect you to do all the work for me.

I am not very good at SQL so I am gratefull for all the the help I get from this Forum.

Thanks for your help

Regards