Assigning records to users

[JacquiNewsGroups]

Good Morning

Scenario:

Record Table
User Table

For example: 852 Records need to be assigned equally among 4 Users and
displayed in a report/form in User sequence.

I need the 'equation' / criteria that is entered into a query.

I am sure that given time I can work this out, but time is limited at the
moment, so any help will be greatly appreciated.

Regards,

JG

 

[John Spencer]

Well, you really haven't given us much information on the data structure.

For instance, if you have an autonumber field in the record table you could
divide the records into 4 groups using modulus arithmetic.

SELECT [AutonumberField] Mod 4 as TheRecordsGroup, *
FROM [Record Table]

If you don't have that you would need to create a ranking order based on
something and use the mod operator to generate a group number for zero to
three. See below for an example of one way to generate a rank order number

What information do you have in the USER table to allow you to designate
groups. A query something like the one below would give you a number for the user

SELECT A.[UserName], Count(B.[UserName]) as TheUsersGroup
FROM [User Table] as A LEFT JOIN [User Table] as B
ON A.[UserName] < B.[UserName]
GROUP BY A.UserName

Joining the two queries on the Group numbers would give you the results you want.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[JacquiNewsGroups]

Thank you for this, I am using your first example ie modulus arithmetic and
so far so good. Thank you for prompt reply.

- JG

Well, you really haven't given us much information on the data structure.

For instance, if you have an autonumber field in the record table you
could divide the records into 4 groups using modulus arithmetic.

SELECT [AutonumberField] Mod 4 as TheRecordsGroup, *
FROM [Record Table]

If you don't have that you would need to create a ranking order based on
something and use the mod operator to generate a group number for zero to
three. See below for an example of one way to generate a rank order
number

What information do you have in the USER table to allow you to designate
groups. A query something like the one below would give you a number for
the user

SELECT A.[UserName], Count(B.[UserName]) as TheUsersGroup
FROM [User Table] as A LEFT JOIN [User Table] as B
ON A.[UserName] < B.[UserName]
GROUP BY A.UserName

Joining the two queries on the Group numbers would give you the results
you want.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Good Morning

Scenario:

Record Table
User Table

For example: 852 Records need to be assigned equally among 4 Users and
displayed in a report/form in User sequence.

I need the 'equation' / criteria that is entered into a query.

I am sure that given time I can work this out, but time is limited at the
moment, so any help will be greatly appreciated.

Regards,

JG