Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: Run query several time with differant criteria with VB?

    I have query that require input when its ran. The user has to enter in a number from 1-25. What I want is to run the query 25 times and post the results in a report. If there is another way plz suggest it.

    Thanks

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not quite sure with the information you gave but one suggestion might be to have a table based on the form the user enters numbers into and link that table with your other table in the query rather than run the query 25 times. Maybe with some more info, I can give you a better suggestion as to what you want to accomplish.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2006
    Posts
    162
    Its only a query. All the data is already there, not adding any. The query has to have the criterie to run correctly. Instead off doing 25 queries and do a report from them. I need to run an array or a vb to run and save info to table then requery with the next number, save to table, etc to 25 then pull info to a report.

    I was trying to do it with vb. I'm not familiar with vb, but i can open it with vb, i need the string to fill in the dialog box with the number and just do that 25 times.

    So i need this in vb.

    open query
    enter number in dialog box
    save info to table

    requery
    enter number in dialog box
    save info to table

    etc...


    open query = DoCmd.OpenQuery "QUERY NAME"

    but i don't know the string for enter number in dialog box

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Still not sure what you're trying to do. Maybe someone else can understand better or if you send an mdb with the table and report. It seems like there's a very easy solution - I just don't quite understand. If you're looping through numbers against a table you could do something like this (this is ADO):
    Dim X as integer
    Dim strSQL as string
    Dim rs as ADODB.RecordSet
    Set rs = new ADODB.RecordSet
    For X = 1 to 25 'or whatever number could be a number retrieved from a form
    ' for strSQL, you can use the SQL statement from the query
    strSQL = "Select * from MyTable where MyNumber = " & X & ""
    rs.open strSQL,currentproject.connection,adopendynamic,adL ockOptimistic
    ' do something with rs (rs is the recordset of the query)
    rs.close
    set rs = nothing
    Next X

    But again, I'm not sure what it is your exactly trying to do. Maybe someone else understands a little better.

    or

    You can also simply have a button you click on the form which once it clicked, does a For X = 1 to 25, populates a text box on your form with X, runs the query, and next X.
    Last edited by pkstormy; 09-12-06 at 11:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2006
    Posts
    265
    Another way of doing this which might sound a little odd is to to create a table with your 25 records, dunp it into the query (no Join whatsover) and therefore you not have 25 diplications of the same record. The user entered the number via a dialogue bxx and in the query limit the records My25Records<=UserInput.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think you answered exactly what he's trying to do SimonMT. Thanks - I didn't quite understand it.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Sep 2006
    Posts
    265
    Normal queries are relational this query is NOT. Ever forgotten to link a table in a query and found rows and rows of duplicated records. This is the same principle an "improper query"

    We used to get transparencies or slides made of stock but the number of prduced varied. So the user enters the stock and the number of labels in a form called Originals Dialogue. The cutdown SQL looks like this:

    SELECT OriginalsQuery.[Orig Old Stock], [Slides Copies].[Slide Copy]
    FROM [Slides Copies], OriginalsQuery
    WHERE [Slides Copies].[Slide Copy])<=[Forms]![Originals Dialogue]![Copies];

    There is intentionally no JOIN

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yeah - I've also done this a few times when we exported data for evaluation. I just didn't quite understand that that's what he was wanting to do.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Sep 2006
    Posts
    162
    Not sure if that is what i need. I don't fully understand what you mean. But let me give you some data and what i'm trying to do.

    pos1 = 1 to 25
    pos2 = 1 to 100
    pos3 = 1 to 25
    points = 1 to infinity
    tag = Anything

    the 3 postions represent a grid that is divided up into groupings. First major group is pos 1, its a 5x5 grid with 10x10 grid within that has 5x5 grid within it.
    So the top left position is 1.1.1, the top right is 5.25.5, the bottom left is 21.91.21, and the bottom right would be 25.100.25.

    My goal is to pull the hightest points for each pos2. Then I want to add the top numbers for each pos2 per tag number and sort with pos1.

    Example output.

    Pos1 = 1
    28996 AAA
    7317 TK
    6436 CR
    6226 AA
    5858 CD

    Pos1 = 2
    27516 AAA
    17039 TK
    11857 K9
    7844 YEA
    5947 GDS

    The number is the sum of the max pos2.
    And the letters represent the tag its sumed up by.


    I can accomplish what I want but only one pos1 at a time by using the criteria input. I have a total of 4 queries to run this this, so i didnt' want to have to do 4x25 querys.
    Last edited by Jay59; 09-14-06 at 17:12.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Wow - still don't think I understand but I'm wondering if a crosstab query might be what you need. Sorry - I'm just not grasping the big picture here.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Jul 2005
    Posts
    39
    Do you mean something along the lines of:

    Private Sub multiple_run_query(n As Integer, pos1 As Integer)
    Dim n As Integer
    Dim counter As Integer
    'get user input n
    'start counter at 1
    'dowhile counter not equal to 26
    'run multiple_run_query where n is criteria and pos1 equal counter
    'increment counter
    End Sub

  12. #12
    Join Date
    Sep 2006
    Posts
    162
    i figured it out last night using sub reports.

Posting Permissions

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