Solved sum of records

[ProgramRasta]

Hello,

I have what will be an elementary question for many.

I have two tables:

Table A - date, customer id, closing balance at previous period
Table B - date, customer id, values

I am attempting to sum the totals per customer id and date using the closing balance in Table A as a starting point (like a Dsum()).

I can achieve the desired result using multiple queries but I am certain there is a more efficient way of doing it perhaps using recordsets? However, I am unable to get it to work as intended.

Thank you for taking the time to read the post.

Cat.

 

[CJ_London]

always helps if you include some example data, also never a good idea to store calculated values as it makes life more complicated down the line. If you must, store them in the same table with some sort of identified (presumably TableB also have an identifier to indicate type of value such as invoice, payment)

Try a union query

SELECT CustomerID, Date, Closing AS Value
FROM TableA
UNION ALL SELECT CustomerID, Date, Value
FROM TableB

Then you can group it

SELECT CustomerID, sum(Value) as NewBal
FROM
(
SELECT CustomerID, Date, Closing AS Value
FROM TableA
UNION ALL SELECT CustomerID, Date, Value
FROM TableB
) as UQ
WHERE UQ.Date>=DMax("date","TableA")

 

[ProgramRasta]

Thank you CJ for taking the time to reply.

Apologies, I am a novice in using the forums and indeed in using Access.

I have attached a sample file.

The desired result to be calculated is in Table B in a column named 'Balance' which hopefully it is clear to see that I am looking for the cumulative balance using a column in Table A as the starting point.

Many Thanks

Cat

 

[CJ_London]

OK you are talking about what is called a running balance or running sum. If so, they are not stored, they are calculated - what if a new record is inserted which is not the latest date? you would need to go back and recalculate.

For that to work, I suspect your sample is too simple. What if there are two transactions for the same customer on the same day? you need an autonumber primary key and perhaps a time element on the date as well if the order is important - you cannot rely on autonumbers being in the right order, their purpose is simply to provide a unique ID for each record.

Also your table structure is wrong - it should something like

PK....Customer...TransactionType....TransactionDate...Amount
1......Customer1..OpenBal...................01/12/2020...........100000
2......Customer1..Sales..........................01/12/2020................1000
3......Customer1..Receipts...................02/12/2020.................-500

You appear to be taking an Excel approach to your data, Suggest you google/bing 'database normalisation' to get an understanding of how databases should be structured

Using the above structure your running sum query would look something like this

SELECT A.PK, A.Customer,A.TransactionType,A.TransactionDate,A.Amount, sum(B.Amount) AS RunningSum
FROM myTable A INNER JOIN myTable B ON A.Customer=B.Customer
WHERE B.TransactionDate<=A.TransactionDate
GROUP BY A.PK, A.Customer,A.TransactionType,A.TransactionDate,A.Amount
ORDER BY A.Customer, A.TransactionDate

Note that reports have a running sum option, so the recordsource would simple be the name of the table, no need for a query

 

[ProgramRasta]

Hello CJ,

Thanks for your assistance thus far and I'm conscious of taking up your time on such a basic question.

The records of customer id and date are unique and there are no instances where they can be duplicated in my actual database.

I thought there would have been some way using recordsets to incrementally add the Closing Balance (TableA) + Sales(TableB) to the column 'Balance'(TableB) as a running total. I need to store these values for a calculation further down the line.

I'll have a further search on the web.

Thanks again for your time.

Cat.

 

[CJ_London]

Sorry I can't be of further help - think you will find you will get much the same recommendations as I provided. Good luck with your search

 

[bastanu]

When\how are you updating the ClosingBalance in Table A? Because the date in Table A will change the value of the Balance in Table B. As CJ mentioned the Balance field in Table B should not be stored but always calculated, including for

a calculation further down the line

In a totals query it would be very easy to do that as shown in the attached update file.

Cheers,

 

[ProgramRasta]

Sorry I can't be of further help - think you will find you will get much the same recommendations as I provided. Good luck with your search

You were very helpful - thanks again.

I probably should have iterated that I'm working with static data and it's not subject to a live working environment.

I got the intended result by doing a running sum on the sales by date and customer and then with a different query just appended the closing balance to the table. It's not very eloquent but it worked!

I have very, very limited development knowledge but I'm surprised it couldn't be done using recordsets and a loop. I guess I'll trust the experts on this one.

Have a great Christmas.

Cat.

 

[ProgramRasta]

When\how are you updating the ClosingBalance in Table A? Because the date in Table A will change the value of the Balance in Table B. As CJ mentioned the Balance field in Table B should not be stored but always calculated, including for


In a totals query it would be very easy to do that as shown in the attached update file.

Cheers,

Thanks for the response but the date in TableB is important. If I bring in the date, the running sum doesn't work - it just provides the total per customer which is not what I need.

Thanks for the taking the time to reply though.

Cat

 

[oleronesoftwares]

Hi, the closing balance, in what intervals are they entered, e.g daily, i assume its manually that its entered?

 

[bastanu]

You're welcome! I know the date in Table B is important but so is the one in Table A therefore my question about updating it.
Anyway, here is an update that uses recordset looping to update the balance field in Table B. The code is run by running the new query qryUpdateBalance. I included a copy of the original Table B for your testing to compare before and after running the code.

Cheers,

 

[ProgramRasta]

You're welcome! I know the date in Table B is important but so is the one in Table A therefore my question about updating it.
Anyway, here is an update that uses recordset looping to update the balance field in Table B. The code is run by running the new query qryUpdateBalance. I included a copy of the original Table B for your testing to compare before and after running the code.

Cheers,

Thank you for this extract of code.

I hope you have a wonderful Christmas.

Cat.

 

[bastanu]

You're very welcome and a very merry Christmas to you as well!

Cheers,

 

[CJ_London]

I'm surprised it couldn't be done using recordsets and a loop.

It can - but very slow compared with using sql. Personally would never even consider going that route - an example a few months ago involving around 80 thousand records - sql took around 10 seconds, vba took 30 minutes. Oh and developing time - SQL about 5 minutes, vba around an hour