Setting a fixed price for different quantities of product

[raziel3]

I'm in a little bind here. I have a Price table setup

EFFDATE	UPC (this is numeric, text field is shown)	QUANTITY	PRICE
1/1/20	BEER	1	$10.00
1/1/20	BEER	12	$85.00
1/1/20	BEER	24	$170.00
1/1/20	Cookies	1	$2.00

I'm using this function to get the current price

Public Function GetPrice(inpUPC As Double, inpQuantity As Double, inpDate As Date) As Double

On Error Resume Next

Dim strPrice As Double
Dim strMaxDate As Date

inpDate = Format(inpDate, "m/d/yyyy")

strMaxDate = DMax("EFFDATE", "Prices", "UPC= " & inpUPC & " AND EFFDATE<= # " & inpDate & " #")

strPrice = DLookup("Price", "Prices", "UPC= " & inpUPC & " And [EFFDATE] = #" & strMaxDate & "# And Quantity= " & inpQuantity & " ")

GetPrice = strPrice

End Function

On my Sales form (a query is the record source), the user enters the Sales Date, UPC, Quantity, and the Price field is automatically filled.

The problem I'm having is, I have a field called Extended that gives the total cost by Quantity * Price so if the customer buys 2 Cookies the Total changes to $4.00 but I don't want it to total the amount when 12 or 24 Beer is bought. The Price for that quantity of Beer is already set. As it is the Extended price for 12 Beer is coming up to $1,020. Any advice on how to get around that?

 

[theDBguy]

Hi. I am not sure I understand your question/issue; but if you have a qty range, then it might easier to include that range in your table as min_qty and max_qty.

Just a thought...

 

[raziel3]

Hi. I am not sure I understand your question/issue; but if you have a qty range, then it might easier to include that range in your table as min_qty and max_qty.

Just a thought...

The Extended price for 12 Cookies would be 12*2 = $24.00. There is no set price for 12 cookies

The Extended price for 12 Beer would be $85.00. The price for 12 Beer is set so there is no need to do the calculation of Quantity*Price.

There are only a few products that have fixed prices on quantity. How do I bypass the Quantity*Price formula if I choose 12 or 24 Beer?

 

[CJ_London]

What if the order is for 18 bottles of beer?

this is a limited example but looks to me like what is missing is a pk to identify the record with the right price and you need to use a cascading combo - choose the upc, then choose the quantity to identify the price, then enter the actual quantity ordered in the chosen unit quantity I.e. 1 unit of 24 bottles.

or if this a quantity discount use the price per bottle - I.e. 8.50 for 12 or 24 bottles

 

[theDBguy]

The Extended price for 12 Cookies would be 12*2 = $24.00. There is no set price for 12 cookies

The Extended price for 12 Beer would be $85.00. The price for 12 Beer is set so there is no need to do the calculation of Quantity*Price.

There are only a few products that have fixed prices on quantity. How do I bypass the Quantity*Price formula if I choose 12 or 24 Beer?

Okay, here's what I might try. Going by the min/max range I mentioned earlier, I would create a price table with the following structure.

Item    MinQty    MaxQty    UnitPrice
BEER    1         11        $10.00
BEER    12        12        $7.084
BEER    13        23        $10.00
BEER    24        24        $7.084

Not saying this is the only way, but maybe worth a try.

 

[raziel3]

looks to me like what is missing is a pk to identify the record with the right price

I had a different UPC (PK) in the product table for 24 or 12 units of beer but I was trying to get away from using that since I am using a barcode scanner. I would have scanned one beer and change the quantity, I think changing the price for one beer may be the easiest way to go. The only problem is that $85 for 12 beers works out to be $7.08 for one but when working it back $7.08 * 12 works out to be $84.96. Another problem I need to deal with.

 

[Pat Hartman]

I would go for simple so you can always use the same logic for all situations. Use from/to quantites and list the price for 1 so you ALWAYS multiply quantity times unit price.

 

[arnelgp]

is the Quantity means the Item is in Pack (of 12 or 24), so even if it is
12 quantity when it is Loose it will be computed as 12 * 10 (for beer)?

if that is the case then, you need a different UPC for a Pack of 12/24 and
a Loose quantity so it will be straight computing.

on my experience, any slight variation in color, size, quantity is a
candidate for new IdentCode.

 

[raziel3]

Thanks everyone for the help. The 2 options, using a separate PK for 12 or 24 Beers or setting a discounted price once the quantity is equal to 12 or 24 are very good suggestions.

I think I will go with the discounted method, I will include a discount column in my Prices table. @CJ_London got me thinking that at some point the grocery may offer specials on other items like buy 3 get 1 free, so I will prepare for that.

 

[Pat Hartman]

The other thing is that a 12-pack is ONE item, not 12 and that is contributing to your problem. If for some reason you ever need to report 12 bottls of beer rather than 1 12-pack, you can keep a quantity field on the product record. So items sold as 1 have a quantity of 1. Items sold as "packs" have the quantity in the pack.

 

[Pat Hartman]

Every application I have built works one of two ways with "quantity" purchases. Either they make inventory items for "packs" and sell 1 12-pack OR they have a pricing table that includes quantity. Separate price records are created for each level. I.e. 1-11, 12-12, 13-99999

 

[raziel3]

I.e. 1-11, 12-12, 13-99999

What if they do a promo for 24?

This is really a tricky issue. In my OP I have a function to get me the current price base on the Max Date. I thought just bringing in a Quantity would be easy, just find the current price based on Date and Quantity but I have encountered the next problem, How to calculate for 2,3,4 or 5.

| EFFDATE  | PRICE  | QUANTITY | DISCOUNT | VAT |

| 1/1/2019 |  21.00  |    24.00|    18.00 |   0 |
| 1/1/2021 | 100.00  |   120.00|    20.00 |   0 |
| 1/1/2019 |   2.00  |     1.00|     0.00 |   0 |

From my table the function will not see any "5" so 0 is returned. I will need to adjust my code to include an If or Case statement for if the quantity is not 12 or 24 use the 1 price. But then if they have a special for 30 units?

I think that I may use the Discount column since the regular price will not have any discount,
-->If Discount = 0 use the normal price else look up the Quantity and Max Date.

 

[Pat Hartman]

What if they do a promo for 24?

They add a price record for 24-24. The price table also needs a BeginDate and ExpireDate. When you add a price that is not a limited time promotion, you add it with either a null ExpireDate or an arbitrary date such as 12/31/2050.

When you add the item to an order, you pick up the price from the pricing table that is valid for that quantity for today's date. Otherwise, pricing history for reporting gets complex.

 

[CJ_London]

for something like price changes you don't need an expiry date, just an effective from or begin date. Any end or expiry date should be covered in a separate table e.g. if product is no longer available this would be in the product table.

The exception would be with something like a time limited special offer/or discount where the offer is only available between certain dates and is outside of the normal pricing. It can get more complicated if the offer is only for one or more days of a week or for certain classes of customer (club card member/oap/etc). Many years ago I wrote a POS app for a customer who wanted to reduce prices (of fresh produce) close to closing time as better to sell for a smaller amount rather than incurring the costs of disposal - so this involved a time element as well.

If you are developing a POS system, you really need to understand what the business wants to do with pricing and how it can use pricing to sell more product, it's not just about recording sales transactions.

 

[Pat Hartman]

While only a start or end date is technically necessary, the selection query is simplified if both are present. Write queries using both table schemas and decide for yourself. In either case, the form that adds new pricing or changes existing pricing has to ensure no gaps or overlaps in the pricing records.

 

[raziel3]

This is what I came up with. I'm using my Discount column which is sort of like an "EndDate" field but instead of having to put an arbitrary end date I will look for products without discounts.

| EFFDATE  | PRICE  | QUANTITY | DISCOUNT | VAT |

| 1/1/2019 | 1.000   | 24.00 | 18.00 |   0 |
| 1/1/2021 | 0.833   |120.00| 20.00 |   0 |
| 1/1/2019 | 2.000   |  1.00  |  0.00  |   0 |

Public Function fncPrice(inpUPC As Double, inpQty As Double, inpDate As Date) As Double

Dim strIDate As String
Dim strPrice As String

strIDate = "#" & Format(inpDate, "m/d/yyyy") & "#"

If fncDiscount(inpUPC, inpQty, inpDate) = 0 Then
    strPrice = "SELECT TOP 1 Price FROM Prices WHERE [EFFDATE] <= " & strIDate & _
                " And [UPC] = " & inpUPC & _
                " And [QUANTITY] = 1" & _
                " ORDER BY [EFFDATE] ASC;"
Else
    strPrice = "SELECT TOP 1 Price FROM Prices WHERE [EFFDATE] <= " & strIDate & _
                " And [UPC] = " & inpUPC & _
                " And [QUANTITY] = " & inpQty & _
                " ORDER BY [EFFDATE] ASC;"
End If

    With CurrentDb.OpenRecordset(strPrice)
        If Not (.BOF And .EOF) Then
            fncPrice = .Fields(0)
        End If
    End With

End Function

 

[CJ_London]

should these be doubles?

inpUPC As Double, inpQty As Double

doubles can be difficult to = on as they are not precise. Normal practice for inpUPC would be long and inpQty as long or integer - unless inpQty can be 1.5 20.3 etc

Whilst prices are usually currency, but it's your app

 

[raziel3]

should these be doubles?

inpUPC As Double, inpQty As Double

doubles can be difficult to = on as they are not precise. Normal practice for inpUPC would be long and inpQty as long or integer - unless inpQty can be 1.5 20.3 etc

Whilst prices are usually currency, but it's your app

Well, I was now about to ask what data type my UPC field should be. I tried to change it to Long but I will lose data in my Product table. I was however able to change it to Short Text but had to declare it as Variant in my code. What would be the alternative to Variant Data type because I read that Variant takes up 16 bytes of memory.

 

[CJ_London]

in your original post you said it is numeric - I assume whole numbers, so not sure why you are saying you will lose data. Long are the normal type of field you use as PK and FK - and PK is typically an autonumber which is a long. Longs take 4 bytes. Strings 2 bytes + 2 bytes per character so a 5 digit code will take 12 bytes.

Also don't know why you need to use a variant in your code. Either way, in the context of memory usage it is fairly insignificant.

 

[raziel3]

When I changed the UPC field type to Long, the barcode on some of the products was left blank. I set it to Number (not Large Number) and Field Size = Long Integer. I don't know what happened.

I really messed up my table design in the beginning, now I have about 3000+ records and I have to go back and fix my Products table.