Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    69

    Unanswered: Loop function in Query

    Hi,

    I have a query where i get results as:

    QTY Description Price

    1 Type 1 10
    3 Type 6 11
    2 Type 3 25


    What i need is another query based on this one where i have the same results but product per product and not totals by same product:

    QTY Description Price

    1 Type 1 10
    1 Type 6 11
    1 Type 6 11
    1 Type 6 11
    1 Type 3 25
    1 Type 3 25


    Should i do this with a vb script and can i then generate a query straight from script ?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    This is one of those times when you want a tally table. Ya got one right?

    Just a table, perhaps called numbers, one column, perhaps called number. Guess what you populate it it with?

    Then:
    Code:
    SELECT "1" AS QTY, Description, Price
    FROM numbers INNER JOIN MyTable ON numbers.number BETWEEN 1 AND MyTable.QTY
    HTH

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You say integers-table I say tally-table

    Letttttttttttttttttttt's call the whole thing off

  5. #5
    Join Date
    Sep 2003
    Posts
    69
    still have some questions:

    1. Am i wrong or both you functions generate tables, but what i wanted was to generate a query i can export to xls and mail. Maybe it could work with a table but i have to write more script to delete it after is mailed.


    2. Your script you wrote , i can add it to a button in my "order" form , right?


    **************************
    The purpose is : i use the program to make orders (sale). On anther machine they have to put all the lines in the system but if there is a line with qty 3 they will create 3 separate lines instead of one. I try to automatise this process so maybe from my form i can directly mail an xls (or else) to their mail and they just copy paste it in their table.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - forget about Excel and Outlook for now - let's walk before we run
    Quote Originally Posted by west
    still have some questions:

    1. Am i wrong or both you functions generate tables, but what i wanted was to generate a query i can export to xls and mail.
    You are wrong - I provided a query in SQL (try googling if you don't know what this is). It requires that you have an additional table in your database - the numbers\ tally\ integer table. I described what it whould look like in my post, Rudy linked to post about integers tables for mySQL (same principle).

    Quote Originally Posted by west
    2. Your script you wrote , i can add it to a button in my "order" form , right?
    Indirectly yes. Again, one step at a time. Make the table, test the query, confirm the results are correct then we move on.

  7. #7
    Join Date
    Sep 2003
    Posts
    69
    ok let's walk before i run

    i created a tally table called numbers with one field called number
    i populated this table with numbers from 1 to 20 (incremental ) If it works i can add extra numbers.


    In my form where i create orders i added an extra buttot with following onclick() function:

    Private Sub Knop189_Click()
    SELECT "1" AS Aantal, PTitel, Vkprijs
    //-> Aantal stands for QTY PTitel for Description and VKprijs for Price
    FROM numbers INNER JOIN BESTELDETAILS ON numbers.number BETWEEN 1 AND BESTELDETAILS.Aantal
    End Sub


    BESTELDETAILS is a table where each new order line is added.


    And (as expected ) i get an syntax error
    I expect once that will be solved he would give a error on INNER (already got message)

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would expect it too

    You've mixed languages. You've posted some SQL code into a VBA (Visual Basic for Applications) window. I also notice you used '//' for comments - that doesn't work in VBA either - you need an apostrophe - '. No comments allowed in SQL at all BTW.

    Anyhoo - open the database window.
    Click on Queries.
    Click on Create New Query In Design View
    Get rid of the box.
    Click View -> SQL View
    Paste your SQL (SQL only - no VBA or comments)in the editor
    Run

    Let us know how you get on.

Posting Permissions

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