Question Summarizing(?) every n

[thundermane]

Hello everyone. I have a names table linked to a 2nd table where I have a date/time stamp field and a quantity field. Is there a way to summarize the quantity field for every n, and show the corresponding date/time stamps?

For example, I have these data for a guy named "Stephen"

Date/time Qty
"9/1/2011 13:05" 2
"9/2/2011 13:52" 2
"9/2/2011 18:02" 1
"9/3/2011 13:00" 2
"9/4/2011 14:20" 3
"9/4/2011 17:01" 1

and I want to summarize(? or group, dunno the right word) them every 5 Qty
so I would get something like

Date/time Qty
"9/1/2011 13:05" 2
"9/2/2011 13:52" 2
"9/2/2011 18:02" 1
Total 5
"9/3/2011 13:00" 2
"9/4/2011 14:20" 3
Total 5
"9/4/2011 17:01" 1

Showing the total on a row or a column doesn't matter, as long as I can summarize/group the rows accordingly. Is there a way to do it? I'm using Access 2007 by the way.

 

[vbaInet]

What are the records sorted by?

 

[Brianwarnock]

What if the qty doesn't group so conveniently ie the first three had all been 2 ?

Brian

 

[vbaInet]

Good thinking Brian.

 

[thundermane]

Thanks for the reply folks.

@Brianwarnock: That's actually one of the problems I've encountered. I guess it doesn't really matter, as long as I could group to the last record that is nearest to whatever grouping number I choose (yes, the 5 could change). Even if the 3rd record had "3" I would still like the first 3 records to group together and still show the total as "7"

@vbaInet: The records are sorted by the Date/Time field. There are other fields of course, like an ID field, etc. but it's primarily sorted by the Date/Time field.

 

[vbaInet]

I can only think of two ways at the moment. Throw me a db and I'll show you how.

 

[thundermane]

Thanks for the help. Here is the DB...

 

[vbaInet]

I ended up thinking of three ways.

See attached. For the first two methods, pay attention to the Can Grow and Can Shrink properties of the textboxes used in the functions and that of the sections.

For the third method, I would advise you don't run the query separately. Well, you can but just remember to run ResetVars() before opening the query.

NB: The db is in 2000 format for the benefit of others.

 

[thundermane]

Wow, thank you very much! It really is a lot more complicated than I thought. Not being a programmer, it is going to take me a while to study your code. I'm just doing this to learn something new while trying to automate a few of the tasks here in my little home business.

Thanks again.

 

[vbaInet]

Yes, unfortunately it's not a built-in feature. I just thought about another way of doing it using a Page Break

Keep the business going