MS Access SQL SELECT TOP QUERY

Question

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

Reinis Verbelis · Accepted Answer · 2021-08-05T16:51:07.400

1

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;

edited Aug 05 '21 at 16:51

answered Aug 04 '21 at 19:15

Reinis Verbelis

396
3
8

Thank you for your response. I fiddled around with your code and I like how it returns the row numbers. I am trying to add the following WHERE Clause onto the code but it's not returning the results I want: WHERE StockSheet.[Inv Code]="AG". Do you have any suggestions where I can put the above code? – Brendon Smith Aug 05 '21 at 13:45
Where exactly are you adding the WHERE clause? – Reinis Verbelis Aug 05 '21 at 16:35
I would say you have to add it for both inner selects so the row numbering is done only for "AG". – Reinis Verbelis Aug 05 '21 at 16:50
Thank you very much, it works perfectly. Appreciate the assistance. When I was testing, I didn't add the WHERE clause in both inner selects. – Brendon Smith Aug 05 '21 at 19:23

MS Access SQL SELECT TOP QUERY

1 Answers1