Query Values 26-50 (1 Viewer)

Tieval · Feb 23, 2018

Hi,

I am looking to find values 26 to 50 from a query and found how to do it but have since lost it.

Code:

SELECT TOP 25 Table.Field, Count(Table.Field) AS CountOfField
FROM Table
GROUP BY Table.Field
ORDER BY Table.Field;

This code will return the top 25 and I have seen a method that basically says select top 25 where not in top 25 and therefore gives the next 25. I just cannot find or remember how to code it.

Any help would be greatly appreciated.

Ranman256 · Feb 23, 2018

i have a field MARK,
Q1, select top 25 where MARK=false
Q2,(mark the 1st 25) update Q1 ,set MARK =TRUE

then I run Q1 again, i get 26-50.
and continue running...

plog · Feb 23, 2018

You can do this calculating the rank of each record in your existing query.

First, save the query you posted as sub1. Then build another query using it, bring down all your fields into it and add this as a calculated field:

Rank: 1+ DCount("Field", "sub1", "Field<'" & Field & "'")

Then, underneath that field, for criteria put this:

>=26 AND <=50

arnelgp · Feb 23, 2018

Select top 25 * from (SELECT TOP 50 Table.Field, Count(Table.Field) AS CountOfField
FROM Table
GROUP BY Table.Field
ORDER BY 2 desc)

Air code.

Tieval · Feb 23, 2018

Ok, I have the following code:

Code:

Dim scantotals As String
    scantotals = "SELECT TempTotal.Blade, Count(TempTotal.Blade) AS CountOfBlade " & _
                    "FROM TempTotal " & _
                    "GROUP BY TempTotal.Blade " & _
                    "ORDER BY TempTotal.Blade ;"

I can set this as the record source for a form and get say 100 rows of two column data on it, a very long thin form.

I am trying to move this to a five column system with each column being rows 1-20, 21-40, 41-60, 61-80 and 81-100 and this will allow me a pop-up form giving a quick overview of values.

My idea is to query scantotals for the first, second, third, fourth and fifth chunk of records as record sources of instances of sub-forms.

arnelgp · Feb 23, 2018

You need to use partition() function.

Tieval · Feb 23, 2018

Now you have confused me, I thought that partition was for breaking down strings

arnelgp · Feb 23, 2018

Im not on my pc right niw so i cannot show you. Try googling: ns access partition on query.

arnelgp · Feb 23, 2018

Select Partition([BLADE],1, DCOUNT("*","TEMPTOTAL"), 25) as range,
Count([blade]) AS Count
FROM temptotal
Group by Partition([BLADE],1, DCOUNT("*","TEMPTOTAL"), 25)

From the abive query create a crosstab query

Query Values 26-50 (1 Viewer)

Tieval

Still Clueless

Ranman256

Well-known member

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

Tieval

Still Clueless

arnelgp

..forever waiting... waiting for jellybean!

Tieval

Still Clueless

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread