Return Current Price based on Quantity

[raziel3]

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

This is my Item Latest Data Query:

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:

What I have so far:

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:

Thanks in advance everyone.

 

[plog]

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.

 

[raziel3]

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

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

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.

 

[plog]

Again, no explanation, data. A & B as described above.

 

[raziel3]

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:

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

 

[MarkK]

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.

 

[Pat Hartman]

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.

 

[raziel3]

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