Group by CNT

[JohS]

Anyone who has a sample of Query which make a group of a COUNT of a text
field.

(I have about 15000 records which I would like to have presented in bulks of
500).

Thanks, JohS

 

[Guest]

One way is to use two queries. First add a number field to your table named
Group.
Create a select query and edit the SQL like this --
SELECT TOP 500 ....
Change it to an update query and put a prompt to update the number field.

Create another select query and add a prompt in the criteria of the number
field.

When you run the update query it fills the number field of 500 records with
the number you supply (first batch - 1, second batch - 2, etc.).

When you run the select query use the same batch number.

 

[Guest]

Try the following. It works by using integer division on the result of the
subquery to give a value for each group of 500 rows as ordered by the text
column. It does rely on the text column having unique values to guarantee
exact grouping, however. It probably will be quite slow due to the degree of
correlation involved, but indexing the text column should help:

SELECT *,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.YourTextColumn < T1.YourTextColumn)
\500 + 1 AS GroupNumber
FROM YourTable AS T1
ORDER BY YourTextColumn;

The ORDER BY clause isn't actually necessary, so if the query is being used
as the RecordSource for a report omit it and use the report's internal
sorting and grouping mechanism to group by the GroupNumber column.

If you omit the '+ 1' the group numbers will start from zero rather than one.

Ken Sheridan
Stafford, England

 

[JohS]

Lovely, thanks, JohS

Try the following. It works by using integer division on the result of
the
subquery to give a value for each group of 500 rows as ordered by the text
column. It does rely on the text column having unique values to guarantee
exact grouping, however. It probably will be quite slow due to the degree
of
correlation involved, but indexing the text column should help:

SELECT *,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.YourTextColumn < T1.YourTextColumn)
\500 + 1 AS GroupNumber
FROM YourTable AS T1
ORDER BY YourTextColumn;

The ORDER BY clause isn't actually necessary, so if the query is being
used
as the RecordSource for a report omit it and use the report's internal
sorting and grouping mechanism to group by the GroupNumber column.

If you omit the '+ 1' the group numbers will start from zero rather than
one.

Ken Sheridan
Stafford, England