Grouping and Sum

[dejhost]

Hello everybody!


It's been a long time since I used Access - just picked it up again to keep an overview over my account balance. Currently, I am creating an report that lists all of my transactions and groups them on a monthly basis. So below each month, a summary is displayed: all the individual transfers are added up to a single value. In addition, I would like to complete this monthly overview by showing the account balance. This is where I have a problem. Works fine for the first months, but scrolling down the report in "Report view", the numbers of the summary begin to jump around. One second, I turn out to be a millionaire, the other, I am hopelessly in debt.

For adding up the monthly transaction, I have a field in the "Date footer": =Sum([Amount]*(-1))
For calculating the monthly account balance, I have a field in the "Date footer": =Sum([Amount]*(-1)) , and I use the "Running Sum" in the Data properties: Over Group.


What am I missing here? See below a screenshot of the report in design view.


Thanks so much for helping!
dejhost

[ranman256]

for a monthly total , in my query I would have a Month field: Format([date],"yyyy-mm") as Month.
so I can put a footer sum.
and an [account] footer.

doing it by the date over detailed.

[dejhost]

Hi Ranman,
I inserted the month-field into the query as you are suggesting, but I can't follow you with the rest of the instructions. Will continue o try&error, but feel welcome to explain...

[rpeare]

why are you sorting by your date field 3 times (1 group by 2 sort by). Also you should stay away from using reserved words in your object names (table names, field names, form names etc), DATE is one of those reserved words.

Lastly, your account balance, unless you are storing the previous month's starting and ending balance on a table, is a calculation of all deposits/withdrawls from an account. If your query is just pulling data from the current month you won't be able to use the query to figure out your starting balance (upon which you can figure your ending balance).

If you want to figure your starting balance (assuming you're not storing a value at the end of each month) you would have to have something like

=dsum("[AmountField]", "TransactionTable", "[TransactionDate] <= #" & [Enter the First Day of the Month]" & "#")

Where AMOUNTFIELD is the field storing the transaction amount
TRANSACTIONTABLE is the table storing your transactions
TRANSACTIONDATE is the date of your transaction

if you need to break this down to individual customers etc you'd have to add criteria to the dsum function

This formula assumes your debits and credits are entered with the appropriate sign (-/+) if not you have to account for that in your dsum formula as well.

[dejhost]

Cheers, rpeare. I changed the field "Date" to "Dato" to avoid any troubles. I also deleted unnecessary grouping - only one group remains: Dato, from oldest to newest, with "Amount" totaled.
good news: all transactions are in one table. The query that lies below the report is basically only a copy of that table.

So my "account balance" only has to add up all transactions up to the current month - shouldn't be a big deal. I realize now, that the field "account balance" changes while scrolling down the report, because it only takes into consideration the transactions currently visible. In other words: as soon as the first month disappears from the screen, the "account balance" is only based on the 2nd month - up to the current month. That doesn't make much sense to me!

dejhost

[rpeare]

not sure if there is a question in there but that's why I mentioned the dsum function, you have to find the total of all transactions prior to the current month to get the starting balance, *then* add the current month's activities to get the ending balance.

[dejhost]

I am with you when it comes to the concept, but I am still struggling on how to and where to setup the dsum function:
If I enter into my query:

Expr1 = dsum("[Amount]", "Table_Transaction", "[Dato] <= #" & [Enter the First Day of the Month]" & "#") 

it says invalid syntax. It would probably help if I would understand the part

#" & [Enter the First Day of the Month]" & "#")

a bit better. Sorry for beeing so slow, but it's at least 8 years since I used Access...

Googling the subject of dates, I figured the following criteria for my query:

Year([Dato])* 12 + DatePart("m", [Sato]) <= Year(Date())* 12 + DatePart("m", Date()) - 1      

This way I can limit the query to display only all transfers up to last month, but I do not manage to get them summed up. I must be missing something.

Any advice appreciated!

[rpeare]

Don't perform domain functions in queries, the dsum function would be in a formula on your report in the CONTROL SOURCE of a text box for instance.

Just remember your date criteria in the dsum function will only work if you have a single entity. If you have multiple customers (for example) you'd have to dsum by the customer number as well as the date.

[dejhost]

thanks again, rpeare. I undid the changes in the query. This is what my report looks like right now:



I am still puzzled with

#" & [Enter the First Day of the Month]" & "#")

...returns a syntax error message. And I still don't figure why this is necessary at all. Why doesn't the sum-function in the grouping section (see bottom of the screenshot above) do the job? At each end of the month, all transactions that occurred in the actual & previous months are supposed to be summed up.
btw: [Dato] is set to a format "short".
cheers,
dejhost

[rpeare]

Make a form, on that form put 2 text boxes, name them

StartDate
and
EndDate

Save the form as frmReportSetup

The query running your report has criteria for a date range, in the date range put

between forms!frmReportSetup!startdate and forms!frmReportSetup!enddate

Change your dsum function to

=dsum("[-Amount]", "TABLENAME", "[Dato] < #" & forms!frmReportSetup!StartDate & "#")

Where TABLENAME is the actual name of your transaction table.

Make sure you have a start date and end date in the appropriate fields of your form then run the query first to make sure you're getting expected results, then run the report.