Is there a way to simplify this code and drive it from a table

[Joshlindsay]

Price calculation loop driven from table. Is this possible?

I've written a chunk of code which calculates an average price based upon a quantity - I'm pretty stoked with it being a complete novice.

Basically the first 50 items are charged at a certain price, the next 50 at a certain price, the next 100 and a certain price all the way to to 5000 with different quantity breaks.

This will be being used as part of an estimating system for a printing company.

Ideally I would like the rates and quantity breaks to be driven from a table so additions and changes could be made without touching the code.
However that won't work with each quantity being hard coded as I've done below.

Dim Remaining As Integer
Dim TotalQuantity As Integer
Dim TotalPrice As Currency
Dim AvgPrice As Currency

'Rates
rate50 = 2
rate100 = 1.9
rate150 = 1.8
rate200 = 1.7

'setQuantity
TotalQuantity = 140

'CalculatesPrice

If TotalQuantity < 51 Then
    TotalPrice = TotalQuantity * rate50
    AvgPrice = TotalPrice / TotalQuantity 
End If

If TotalQuantity > 51 And TotalQuantity < 101 Then
    Remaining = TotalQuantity - 50
    TotalPrice = (50 * rate50) + Remaining * rate100
    AvgPrice = TotalPrice / TotalQuantity 
End If

If TotalClicks > 101 And TotalClicks < 151 Then
    Remaining = TotalClicks - 100
    TotalPrice = (50 * rate50) + (50 * rate100) + Remaining * rate150
    AvgPrice = TotalPrice / TotalQuantity 
End If

'This continues on to 5000

MsgBox ("Total price for " & TotalQuantity & " items is $" & TotalPrice & ". The average price is $" & AvgPrice)

The above code has been simplified for posting as it is quite long and just repeats.

Could it be achieved with a super clever loop?

If you had any tips or advise on where I could start looking to achieve my idea scenario would be awesome.

Thanks

 

[PNGBill]

It is normal to refer to a table for your variables, in code.
I assume you are effecting all products qty price the same.
Have a table with fields for (eg) QuoteQty and RatioQty plus Primary Key of course.

Consider Select Case to handle the calculation.

 

[Joshlindsay]

Thanks for the reply.

This code will calculate a rate at which the printing is charged at (avgclick would be the price per sheet printed) based upon the quantity. The rest of our products are simple per 1000 rates.

I've got a table setup with ID, MinimumQuantity and Rate which has all my price breaks stored in it however I'm not sure how to dynamically get that running into the table.

Would it be possible to code a vba solution where it would loop through updating a running total until it reached the valid price break then stopped?
That would remove the need to have the rates coded into the script.

I'll have a look into select case.
It looks much better than how I've done it.

 

[jdraw]

As Bill said, you would normally have the "variables" in your tables.
You could have a function(script) to do a calculation(s) based on some parameters, and call that function from a query.

Please show us your table layout.

how to dynamically get that running into the table.

is a little confusing. You would get some dynamic calculations via a script working with data stored in your table.

There is material/tutorial on functions at
http://www.functionx.com/vbaccess2003/Lesson05.htm

 

[PNGBill]

If you could attach a sample of your database with non sensitive data, it would help.

Often the vba code would start with an SQL to select the data that relates to the record being reviewed.

Then the data is processed.
This may require more variables being populated - more sqls.

Why I suggested Select Case rather then a series of If Then is because when you have an Option ie 5 alternatives ways to calculate a price depending on the qty then Select Case does it faster.

If you need to review each result and do something else you could use If Then or a combination of both.

The starting point with your solution is to review how your data is stored and arranged.
Your code, so far posted, is quite limited in that is doesn't indicate the stored raw data.
Specific advice, at this stage, could be misleading.

 

[Joshlindsay]

Thank you both for your help so far.
It is very much appreciated.

Attached are some files for you.

They way the system is currently working is it's taking a bunch of lookups from combo boxes in a form and comes back with a average click price which is multiplied by the number of sheets.

We still haven't figured out how this data would be saved. I'm thinking 2 SQL statements to insert into 2 tables (tblEstimateDetails and tblEstPrinting) at the end of the VBA.

I'm sorry if this is confusing for you to operate.
If you click on the ID number of the estimates on the Main Menu then click on the Add Printing button you'll be taken to the printing entry form.

My goal for this is for the rates and quantity breakdown to be a lookup from the tblEstPrintingClickPrice table for the VBA calculation so that if a new record is added into that table it automatically takes that into account for new estimates without needing to hard code.

I can almost see it working like below

runningquantity = quantity
While quantity is less than minumumquantity and more than maximumquantity then
Add rate * maximumquantity to runningtotal
runningquantity = maximumquantity - quantity
Loop

Then when quantity is between minumumquantity and maximumquantity
Add rate * runningquantity to runningtotal
AveragePrice = runningtotal / quantity


Thanks for the link. I'll have a read shortly.

 

[PNGBill]

I get an error message when attempting to open the zip file.

 

[Joshlindsay]

Bugger. Try this.
I've zipped with another piece of software and uploaded with Chrome rather than FF.

Cheers,

 

[PNGBill]

Opens now.
I am going out for a walk on the beach.
Brilliant sunny day here but Freezing cold. 14deg C. Will have a look at the database in a few hours.

 

[Joshlindsay]

A walk on the beach. That sounds nice.
Are you in NZ at the moment? I'm in Auckland which Google is saying is 10deg C. Cold but very sunny.

Thanks for your help Bill.

 

[Simon_MT]

I don't understand that the price is scalular - banded. If you order 5000 you should get the unit based on 5000 not 50 then 100 then 150 then 200 etc.

Simon

 

[Joshlindsay]

Thanks for your reply Simon.
It's the fairest way we've found to calculate that price.
It's not the price of an actual product but a component of one.

 

[PNGBill]

A walk on the beach. That sounds nice.
Are you in NZ at the moment? I'm in Auckland which Google is saying is 10deg C. Cold but very sunny.

Thanks for your help Bill.

In Raglan. Cloudless day yesterday. Overcast and rain today

 

[Simon_MT]

Surely, yjr Customer needs to to get the benefit of bulk buying. If it is printing production the major cost is the the set-up and the greater the size of the run then the unit cost is significantly less.

Last time I was in NZ I drove from Auckland to New Plymouth I think throughthe Awakino Gorge. It was the first time I travelled this way in daylight and I was so impressed with west Coast. I then followeed Highway 30 to Whakatane, even picked up a hitchhiker at the Waitara turn going to Tauranga. Dropped him off at Rotorua, you forget how beautiful the Rotorua lakes are.

Simon

 

[Joshlindsay]

Surely, yjr Customer needs to to get the benefit of bulk buying. If it is printing production the major cost is the the set-up and the greater the size of the run then the unit cost is significantly less.

What we're calculating is the impressions not printed items.
It means that if someone gets a quote for 20 impressions one day, 250 impressions the next and 2200 impressions the prices are all calculated the same way rather than having a set price for the quantity break.
It also means that there is a small discount between say 2000 and 2900 without manually setting it.

The prices I've loaded are examples. The actual prices are much less.

 

[Joshlindsay]

In Raglan. Cloudless day yesterday. Overcast and rain today

Same in Auckland. At least the weekend was nice.

 

[PNGBill]

..
Last time I was in NZ I drove from Auckland to New Plymouth I think throughthe Awakino Gorge. It was the first time I travelled this way in daylight and I was so impressed with west Coast. I then followeed Highway 30 to Whakatane, even picked up a hitchhiker at the Waitara turn going to Tauranga. Dropped him off at Rotorua, you forget how beautiful the Rotorua lakes are.
Simon

Great country. Roads are quite good

 

[PNGBill]

Some observations...
tblEstimateDetails doesn't need ProductName. You can query this from tblEstProducts. Units may also be able to be queried.

Consider changing Primary Key names to tblEstimatesID, tblEstDetID, tblEstProdID etc. Repeated use of ID could be very confusing when using fields in queries and code.

Better to use double names eg SalePrice, CurrentPrice rather then Price.
SaleUnits, rather then Units.
Quantity ? EstQuantity.
The idea is to make the word mean more eg Price ?? QuotePrice, AvgPrice, LastPrice all mean more and avoid using a Reserved Word.

Description is a Reserved Word - tblEstPrintingClickPrice

What is the purpose of tblEstQuantity and tblEstPrintingClickPrice ?

What is ItemName in tblEstPrinting ?

 

[Joshlindsay]

Some observations...
tblEstimateDetails doesn't need ProductName. You can query this from tblEstProducts. Units may also be able to be queried.

Consider changing Primary Key names to tblEstimatesID, tblEstDetID, tblEstProdID etc. Repeated use of ID could be very confusing when using fields in queries and code.

Better to use double names eg SalePrice, CurrentPrice rather then Price.
SaleUnits, rather then Units.
Quantity ? EstQuantity.
The idea is to make the word mean more eg Price ?? QuotePrice, AvgPrice, LastPrice all mean more and avoid using a Reserved Word.

Description is a Reserved Word - tblEstPrintingClickPrice

What is the purpose of tblEstQuantity and tblEstPrintingClickPrice ?

What is ItemName in tblEstPrinting ?

Thanks Bill.

ItemName (could also be names ProductName) in tblEstPrinting is the name for that printed item as each estimate can have more than 1 printed item (letter and brochure for example) so they need to be differentiated.
That is the reason why I've got ProductName on the tblEstimateDetails form.

I'll definitely make the changes to the names as you've explained.
We've kind of been learning while building this so parts have been added on.

tblEstQuantity will be the quantities for each estimate. An estimate can have multiple quantities.
tblEstClickPrice is the lookup table I'd like to use for the VBA calculation rather than the set fields (rate50, rate100 etc)

Since I uploaded the database I've got the frmEstPrintingSpecs inserting into both tblEstimateDetails and tblEstPrinting but only for the selected quantity.
Will need to build the loop to insert for each quantity.

 

[PNGBill]

I noticed another field name that is a reserved word - Type.