numbering report rows over nested groups (1 Viewer)

[senseidan]

Hi all,
I have a report, grouped by day then by some ID. If I use a textbox with ControlSource=1 and RunningSum OverGroup/OverAll I have the results shown below, different from what I need (restart after each day). Any suggestions ?

ex: .........................................................REPORT
runSum..runSum .....number.................... 01ian2019
overall...overGroup...I need ..................ID.........VALUE
....1........... 1..............1.................... 1000.......300
....2 .......... 2 .............2 ................... 1000.......350
........................................................total for id1000=650
....3............1..............3.....................1001........200
....4............2..............4.....................1001........600
.........................................................total for id1001=800
........................................................TOTAL for 01ian2019=1450
...........................................................02ian2019
....5 ...........1..............1......................1002.......450
....6............2 .............2 .....................1002.......620
......................................................... total.....

 

[theDBguy]

Hi. Have you tried adding a group for the dates as well?

 

[senseidan]

Sure. If I press Grouping&Sort button, I see a group on Date then a group on ID. In fact, this report is a copy from another, grouped only on date, where numbering is as I expected. The problem is that I added a new group, the ID group, inside (nested on) the Date group

 

[theDBguy]

Hi. Can you post a sample copy of your db with test data, so we can better understand what is happening? Thanks.

 

[senseidan]

OK. I have attached a (very) simplified version of my problem. Running Sum is set OverGroup and setting OverAll does not produce the result I expect, which is starting over on new day. Thank you.

 

[theDBguy]

Hi. Thanks. I see what you mean. I guess you could try a different approach. For example, take a look at the attached modified version of your demo.

 

[senseidan]

Sorry, I don't understand. I see #Error instead of row number. What do you mean by adding the word Row in Control Source of the Text Box? Thanks.

 

[theDBguy]

Hi. What version of Access are you using? I used 2010 to modify your file. Did you click the Enable Content button? Here's what I see...

 

[senseidan]

I'm using Access 2016. I was not paying attention to Enable Content button but I restarted your db and there is no warning. Maybe if you can tell me what are you trying to do... There is a function/property named Row in Access 2010?
P.S. In your image is exactly what I tried to achieve.

 

[senseidan]

Have you changed the image? The first one was correct. And I see that somehow my totals for nrC are lost.

 

[theDBguy]

Have you changed the image? The first one was correct. And I see that somehow my totals for nrC are lost.

Hi. I was only paying attention to the row numbers and didn't pay attention to the totals. Let me take another look. The "Row" part is a calculated column I added to the Record Source of your Report. It went like this:

Row: DCount("*","Table1","ID<=" & [Table1].[ID] & " AND dataV=#" & [Table1].[dataV] & "#")

 

[theDBguy]

Okay, it looks like this now...

 

[senseidan]

Indeed, this is what I want. Maybe the fact that I have #Error instead of rows numbers is due to the fact that in Control Source of my text box is just the word Row. If I understand correctly, I have to add the rest of the expression in order to get the results shown in your image? I’m asking because I’m away from my computer now and, as anxious as I am, I can’t try until tomorrow.

 

[theDBguy]

Indeed, this is what I want. Maybe the fact that I have #Error instead of rows numbers is due to the fact that in Control Source of my text box is just the word Row. If I understand correctly, I have to add the rest of the expression in order to get the results shown in your image?

In my mind, you shouldn't have to add anything because I already added it. But if it's not working, then manually adding it yourself is worth a try. Just in case though, since I changed the sort order to get the latest result, I am reattaching the updated file here.


Also, if it might help, here's the Record Source I am using:

SELECT Table1.*, 
DCount("*","Table1","ID<=" & [Table1].[ID] & " AND dataV=#" & [Table1].[dataV] & "#") AS Row 
FROM Table1;

 

[senseidan]

Thank you very much. I will try this first thing in the morning and I’ll be back with the results. I think it might work. Thanks.

 

[theDBguy]

Thank you very much. I will try this first thing in the morning and I’ll be back with the results. I think it might work. Thanks.

Hi. You're welcome. Hope you get it to work. Good luck and let us know how it goes.

 

[senseidan]

Well, I'm back with a VERY BIG THANK YOU. It worked perfectly. #Error was due to my regional settings, different than yours. I just formatted the date and fiddled a little with my (a little more complicated) real case scenario and now I am very happy with the result. Thanks a lot.

 

[theDBguy]

Hi. You're welcome. Glad to hear you got it sorted out. Good luck with your project.