Need help creating a discount calculator

[RossWindows]

Hi all, I'm creating a discount calculator to easily find what price to adjust the items to.

The discount is that if the customer spends $300 or more of merchandise, they will receive $100 off their order.

So if the merch equals 400, the merch total after the discount would be $300. Sounds straight forward right? But accounting wants that $100 to be calculated into each item price so I have to find the percentage of the discount (300/400 = 75%) Then I have to apply 75% to each item price.

So if the items were originally:
Item 1 = $200
Item 2 = $200

Then the new prices would be:
Item 1 = $150 ($200 x .75)
Item 2 = $150

There could be an infinate number of items to calculate so what I have so far is a form that you enter the old merch amount and a text box to type the price of one item, then you click a button that adds the item price to a subform to be calculated after all the items have been entered.

My problem is that with this method, I have to use a table to hold the items until the new prices are calculated, but this table is going to keep growing unless it is cleared out.
I attached a sample, so if anyone wants to see if there is a cleaner way to do this, please let me know.
I'm using a spreadsheet right now that I threw together in 5 minutes. I've attached that as well so you can a working example of the calculations.

 

[dkinley]

Reduce Bloating by Seperate Database

If you are after a bit of strategy to prevent bloating of your database due to the infinite item calculation; could you create a seperate database and link those two together?

The implementation would be the seperate database (merch_temp.mdbx) would simple be a holding tank for your temporary tables to be constructed and tore down with adding the bloat to your main database.

Your main database would create a temp table there, fill it with data, perform the calculations, then query the updated data into your database and finally delete the temporary table.

-dK

 

[John Big Booty]

You should be able to do this calculation on the fly, using

(Line Price)-((Discount Amount)/(Invoice Total)*(Line Price))

[I]
(Discount Amount) = Discount amount in dollars
(Line Price) = Your undiscounted total line price
(Invoice Total) = Your undiscounted invoice total[/I]

You can then do do a logical test (after each item is added to your invoice/order) to determine if your client is eligible for the discount or not, and then either apply the discount or not as the case may be.

 

[John Big Booty]

Here's a sample DB Hope it's headed in the right direction.

 

[RossWindows]

Oh wow, you are fast!

I wish this company wuold spend the money for something newer than ac'97

I'll play around with your sample a bit and I'll come back and let you know how it works out.