query top 3 ID with exclusion of the last 4

[blues66]

Good evening,

I'm building aspx pages.
In one of those I've three "datalists" populated by Access DB, called "news".
The first one I'm querying just the last (recent) ID, and it is OK.
In the second one, I want to query the Top 3 [ID], with the exception of the last one, and it is OK too.
In the third one, I'd like to have the TOP 3 [ID] with the exception of the last 4 ID (so I need to populate starting from the last fifth ID.

I'm not able to find the right query to populate this last datalist. Can somebody have solution idea to kindly advise me?

Thanks a lot and rgds

 

[blues66]

I've seen that I did the question in a bit complicated way.

I just need a query able to extract all the ID record in a table, with the exception of the last 4 records inserted.

Thanks a lot for the precious help

 

[honda882000]

Is using VBA an option? If so, you could use this function to get the number of records in the table:

Function num_records(table_name As String) As Double

    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT COUNT(*) AS cnt FROM " & table_name & ";")
    
    If (rs.BOF And rs.EOF) Then 'No recs found
        num_records = 0
    
    Else 'Return number of records
        num_records = rs!cnt

    End If
    
    'Clean:
    rs.Close
    Set rs = Nothing
    
End Function

And then use a procedure with this function embedded into the TOP clause like so:

Sub create_table()
    
    CurrentDb.Execute ("SELECT TOP " & num_records("Table1") - 4 & " id INTO new_table FROM Table1 ORDER BY id;")

End Sub

Would this work for you? Sorry, I cannot think of a way to do it with strictly SQL, but would really enjoy seeing it if it exists.

 

[blues66]

Thanks Honda,

I'm not using VBA but I solved anyway the problem with two queries :

q1 :

SELECT TOP 4 Table2.id, Table2.name
FROM Table2
ORDER BY Table2.id;



q2 :

SELECT Table2.id, Table2.name
FROM q1 RIGHT JOIN Table2 ON q1.id = Table2.id
WHERE (((q1.id) Is Null));

Maybe somebody could be interested on it.

Thanks again