TOP x sql

Hi Experts

I have this SQL and I'm not sure how to do it:

SELECT TOP DLookUp("Antal";"qry_Priotering_Antal";"") qry_forsinkelsesProcent.POR, qry_forsinkelsesProcent.ForsinkelsesProcent
FROM qry_forsinkelsesProcent
WHERE (((qry_forsinkelsesProcent.ForsinkelsesProcent)>10))
GROUP BY qry_forsinkelsesProcent.POR, qry_forsinkelsesProcent.ForsinkelsesProcent
ORDER BY qry_forsinkelsesProcent.ForsinkelsesProcent DESC;

As you can see I want the TOP n number of posts i the SQL showing above. But as you can see is the number of posts dynamic. Cant that be done?
DCRAPACCESSAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
i don't think that is possible in ms access...

listening...
0
 
DCRAPACCESSAuthor Commented:
I have got an idea my self.

I think I can do it in the VBA code. By getting the total result of the SQL and the make a loop with the DLookUp("Antal";"qry_Priotering_Antal";"") value and thereby get my result?
0
 
Pratima PharandeCommented:
is it working

SELECT TOP 2 qry_forsinkelsesProcent.POR, qry_forsinkelsesProcent.ForsinkelsesProcent
FROM qry_forsinkelsesProcent
WHERE (((qry_forsinkelsesProcent.ForsinkelsesProcent)>10))
GROUP BY qry_forsinkelsesProcent.POR, qry_forsinkelsesProcent.ForsinkelsesProcent
ORDER BY qry_forsinkelsesProcent.ForsinkelsesProcent DESC;
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, although that will query all the rows anyhow...
you might consider setting a TOP 200 for example, 200 being the really upper limit of what you expect to be used...
0
 
Pratima PharandeCommented:
SELECT TOP ( DLookUp("Antal";"qry_Priotering_Antal";"")) qry_forsinkelsesProcent.POR, qry_forsinkelsesProcent.ForsinkelsesProcent
FROM qry_forsinkelsesProcent
WHERE (((qry_forsinkelsesProcent.ForsinkelsesProcent)>10))
GROUP BY qry_forsinkelsesProcent.POR, qry_forsinkelsesProcent.ForsinkelsesProcent
ORDER BY qry_forsinkelsesProcent.ForsinkelsesProcent DESC;
0
 
DCRAPACCESSAuthor Commented:
Sorry pratima mcs, that does not do the trick. My own solution in vbs code worked:

Dim rstAll As DAO.Recordset
Dim intTop As Integer
Dim boolControl As Boolean
Dim inttest As Integer
Dim sql As String
inttest = 0
If IsNull(DLookup("[Antal]", "qry_Priotering_Antal")) = False Then
    intTop = DLookup("[Antal]", "qry_Priotering_Antal")
    Set rstAll = CurrentDb.OpenRecordset("SELECT * FROM qry_Priotering_Alle")
    If rstAll.EOF = False Then
        If rstAll.RecordCount > intTop Then
            rstAll.MoveFirst
            For i = 0 To intTop - 1
                sql = "UPDATE tbl_RapportUdskrivningsliste_Log SET Prioritering = 1 WHERE tbl_RapportUdskrivningsliste_Log.POR ='" & rstAll!POR & "'"
                CurrentDb.Execute sql
                rstAll.MoveNext
            Next i
        End If
    End If
End If
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.