Help with a query

  • Thread starter Thread starter Dana
  • Start date Start date
D

Dana

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
 
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;
 
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
 
The first query pulls IDs based on your criteria --
Raw_Data_Set --
SELECT [Raw Data from BOSS EDB].ID
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));

Second query separates data into number of groups you specify --
Data_Set_Groups --
SELECT Q.ID, (((SELECT COUNT(*) FROM [Raw_Data_Set] Q1
WHERE Q1.[ID] < Q.[ID]))\ ((SELECT COUNT(*) FROM [Raw_Data_Set]
Q2)/[Enter number of splits]))+1 AS Segment
FROM Raw_Data_Set AS Q
ORDER BY Q.ID;

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], Segment
FROM [Raw Data from BOSS EDB] INNER JOIN Data_Set_Groups ON [Raw Data from
BOSS EDB].ID = Data_Set_Groups.ID
ORDER BY Segment, [Raw Data from BOSS EDB].ID ;


Dana said:
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
 
Back
Top