Solved

TOP x sql

Posted on 2008-10-08
6
183 Views
Last Modified: 2012-05-05
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
Comment
Question by:DCRAPACCESS
  • 2
  • 2
  • 2
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22666811
i don't think that is possible in ms access...

listening...
0
 

Author Comment

by:DCRAPACCESS
ID: 22666833
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666845
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666846
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666852
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
 

Author Comment

by:DCRAPACCESS
ID: 22667079
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question