Return Current Price based on Quantity (1 Viewer)

raziel3

Registered User.
Local time
Today, 13:48
Joined
Oct 5, 2017
Messages
311
Need some help getting the Current Price for an Item based on Quantity purchased.

This is my Item Latest Data Query:

Code:
SELECT Product.UPC, Product.PNAME, ITEMCOST.CPU, ITEMPRICE.PPU, ITEMPRICE.LQTY
FROM (Product LEFT JOIN ITEMCOST ON Product.UPC = ITEMCOST.UPC) LEFT JOIN ITEMPRICE ON Product.UPC = ITEMPRICE.UPC;

These are the results:

1726846044769.png



What I have so far:

Code:
Public Function ITEMSPECIAL(iUPC As String, iQTY As Double) As Double

    With CurrentDb.OpenRecordset("ITEMDETAIL", dbOpenSnapshot)
        .FindFirst (inpUPC)
        If Not (.BOF And .EOF) Then
            ITEMSPECIAL = !PPU.Value
        End If
    End With

End Function

I am having problems passing the Quantity parameter. But then I thought about if someone bought 11 Items that would mean that the query would have to give me results like this:

1726846025411.png



Thanks in advance everyone.
 

Attachments

  • 1726846038928.png
    1726846038928.png
    9.6 KB · Views: 38
Last edited:
I don't follow. A lot of things, but mostly the premise:

Need some help getting the Current Price for an Item based on Quantity purchased.

'Current' is a term related to time, but your definition of 'current' is related to quantity? Very odd. So if someone bought 100 items on 4/4/2000 and 5 items on 3/3/2024 the 'current' price is based on the purchase in 2000?

2nd, I don't understand where VBA fits in to all of this. Seems like this can be handled with a simple query.

3rd, I don't understand where you are coming from and where you are going. You showed us a lot of sample data of things you have tried and didn't work, so that's not really helpful. Here's what would be immensely helpful---2 sets of data:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed in the data from A.

Again, sample data, not explanations. Show us what you are starting with and what you hope to end up with.
 
I don't follow. A lot of things, but mostly the premise:



'Current' is a term related to time, but your definition of 'current' is related to quantity? Very odd. So if someone bought 100 items on 4/4/2000 and 5 items on 3/3/2024 the 'current' price is based on the purchase in 2000?

2nd, I don't understand where VBA fits in to all of this. Seems like this can be handled with a simple query.

3rd, I don't understand where you are coming from and where you are going. You showed us a lot of sample data of things you have tried and didn't work, so that's not really helpful. Here's what would be immensely helpful---2 sets of data:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed in the data from A.

Again, sample data, not explanations. Show us what you are starting with and what you hope to end up with.

The ITEMDETAILS Query gets the most recent Price and last Cost from these two queries

Latest Price
Code:
SELECT d.UPC, d.QUANTITY AS LQTY, t.LDATE, d.PRICE AS PPU, d.VAT AS LVAT
FROM Prices AS d INNER JOIN (SELECT UPC, QUANTITY, MAX(EFFDATE) AS LDATE FROM Prices GROUP BY UPC, QUANTITY)  AS t ON (d.QUANTITY = t.QUANTITY) AND (d.EFFDATE = t.LDATE) AND (d.UPC = t.UPC)
ORDER BY d.UPC, d.QUANTITY, t.LDATE DESC;

Last Cost
Code:
SELECT LCP.UPC, LCP.LDate, LCP.LQTY AS QTY, LCP.LCost AS CP, LCP.LZR, IIf([LZR]=0,Round(([LCOST]*(1+[RATE]))/[LQTY],2),Round([CP]/[QTY],2)) AS CPU
FROM LCP, qryTAX
WHERE (((LCP.LDate) Between [qryTAX].[STARTDATE] And [qryTAX].[ENDDATE]) AND ((qryTAX.TAXTYPE)="VAT"))
ORDER BY LCP.UPC;

I was going to use the vba on the Sales Form.

User enters the UPC and Quantity and the Related Price is autofilled.

The database is split so I will try to get some data in it and upload a sample.
 
Again, no explanation, data. A & B as described above.
 
I've attached a sample db.

The form subfrmSales is where data entry will be done.

Double-clicking the Quantity field will open up a popup to change the Quantity. This is where I need the help.

On changing the Quantity, depending on the amount, say 11, it should fill 3 records to subfrmSales like this:

1726851135265.png


This is what I'm thinking. I am sure there must be a simpler way to do it.

Other Forms in db:
frmProMaint - Used to maintain Item Info, Price etc.
frmPurHistory - Used to View Item Purchases
 

Attachments

Your code tries to take you to a new record when you have a pending edit in the original record. Don't try to move to a new record.

But I don't see the sense in opening a popup to control the value of quantity when it would simpler to just let the user edit the field directly. You force your user to have to double click the field, type a number (which they have to do anyway), and then click again in the popup. This adds friction to a very simple operation without adding value.
 
If you want to support multiple prices depending on quantity, you need the quantity range and you store the unit price.
1, 1 = $5
2, 10 = $4
11,99 = $3

So you find the range where the low value is <= the quantity and the high value is >= the quantity. Then you multiply the unit price * quantity. AND you store the unit price. Otherwise, you have to include sale date in the price lookup.

Keep in mind that you need validation code when you are adding pricing data to ensure that there is no overlap. i.e. you can't have 1, 3 and 2, 5 as a range because 2 falls in both ranges.
 
Last edited:
But I don't see the sense in opening a popup to control the value of quantity when it would simpler to just let the user edit the field directly. You force your user to have to double click the field
I was just thinking (just brainstorming here) that for 11 bottles of 7312040017010, if the user enters directly in the text box I can undo the record entry and open the popup.

There the actual calculation will be done based on the Price Ranges

So the available quantity ranges for Item 7312040017010 is 4, 2, 1
Record 1: Int(11/4)= 2 @ $670.00
Record 2: Int(11-8)/ 2) = 1 @$340.00
Record 3: 11-8-2 = 1 @$175.00

On clicking "CHANGE" on the pop-up, 3 records are posted to subfrmSales 🤷‍♂️
 
Last edited:
On changing the Quantity, depending on the amount, say 11, it should fill 3 records to subfrmSales like this:
probably you'll get lots of complain from customer if there are Same items but different prices?
you can't argue with them since that is not a normal practice even on supermarkets.

firstly there must be a business rule as to how you change the price of your inventory.
then you can only show 1 item with uniform price and consolidated quantity.
 
probably you'll get lots of complain from customer if there are Same items but different prices?
you can't argue with them since that is not a normal practice even on supermarkets.

firstly there must be a business rule as to how you change the price of your inventory.
then you can only show 1 item with uniform price and consolidated quantity.
This is not the regular pricing system. I am trying to develop a method for specials/deals on certain items.
 
This is not the regular pricing system. I am trying to develop a method for specials/deals on certain items.
Tell us more about your line of business and how these specials would work. We can speculate all day long, but you can fill us in.
 
Tell us more about your line of business and how these specials would work. We can speculate all day long, but you can fill us in.
Post #8 has an example of special pricing on Item 7312040017010

Post #1 has the Item Price (PPU) by Quantity for Item 7312040017010
 
You don't have any date range associated with the special pricing. How do you decide which price to offer? What is the price if they want 3?
 
You don't have any date range associated with the special pricing. How do you decide which price to offer? What is the price if they want 3?
In the attached db, the query ITEMDETAIL is the current price for all Items based on Quantity. It uses this query to get the latest/most recent price based on Max(EFFDATE)

Code:
SELECT d.UPC, d.QUANTITY AS LQTY, t.LDATE, d.PRICE AS PPU, d.VAT AS LVAT
FROM Prices AS d INNER JOIN (SELECT UPC, QUANTITY, MAX(EFFDATE) AS LDATE FROM Prices GROUP BY UPC, QUANTITY)  AS t ON (d.QUANTITY = t.QUANTITY) AND (d.EFFDATE = t.LDATE) AND (d.UPC = t.UPC)
ORDER BY d.UPC, d.QUANTITY, t.LDATE DESC;

I am taking your advice of adding an ENDDATE field to my Prices table.

The Price for 3 would be:
2 @ $340.00
1 @ $175.00
 
The Price for 3 would be:
Who sets the prices? This will be difficult to code and more difficult to audit. The range method is very simple and the correct price can be obtained using just a query. If you have to use multiple records, you need to use VBA.
 
This makes absolutely no sense.
Why would the average price for three be more average price for two.
Curious, how would you price 3 units?

I'm thinking if the customer can rationalize that 3 units would cost them more per unit than if they just purchase one more then they opt to purchase 4 units.

But I understand that the pricing structure should be like:

1 - $175 per unit
2 - $170 per unit
3 or more - $168 per unit.

This would be easier. But I don't want to sell 11 units for $168. It would be 10 units at $170 and 1 unit at $175. This I think would reduce bulk buying.
 
Last edited:
This would be easier. But I don't want to sell 11 units for $168. It would be 10 units at $170 and 1 unit at $175. This I think would reduce bulk buying.
you need a uniform price (+ markup, for profit),at least that's is what i know supermarkets/dept store does?
those 3 different prices are for internal to the company only (the accountants in charge will take care of that). and if you have
one (accountant) better seek the advice as to how they deal with this pricing.
 
1 - $175 per unit
2 - $170 per unit
3 or more - $168 per unit.

This would be easier. But I don't want to sell 11 units for $168. It would be 10 units at $170 and 1 unit at $175. This I think would reduce bulk buying.
I don't think you would be successful with this type of thinking, and very awkward to program.
If someone wants 5, 11 or 100 or a 1000 you should have a uniform price structure.

The fact you apparently don't want people bulk buying, would tend to indicate you shouldn't be offering any sort of quantity discount.
It's called a Quantity Discount for a reason...
 

Users who are viewing this thread

Back
Top Bottom