• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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?
0
DCRAPACCESS
Asked:
DCRAPACCESS
  • 2
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now