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));
KARL DEWEY said:
Use your table and field names --
SELECT Q.Team, Q.Item_no, Q.Points, (((SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.Team&Q1.Item_no&Q1.[Points] <
Q.Team&Q.Item_no&Q.[Points]))\ ((SELECT COUNT(*) FROM [Product] Q2)/[Enter
number of splits]))+1 AS Segment
FROM Product AS Q
ORDER BY Q.Team&Q.Item_no&Q.Points;
Dana said:
I am just learning access. I need help with query. I need to count the number
of records in a query with a certain field that is empty. Once i get a total,
I need to divide that total number of records by 7, then I need to assign
each of the 7 groups of records to a person.
Any ideas on how I can do this? I already have a table with the total
records in it. I did a query to make a table with a certain field is null.
Thanks
