Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2006
    Posts
    37

    Unanswered: limit query results based on table value

    Allright guys here is the problem I am having... hopefully someone can help me with this as I have been struggling with it for a while now.

    I need to create a query that only shows a limited number of responses. Typically this would be referred to as a Top N query where N is a number like 5, 25, 100, etc. In this case the value of N is static. However, I have a dynamic value that I require for N that could change daily. I would like to be able to pass this into a query (I know it takes VBA code) so that I do not have to adjust the query. The number N that I have is available from either the query where it is calculated or a table where it can be stored.

    I have written some VBA code that works for limiting the number of query results when a static number is input now I just need to find out how to make it read in the number I need (I am pretty confident in that I can place it in the SQL string once I know it is available to the VBA code).

    The code I have is as follows:

    Option Compare Database
    Option Explicit

    Sub qryTopNValues()
    On Error GoTo Err_BuildQry

    Dim strSQL As String
    Dim qdf As DAO.QueryDef
    Dim MyNumber As String


    strSQL = "SELECT TOP "

    MyNumber =

    strSQL = strSQL & MyNumber
    strSQL = strSQL & " tblKanbansAvailable.Item, tblItems.Group, tblKanbansAvailable.DateTriggered, tblKanbansAvailable.Autonumber"
    strSQL = strSQL & " FROM tblKanbansAvailable INNER JOIN tblItems ON tblKanbansAvailable.Item = tblItems.Item;"

    DoCmd.DeleteObject acQuery, "qryGroupAKanbansIssued"

    CurrentDb.CreateQueryDef "qryGroupAKanbansIssued", strSQL

    DoCmd.OpenQuery "qryGroupAKanbansIssued"

    Exit_BuildQry:
    Exit Sub

    Err_BuildQry:
    If Err.Number = 7874 Then
    Resume Next
    Else
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_BuildQry
    End If

    End Sub

    What I need to know how to do is set the value of MyNumber to a value located in a table named tblLevelAttainment and a field named Group1.

    Can anyone help me with the proper lookup code for this?

    Oh and currently running Access 2003
    Last edited by wrestleSBA; 02-23-06 at 17:35.

  2. #2
    Join Date
    Feb 2006
    Posts
    37

    limit query results based on dynamic value

    Allright guys here is the problem I am having... hopefully someone can help me with this as I have been struggling with it for a while now.

    I need to create a query that only shows a limited number of responses. Typically this would be referred to as a Top N query where N is a number like 5, 25, 100, etc. In this case the value of N is static. However, I have a dynamic value that I require for N that could change daily. I would like to be able to pass this into a query (I know it takes VBA code) so that I do not have to adjust the query. The number N that I have is available from either the query where it is calculated or a table where it can be stored.

    I have written some VBA code that works for limiting the number of query results when a static number is input now I just need to find out how to make it read in the number I need (I am pretty confident in that I can place it in the SQL string once I know it is available to the VBA code).

    The code I have is as follows:

    Option Compare Database
    Option Explicit

    Sub qryTopNValues()
    On Error GoTo Err_BuildQry

    Dim strSQL As String
    Dim qdf As DAO.QueryDef
    Dim MyNumber As String


    strSQL = "SELECT TOP "

    MyNumber =

    strSQL = strSQL & MyNumber
    strSQL = strSQL & " tblKanbansAvailable.Item, tblItems.Group, tblKanbansAvailable.DateTriggered, tblKanbansAvailable.Autonumber"
    strSQL = strSQL & " FROM tblKanbansAvailable INNER JOIN tblItems ON tblKanbansAvailable.Item = tblItems.Item;"

    DoCmd.DeleteObject acQuery, "qryGroupAKanbansIssued"

    CurrentDb.CreateQueryDef "qryGroupAKanbansIssued", strSQL

    DoCmd.OpenQuery "qryGroupAKanbansIssued"

    Exit_BuildQry:
    Exit Sub

    Err_BuildQry:
    If Err.Number = 7874 Then
    Resume Next
    Else
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_BuildQry
    End If

    End Sub

    What I need to know how to do is set the value of MyNumber to a value located in a table named tblLevelAttainment and a field named Group1.

    Can anyone help me with the proper lookup code for this?

    Oh and currently running Access 2003

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    MyNumber = DLookup("Group1","[tblLevelAttainment]","SomeCondition=True")

    Next question ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2006
    Posts
    37
    M Owen,

    Thanks for the reply but I can't seem to get it to work (I tried this earlier).

    1) Do I need a condition listed?
    - the table set-up is as follows, if you can tell me how to set the condition I would be grateful.

    Group 1--Group2--Group3--Group4
    1 -- 3 -- 2 -- 4

    2) I have the code as follows:
    Option Compare Database
    Option Explicit

    Sub qryTopNValues()
    On Error GoTo Err_BuildQry

    Dim strSQL As String
    Dim qdf As DAO.QueryDef
    Dim MyNumber As String

    strSQL = "SELECT TOP "

    MyNumber = DLookup("Group1", "[tblLevelAttainment]")

    strSQL = strSQL & MyNumber
    strSQL = strSQL & " tblKanbansAvailable.[Item], tblItems.[Group], tblKanbansAvailable.[DateTriggered], tblKanbansAvailable.[Autonumber]"
    strSQL = strSQL & " FROM tblKanbansAvailable INNER JOIN tblItems ON tblKanbansAvailable.[Item] = tblItems.[Item];"

    DoCmd.DeleteObject acQuery, "qryGroupAKanbansIssued"

    CurrentDb.CreateQueryDef "qryGroupAKanbansIssued", strSQL

    DoCmd.OpenQuery "qryGroupAKanbansIssued"

    Exit_BuildQry:
    Exit Sub

    Err_BuildQry:
    If Err.Number = 7874 Then
    Resume Next
    Else
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_BuildQry
    End If

    End Sub


    But I get the following error:

    2001 - You canceled the previous operation

    From what I can figure this is because the VB code is causing the query to ask for a parameter input which is then being cancelled. this could be ebcause of the condition check but I don't know.

    Any ideas?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try DoCmd.Execute or DoCmd.RunSQL instead of your DAO calls. This is assuming you don't need the query after running this. Another possibility is to reference your dynamic value from the query and avoid this whole ordeal all-together:

    SELECT TOP DLOOKUP("yourField", "yourTable") * FROM yourTable
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Feb 2006
    Posts
    37
    Quote Originally Posted by Teddy
    Try DoCmd.Execute or DoCmd.RunSQL instead of your DAO calls. This is assuming you don't need the query after running this. Another possibility is to reference your dynamic value from the query and avoid this whole ordeal all-together:

    SELECT TOP DLOOKUP("yourField", "yourTable") * FROM yourTable
    I will need to pull the values from this query at a later time so I will likely need to keep the query.

    How would I reference the value from the query? This would certainly make thigns simpler as I wouldn't have to write new values to the table each time.

    Sorry for bugging but I am very new at the VB in Access and SQL in general (have been creating simpler databases for quite a while though).

  7. #7
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Is this a php, VB, or SQL question?

    I'm assuming that it is just a SQL question:
    What I need to know how to do is set the value of MyNumber to a value located in a table named tblLevelAttainment and a field named Group1.
    If that is your question, post your table structure (to the correct forum...this is the php forum ). But you'll probably want to do something like:
    Code:
    SELECT TOP 1 Group1 
    FROM tblLevelAttainment
    ORDER BY createdDate DESC
    ...maybe???

  8. #8
    Join Date
    Feb 2006
    Posts
    37
    Is there anyone else out there who can help with this? Still looking for a solution or some help understanding those already offered.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by wrestleSBA
    Is there anyone else out there who can help with this? Still looking for a solution or some help understanding those already offered.
    Back to your DLookup ... Is there only 1 record in the tblLevelAttainment table? If not, you NEED to put a condition in the DLookup to return EXACTLY 1 record ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Feb 2006
    Posts
    37
    Everyone thanks for your help. I have gotten things working now by using the following code I found in a very old thread on this forum. Thanks to everyone for trying to help with this problem. It was greatly appreciated.

    Sub ScrollRS()
    Dim rs As DAO.Recordset ' not putting the DAO. in should eork but can do some very odd things
    Dim myCount As Integer
    Dim rsCount As Integer

    Set rs = currentdb.OpenRecordset("tableName")
    rs.MoveLast
    myCount = rs.RecordCount
    rs.MoveFirst
    For rsCount = 0 To myCount - 1
    Debug.Print rsCount
    Debug.Print rs.Fields(0)
    Debug.Print rs.Fields(1)
    rs.MoveNext
    Next rsCount

    'close recordset
    rs.Close
    End Sub

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    take a look at dlookup() in VBA help
    depending on what determines N, you might also want to check out other domain agregate functions like dcount(), dmax(), dmin() etc

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •