Solved

TOP x sql

Posted on 2008-10-08
6
184 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 143

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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help on t-sql 2012 10 56
Help Required 3 108
Need help constructing a conditional update query 16 69
MYSQL responding very slow 3 21
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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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