How to call multiple distinct groups of data?

[Amy Badgett]

Okay, simply put I need to write a query that will return x number of groups of y number of groups that each hold z number of records.

Example:
There are four teams (x)
with 15 team members per team (varying on the day) (y)
and we need to pull 350 records per team member (z).

Each team member needs to have a distinct set of 350 records from the database.

Help? Please?

Thanks!

[Rabbit]

You can use a subquery in the where clause to return the unique IDs of the top 350 records filtered by the parent query's team member.

[Amy Badgett]

Can you give me an example??

[Rabbit]

SELECT * FROM Table1 AS x
WHERE UniqueID IN
  (SELECT TOP 10 UniqueID
   FROM Table1
   WHERE Category = x.Category
   ORDER BY Sales DESC)

[Amy Badgett]

What I need to be returned is x number of distinct groups of 350 from the larger group that is being called. I need all of these groups to be returned at the same time, not one at a time. There is a distinct number per record (Caller ID), and the original group of records being called calls the records in order, but some Caller IDs are skipped (i.e.: 2,3,5,7,8,9,12) . For each group of 350 records, I need to have the lowest and highest Caller IDs returned.

So, I need something like this returned:
1 – 350
351 – 700
701 – 1050
…

[Rabbit]

I have no idea what you're looking for. Can you provide some sample data and expected results?

[NeoPa]

Amy, using x, y & z is fine if your English is good enough, but without clearly specifying what you need you would be better advised using more meaningful terms. That way at least we'd have some form of context to fill in the gaps as to what you are trying to ask. I expect there are others, like me, who'd be only too willing to help if we but had a question we could understand.

[Amy Badgett]

Problem restated:

I have a database of several thousand records, I have 4 groups with 15 members each and I wish to write a query that will give each member a set number of different records to work on; so no member from any team should get any of the same records.

[NeoPa]

It sounds like the groups is entirely inconsequential to the question. Am I right in thinking you have a table in a database with several thousand records in it and each record needs to be assigned to one, and only one, person (who happens to be a member of one of four distinct teams though this isn't relevant) from a pool of sixty such people? Also that the records need to be assigned evenly, such that the workloads of each person are similar?

As this seems somewhat inconsistent with your first post I won't assume I have this right until I get confirmation from you. I can say that if this is the situation then it's not a trivial problem, and will require some thinking about.

Presumably, if this is all correct, then you have a table in the database which contains references to each of the sixty people involved. If a record is to be assigned to a person then the ID for that person would need to be stored somewhere to indicate that the record has been assigned and to whom.

[Rabbit]

It would help if you could provide sample data and results. But if I understand correctly and everything is sequential, you can use a mod 4 to assign the records.