[SOLVED] MS Access SQL SELECT TOP QUERY

MS Access SQL SELECT TOP QUERY

I need some assistance on the SELECT TOP Queries and whether it will be easier to write the code in VBA or carry on using SQL.

I have a table (StockSheet) which has 17 columns and over +500 records, the only relevant fields for the Queries are Pallet Id and Inv Code.

I have also created 3 Forms to display the Queries as Subforms.

The first query (LoadSpec1Qry) is as follows:

SELECT TOP 20 StockSheet.*
FROM StockSheet
WHERE (((StockSheet.[Inv Code])="AG"))
ORDER BY [Pallet Id];

The second query (LoadSpec2Qry):

SELECT TOP 20 *
FROM (SELECT TOP 40 * FROM StockSheet WHERE (((StockSheet.[Inv Code])="AG")) ORDER BY [Pallet Id])  AS t
WHERE (((t.StockSheet.[Inv Code])="AG"))
ORDER BY t.[Pallet Id] DESC;

The third query (LoadSpec3Qry):

SELECT TOP 20 *
FROM (SELECT TOP 60 * FROM StockSheet WHERE (((StockSheet.[Inv Code])="AG"))     ORDER BY [Pallet Id] DESC)  AS t
WHERE (((t.StockSheet.[Inv Code])="AG"))
ORDER BY t.[Pallet Id] DESC;

To summarise: in Form 1 it will display the first 20 records (LoadSpec1Qry) where the Inv_Code = "AG", in Form 2 (LoadSpec2Qry) it will display records 21-40 and in Form 3 (LoadSpec3Qry) will display records 41-60.

The issue I am having is for eg: if there are 52 records where Inv Code = "AG", LoadSpec3Qry will display records 33-52. The result I am trying to get is for LoadSpec3Qry to display records 41-52.

Is there a way where SQL can determine there is X many records for the Inv Code and display the correct number of records per query?

Please advise if I should use VBA rather than SQL for this issue, or if anyone requires more info/screenshots or a better explanation about the issue.

Regards, Brendon

Solution

Just an idea, but maybe you could try to generate row numbers and then select based on row number: 1-20, 21-40 and 41-60. Wouldn't that work!? Something like this:

SELECT *
FROM (
    SELECT 
    (
        SELECT COUNT(t1.[Pallet Id]) + 1 
        FROM [StockSheet] t1 
        WHERE t1.[Inv Code]="AG" AND t1.[Pallet Id]<t2.[Pallet Id]
    ) AS RowID,
    t2.*
    FROM [StockSheet] AS t2
    WHERE t2.[Inv Code]="AG"
)
WHERE RowID BETWEEN 1 AND 20;
--WHERE RowID BETWEEN 21 AND 40;
--WHERE RowID BETWEEN 41 AND 60;