?
Solved

TOP x sql

Posted on 2008-10-08
6
Medium Priority
?
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 143

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month8 days, 15 hours left to enroll

764 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