A different way to group data

[bvdahl]

Hello,

I have a table where I have a ton of entries for each employee in my
company. To simplify, we can say the table consits of two columns: emplyee_id
and amount. Obviously, grouping the data by employee_id is easy, but I need
to add a little twist.

I want to divide the data into batches of 100 for each employee so that the
query groups by not only employee_id, but returns one row for each group of
100 entries.

Any way of doing this?

Thx in advance

 

[Allen Browne]

Simplest way to do this is to create a report based on the query.

In the report, include a text box with properties like this:
Control Source =1
Running Sum Over All
Name txtCount

This gives you the count, so you can break every 100 records using:
([txtCount] - 1) Mod 100

If you need to do the numbering within a query, see:
http://allenbrowne.com/ranking.html#query

If there's a time delay between serving batches, a problem arises in a
multi-user environment, because the data could change (e.g. if a new
employee is added between serving batches.)

 

[bvdahl]

I do have date/time field that I can sort by. How would I use this to
achievethe desired result?