Count and assign

[Dana]

I have a query that selects records based on whether a field is blank,
Query1. From this query I developed a make table query where another cell is
blank, Table2. From this table we wrote a query that will count the number
of records and divide them by 7(Query2).

What I need to do now is apply this query somehow to count the number of
records in Table2, divide them by 7. I then need to take the groups of 7 and
assign each group to a different person.

We will run queries daily to update the Table2, so that when we run the
query to assign names, we are only counting/dividing/assigning those that
have not been previously assigned.

My questions is how do a write a query that will take the total unassigned
(assigned cell is null) and count them divided them by the 7 and then assign
a person’s name to each group of 7?

Or is there an easier way to compute the total unassigned and divide by the
number of workers I have and assign each record to a different worker?

Using Access 2003

 

[John Spencer MVP]

It would help if you posted your queries. (View: SQL, then copy and paste).

Generically. I would have a table of Persons with a personid (personName) and
a unique number (from zero to six) for each person.

queryOne
SELECT PrimaryKeyField
FROM SomeTable
WHERE SomeOtherField is Null

queryTwo
SELECT A1.PrimaryKeyField
Count(A2.PrimaryKeyField) Mod 7 as Rank
FROM QueryOne as A1 LEFT JOIN QueryOne as A2
ON A1.PrimaryKeyField > A2.PrimaryKeyField
GROUP BY A1.PrimaryKeyField

queryThree - which could be used as the make table source or instead of having
a make table, just use the query
SELECT PrimaryKeyField, PersonNumberTable.PersonName
FROM QueryTwo INNER JOIN PersonNumberTable
ON QueryTwo.Rank = PersonNumberTable.NumberField

If you table and field names consist of ONLY Letters, Numbers, and the
underscore character, you could probably build this all in one query instead
of having to nest saved queries.

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

 

[Dana]

Thanks for the input here is a copy of the query used to select items to be
worked in this database.

The query name is “BA To Work 2â€. It is a query that pulls records from a
table where the “Sales Order Nr†and the “PR Nr†are null, the Trx Type is
not equal to 861 and the Unit Prc Am has a 3, 4 or 5 integers after the
decimal point..

From this query I need to count the number of records where the “BA
Assigned†field is null. I need to count these records, divide by 7 and then
assign the groups of records to a BA to work. I am assuming that is what the
queryTwo in your example does? But since it has been a while since I worked
in Access I am not familiar with the structure noted or specifically how I
would actually build the expressions.

The third query, queryThree, is assigning the names to each of the seven
groups.

I now have a table called BA Names with two fields, ID Number and BA Name.
The table is indexed on the ID Number field.

Here is the SQL View of the Query, BA To Work 2:

SELECT [Raw Data from BOSS EDB].ID, [Raw Data from BOSS EDB].[Trx Type],
[Raw Data from BOSS EDB].[Prcrmt Instrmt Idnt Nr Id], [Raw Data from BOSS
EDB].[Bsm Contr Line Item Nr], [Raw Data from BOSS EDB].[Doc Nr], [Raw Data
from BOSS EDB].[Unit Prc Am], [Raw Data from BOSS EDB].[Tot Am], [Raw Data
from BOSS EDB].[Currency Type], [Raw Data from BOSS EDB].[Sales Order Nr],
[Raw Data from BOSS EDB].[PR Nr], [Raw Data from BOSS EDB].[Prchs Rlse Dt],
[Raw Data from BOSS EDB].[BA Remarks], [Raw Data from BOSS EDB].[FOL
Remarks], [Raw Data from BOSS EDB].[BA Assigned]
FROM [Raw Data from BOSS EDB]
WHERE ((([Raw Data from BOSS EDB].[Trx Type])<>861) AND (([Raw Data from
BOSS EDB].[Unit Prc Am]) Like "*.???" Or ([Raw Data from BOSS EDB].[Unit Prc
Am]) Like "*.????" Or ([Raw Data from BOSS EDB].[Unit Prc Am]) Like
"*.?????") AND (([Raw Data from BOSS EDB].[Sales Order Nr]) Is Null) AND
(([Raw Data from BOSS EDB].[PR Nr]) Is Null));