# Thread: query (loop function)

1. Registered User
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. Registered User
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. Registered User
Join Date
Jan 2004
Posts
15
There must be an easier way. or maybe I'm just putting too much into it.

4. Registered User
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.

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

6. Registered User
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.

7. Registered User
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. Registered User
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?

9. Registered User
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. Registered User
Join Date
Sep 2003
Location
T.O.
Posts
326
Not sure where you are getting your run time error.

11. Registered User
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 13:21.

12. Registered User
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.

13. Registered User
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. Registered User
Join Date
Jan 2004
Posts
15
or is there an easier way to do this?

15. Registered User
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
•