Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2004
    Posts
    15

    Unanswered: query (loop function)

    I can't seem to think of the easiest solution for this. But here is my problem I have a table with (car) dealership information. I need the top six dealerships that perform the best but cost various ammounts to finanance. For instance:

    Dealership "A" sold 1500 units last month but he cost the most per month to finanance at 14.3 million dollars.

    Dealership "B" sold 1000 but only cost 10 million dollars to finance.

    Dealership "C" sold 800 and cost 9.3 mill to fincance.

    I have a total budget of 35 million to pick the top 6 dealerships out of.

    There is a quite a few dealerships to compare from. Say three hundred plus.

    I would need to pair delearship a or row 1 field "1" with "B", "C", "D", "E", and "F". (But that would but me over my budget asuming they are sorted descending by price.) So I would need to keep "A", "B", "C", and look for the next without going over my total budget by adding all the fields up in field "2" assuming field "2" is the price field.

    And if the best combination only had 2 dealerships then that would be ok but I can't go over 6 dealerships. I am a newbie to both databases and VBA. So any insight would be helpfull. Even if you just gave some ideas.

    Thanks!
    Ben

  2. #2
    Join Date
    Jan 2004
    Posts
    15
    I am thinking I'll have to store the results in a temp table with the combination information in one field. the total units sold in the second, and the total price in the last field. Then all I would have to do is sort that table descending by total units sold and top 6 it.

  3. #3
    Join Date
    Jan 2004
    Posts
    15
    There must be an easier way. or maybe I'm just putting too much into it.

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If you're simply looking for ideas: You may want to think about how you define profitability. Once you have that, write a query based on descending profitability, then ensure you are within your 35M$.

    You have your financing dollars in M$ so you could actually eyeball the query's results without developing anything too complicated.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Jan 2004
    Posts
    15
    okay well say there is 47,893 dealerships.

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Okay, there is 47,893 dealerships. I'm sorry if that frustrates you, I couldn't resist.

    All joking aside, it depends on how you define performance. If performance is strictly based on number of units sold, the approach is different than if it's units sold / amt to finance, etc. W/o knowing that, you don't know the pool to which you want to distribute your budget.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  7. #7
    Join Date
    Jan 2004
    Posts
    15
    Ok I see well here is the deal. Profits aren't as important in the top 6 of the type of report I want to run. Here is the deal we break up the year into 2 "quarters". For the first quarter x dealership sold a total of 1500 cars. dealership b 1200 etc. to project how many they will sell in all next quarter I need to use previous quarter information. Throughout the season I can redistribute the finances accordingly by running the report based on profits but if we switch to a fixed distribution for the whole season which dealership combination is going to provide the best performance based on units sold. the profits are pretty much fixed and we pretty much know how much profit each vehicle will make, because we already own the vehicles. we leased them out the customer returned it and we want to just sell the vehicles for pretty much full profit. so the ammount of vehicles sold is going to be more important. anyhow I don't want to get into to much detail. But that is my problem. I want to know what the best way to flowchart this out would be. If the a + b check to see if it goes over price then if not + c check to see if it goes over price if not + d when it goes over budget -d and add the total units sold and total price throw them into a temp table(or a make table) sort descending and top 6 it, then so be it but is there an easier way?

  8. #8
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Not sure if this is what you're looking for, if it's close, you'll need to decide on how you want your routine to report the dealer names back to you (table, Debug.Print), etc.

    -----------------------------------
    dim strDealerNames as string
    dim rst as New Adodb.Recordset
    dim dblCost as double ' Assuming Finance cost is in M$
    dblCost = 0

    rst.open "SELECT * FROM DEALERSHIPS ORDER BY AmtSold DESC;", currentproject.connection, 3,1 ' (3 - static, 1 - ReadOnly)

    do until rst.eof
    dblCost = rst("FinancedAmt") + dblCost
    if dblCost > 35 then Exit do
    strDealerNames = strDealerNames & rst("DealerName") & ";"
    rst.movenext
    loop

    msgbox strDealerNames

    --------------------------------
    Keep in mind a couple of things:
    (1) If your dealer selling the most units has a finance cost > 35M$, you won't get any names (hence my banter about profitability)
    (2) With the AmtSold ranking, you can run into a situation where the last dealer your routine picks sells 400 units, but so does the next dealer, not sure how you choose one 400 sell over another?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  9. #9
    Join Date
    Jan 2004
    Posts
    15
    (2) he sold 400 but the one of the dealers cost less to finance. Financing the dealer is not fixed on how many units they sold.

    get a runtime error trying to trap it.
    Run-time '-2147217900(80040e14)':
    syntax error in from clause.

  10. #10
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Not sure where you are getting your run time error.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  11. #11
    Join Date
    Jan 2004
    Posts
    15
    Function Dealer()


    Dim strNames As String
    Dim rst As New ADODB.Recordset
    Dim dblCost As Double ' Assuming Finance cost is in M$
    Set rst = New ADODB.Recordset
    dblCost = 0

    With rst

    .Open "SELECT * FROM season2", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    ' (3 - static, 1 - ReadOnly)
    .MoveFirst

    Do Until rst.EOF
    dblCost = rst("Price") + dblCost
    If dblCost > 35 Then Exit Do
    strNames = strNames & rst("Name") & ";"
    rst.MoveNext
    Loop
    .Close
    End With
    MsgBox strNames
    End Function

    Had to take of the order by desc and it will shoot back the first two records. But thats easy enough to just eye it. What I want it to do is if its more then 35 don't use the third record because it will take you over 35 go to the next record and that if its under 35 go to the next till you get to 6 full records. throw those six (or less) records into a table as one record with the names of each dealer, total price, and total units sold. so the first time it runs through it will go until 35 then. it will go to the next record and start with his combinations, etc.
    Last edited by br2325; 02-13-04 at 14:21.

  12. #12
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If you know that you will always have more than 6 to evaluate, you won't need to put in a recordcount check. The following is bad form because it doesn't evaluate the recordset's recordcount property to ensure the for loop will execute properly.

    The If dblCost > 35 Then Exit Do checks before the strDealer variable is updated so if it's more than 35, it will stop checking.

    Instead of the Do...Loop,

    For i = 1 to 6
    dblCost = rst("Price") + dblCost
    If dblCost > 35 Then Exit For
    strNames = strNames & rst("Name") & ";"
    rst.MoveNext
    next i

    The strNames update is where I would put a table update with another recordset if I had to store data in a table.

    The 1 to 6 ensures that if dblCost =< 35M$ for the duration of the for loop, you get your six entries.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  13. #13
    Join Date
    Jan 2004
    Posts
    15
    Ok this is what i came up with and its doing what I want for the first combination. I want to throw that combination into a table as the first record it will have the names of the dealers in one field, the total units sold, and the total amount of cost(price).
    then it would go i guess use the first record with the third instead of the second and go through combinations with that and so on and so forth till i have a table with all these combinations. I don't think there can be too many for access to handle but I know there will be a lot. is this asking too much? does this seem feasible?

    Function Dealer()


    Dim strNames As String
    Dim rst As New ADODB.Recordset
    Dim dblCost As Double ' Assuming Finance cost is in M$
    Dim dblTsnp As Double 'units sold
    Dim dblTotalCost As Double
    Dim dblTotalTSNP As Double
    Dim i As Integer


    Set rst = New ADODB.Recordset
    dblCost = 0

    With rst
    .Open "SELECT * FROM season2", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    ' (3 - static, 1 - ReadOnly)
    .MoveFirst
    Do While Not .EOF
    For i = 1 To 6
    dblTotalCost = rst("Price") + dblTotalCost
    dblTotalTSNP = rst("TSNP") + dblTotalTSNP
    If dblTotalCost > 35 Then Exit For
    strNames = strNames & rst("Name") & ";"
    dblTotalTSNP = dblTotalTSNP + dblTsnp
    rst.MoveNext
    Next i
    dblTotalCost = dblTotalCost - rst("price")
    dblTotalTSNP = dblTotalTSNP - rst("tsnp")
    rst.MoveNext
    Loop
    .Close

    End With
    MsgBox strNames
    End Function

  14. #14
    Join Date
    Jan 2004
    Posts
    15
    or is there an easier way to do this?

  15. #15
    Join Date
    Jan 2004
    Posts
    15
    By the way thanks for all the help! You have put this into perspective for me.

Posting Permissions

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