Hello all! I have a simple report that shows 2 months worth of data. The report groups by month with totals for each month. I need to subtract the total $$ for month 1 from the total $$ for month 2. Is it possible to perform this calculation without using VBA? Thank you in advance for your time.
Calculated fields in reports
- Thread starter rchhvol
- Start date
Trevor G
Registered User.
- Local time
- Today, 20:52
- Joined
- Oct 1, 2009
- Messages
- 2,367
Can you supply the table name and field names I will look at resolving this for you. It would requrie something like a DSUM and then the extra calculation.
Even an upload of your database (or extract) would be useful. You can always hide any sensitive data. If over 2mg big you need to zip up the database, which version of MS Access are you using.
Even an upload of your database (or extract) would be useful. You can always hide any sensitive data. If over 2mg big you need to zip up the database, which version of MS Access are you using.
Hi Trevor and thank you for lookikng at my request. I have attached the database. There are two months of data. I would like to be able to subtract the first month total from the second month total at the end of the report. Thank you for your time. -Pam
DCrake
Remembered
- Local time
- Today, 20:52
- Joined
- Jun 8, 2005
- Messages
- 8,620
This post is very confusing. The OP asks to subtract one MONTH from another MONTH, theny you look as the name of the mdb and it is called Dollars by DAY, then when you actually open the app it is actually comparing YEARS.
I just opened it. Yes, it looks like the OP has got the Groupings and data wrong.
rchhvol: You need to create another field that will extract the month and year from the date. You can use the Format() function but this needs to be an alias field in your query. That is:
AliasField: Format(...)
Perform the grouping on this alias column and do it just once.
rchhvol: You need to create another field that will extract the month and year from the date. You can use the Format() function but this needs to be an alias field in your query. That is:
AliasField: Format(...)
Perform the grouping on this alias column and do it just once.
Sorry for the confusion. The database holds dollars by day. The query will extract off specific months of data. I did not add the paraments for the selection process for simplicity purposes. The report will compare data for two months regardless of the year. The two months may or may not be within the same year.
The report groups by month so the text box with the total for each month occurs only one time, in the detail section. I don't have a separate text box for each month. I hope this clarifies things a bit. Thanks!
Ok, you say you want to subtract month 1 from month 2 but you do realise that you have January 2009 and January 2010? They are the same months but different years.
You didn't group properly. Once you clarify your objective I will show you how best to group.
You didn't group properly. Once you clarify your objective I will show you how best to group.
I just copied the same data and changed the year to create test data. The year is irrelevant. Think of the months as Jan and Feb of any year if that makes it any more clear.
My question is not about grouping levels but how to perform calculations between two (or more) different groups of data. Thank you for your time!
That also ties into the solution. If not properly grouped you will simply not get the right results. We wouldn't point out some discrepancy if it wasn't relevant to your question.
It can't be done without vba so I've done it in vba and attached it. See the code behind the Group's footer section.
I was afraid it couldn't be done without vba. Thank you so much for your help!
When you said doing it without vba, if you use the DSum() function in the control source of a textbox it's still vba. But here's the none vba editor way:
Code:
=txtSumOfDollars - DSum("[AmountField]", "[QueryName]", "Format([Day], "mmyyyy") <> " & Format([Day], "mmyyyy"))It sure does. It's shocking that we were here on the weekend
Thanks! This code works better for me. I'm intimidated by the VBA editor! -Pam
You shouldn't be. It's your friend
You will need it if you want your db to be more robust!
Trevor G
Registered User.
- Local time
- Today, 20:52
- Joined
- Oct 1, 2009
- Messages
- 2,367
Pam,
As you develop further things in your database you will find that the VBA part will have a bigger part to play. It does take time to learn but there will become a time when you can see the logic that will help you further.
One good thing is that you have found a forum with some very talented people who can assist you, and don't be to concerned about asking for help. Many people have been in the situation where they have had limited knowledge and also came up against brick walls, but others have come over the wall and can show you how they got over it and around it, and through it.
As you develop further things in your database you will find that the VBA part will have a bigger part to play. It does take time to learn but there will become a time when you can see the logic that will help you further.
One good thing is that you have found a forum with some very talented people who can assist you, and don't be to concerned about asking for help. Many people have been in the situation where they have had limited knowledge and also came up against brick walls, but others have come over the wall and can show you how they got over it and around it, and through it.
Similar threads
