Solved Price History (1 Viewer)

[asteropi]

Hey guys
So I want to create invoicing history for the prices.
I have done the same for the addresses and it worked but for whatever reason it doesn't work with the prices

In the Order Details table I created a field called OrderPrice to indicate at what price that product was actually bought and actually create a historical event.
So if change the product price in the Product table, it won't change in the order table.

And in the order form, I used a Dlookup command to get the price from the relevant Order Query. But it just stays blank.
How should I go on about this?

 

[DHookom]

We can't see the DLookup() expression or the SQL of the Order Query. We need a lot more information to make a useful diagnosis.

 

[Gasman]

Perhaps show the code and the table values?

 

[The_Doc_Man]

In general terms, you would design a price history table with an item identifier, a date, and a price. Then your historical pricing lookup would look for the price of the item at the latest price-history date not later than the date on the invoice.

SELECT PH.historical-price FROM price-history-table AS PH
WHERE PH.item-code = selected-item-code AND PH.price-date = 
    ( SELECT MAX( PH1.price-date) FROM price-history-table AS PH1
      WHERE PH1.price-date <= invoice-date AND PH1.item-code = selected-item-code) ;

This is totally AIR-code and won't run. It is, among other things, incomplete with respect to selecting particular item codes (see further discussion below), but it gives you the idea. The above type of query is how you get the price as of the indicated date. The sub-query selects the latest date not more recent than the date of the invoice and uses the selected item code to make this selection return a single price value.

The trick will be getting the code that selects the item. To do THAT you will need an Invoice table that contains a date and a line-item table that contains the individual items bought under a particular invoice. So that of course will complicate the process by adding a query to join the invoice table with a line-item table that is a child of the invoice table. Then you could get the invoice date from the Invoice table and the items under that invoice from the line-item table, in effect joining the above to a JOIN query.

 

[gemma-the-husky]

Hey guys
So I want to create invoicing history for the prices.
I have done the same for the addresses and it worked but for whatever reason it doesn't work with the prices

In the Order Details table I created a field called OrderPrice to indicate at what price that product was actually bought and actually create a historical event.
So if change the product price in the Product table, it won't change in the order table.

And in the order form, I used a Dlookup command to get the price from the relevant Order Query. But it just stays blank.
How should I go on about this?

I would imagine either the order price in the order details table is blank, (maybe this wasn't in place at the time of some older orders), or the code to read the saved order price is incorrect.

 

[asteropi]

Perhaps show the code and the table values?

You want OrderT (this has the PriceOrder), OrderDetailsQ (This has the Price with an if command), OrderF, OrderDetailsF and change prices in ProductT. Please only play around with the first one product, as I don't want to redo everything

I want PriceOrder to find the values in the Price from OrderDetailsQ, but save it in OrderT.
Can it be done?

In general terms, you would design a price history table with an item identifier, a date, and a price. Then your historical pricing lookup would look for the price of the item at the latest price-history date not later than the date on the invoice.

SELECT PH.historical-price FROM price-history-table AS PH
WHERE PH.item-code = selected-item-code AND PH.price-date =
    ( SELECT MAX( PH1.price-date) FROM price-history-table AS PH1
      WHERE PH1.price-date <= invoice-date AND PH1.item-code = selected-item-code) ;

This is totally AIR-code and won't run. It is, among other things, incomplete with respect to selecting particular item codes (see further discussion below), but it gives you the idea. The above type of query is how you get the price as of the indicated date. The sub-query selects the latest date not more recent than the date of the invoice and uses the selected item code to make this selection return a single price value.

The trick will be getting the code that selects the item. To do THAT you will need an Invoice table that contains a date and a line-item table that contains the individual items bought under a particular invoice. So that of course will complicate the process by adding a query to join the invoice table with a line-item table that is a child of the invoice table. Then you could get the invoice date from the Invoice table and the items under that invoice from the line-item table, in effect joining the above to a JOIN query.

This seems more complicated than my idea

 

[Gasman]

Just copy and paste the code.
Don't make us go looking for it.
Also state where the code exists, it the pasted code does not make it obvious.

Also, you only ever upload a copy of your DB. If any changes are made here and uploaded, then you copy them into your DB.

 

[Gasman]

You do not even have the productID in the orders table?

 

[arnelgp]

check (if correct) the code on the "Add" button of your Order form.

 

[asteropi]

check (if correct) the code on the "Add" button of your Order form.

Wow this code is too advanced for me, so Thank you in advance

So 2 problems:
1) It should take the Price field from OrderDetailsQ, because that field works on an IF command to understand if it is a Wolesale customer or a Retail Customer. So it can't take just the RetailPrice. I think it's the IF command that messes it up tbh but there is no roundabout about this. There are 2 sets of prices

2)

Every time I tried adding an item, it adds the SKU first in the ProductName, then the next time in the Colour etc
My code also did that and I can't figure out why

 

[asteropi]

Just copy and paste the code.
Don't make us go looking for it.
Also state where the code exists, it the pasted code does not make it obvious.

Also, you only ever upload a copy of your DB. If any changes are made here and uploaded, then you copy them into your DB.

Private Sub AddBtn_Click()
If IsNull(ProductCmb) Then
DoCmd.GoToControl "ProductCmb"
ProductCmb.Dropdown
End If

DoCmd.GoToRecord , , acNewRec
ProductDetailsID = ProductCmb
ProductName = ProductCmb.Column(2)
Colour = ProductCmb.Column(3)
Size = ProductCmb.Column(4)
OrderPrice = Nz(Dlookup ("Price", "OrderDetailsQ", "ProductDetailsID=" & "ProductCmb"),0)
Me.Refresh


End Sub

Apologies, when I had problems before people were asking for the db.

This is the code I used

 

[asteropi]

Wow this code is too advanced for me, so Thank you in advance

So 2 problems:
1) It should take the Price field from OrderDetailsQ, because that field works on an IF command to understand if it is a Wolesale customer or a Retail Customer. So it can't take just the RetailPrice. I think it's the IF command that messes it up tbh but there is no roundabout about this. There are 2 sets of prices

2)
View attachment 116091

Every time I tried adding an item, it adds the SKU first in the ProductName, then the next time in the Colour etc
My code also did that and I can't figure out why

Scratch number 2. I solved it. It still doesn't take the proper price

 

[asteropi]

You do not even have the productID in the orders table?

View attachment 116088

There really isn't any need for the ProductID to be in the OrderDetailsT. This field is for the categories. Pex I have one product in 3 colours, they have the first initials in the SKU and I only have it to print my wholesale prices.

We only need the ProductDetailsID for this function.

 

[Gasman]

I really would think you would need a ProductID for each line in an Invoice?

Anyway......

Works for me (I think) ?

And I think I found the cause.
I always use Me. to indicate the controls. As I was amending your code, I was prompted for each control until I got to OrderPrice.
That was because although that is the source for that control, it was called Price.

Now I prefer to name my controls txtPrice or txtWhatever for textboxes, cmb for combos, so the controls and fields are always different.

So get the name correct, whichever you prefer and it will work.
Learn to walk your code, which is what I did, and that helped spot the issue.

Also I could not compile the DB, way too many errors, which you need to fix.

 

[asteropi]

I really would think you would need a ProductID for each line in an Invoice?

Anyway......

Works for me (I think) ?
View attachment 116092
And I think I found the cause.
I always use Me. to indicate the controls. As I was amending your code, I was prompted for each control until I got to OrderPrice.
That was because although that is the source for that control, it was called Price.

Now I prefer to name my controls txtPrice or txtWhatever for textboxes, cmb for combos, so the controls and fields are always different.

So get the name correct, whichever you prefer and it will work.
Learn to walk your code, which is what I did, and that helped spot the issue.

Also I could not compile the DB, way too many errors, which you need to fix.
View attachment 116093

Okay I corrected the name, and it half-works. It tries to frind the price, but it only takes the wholesale price. It doesn't change to the retail price
Please check order #4. It's retail. You will see it inputs the same price

Also I could not compile the DB, way too many errors, which you need to fix.

Also fixed.
This is the latest

 

[Gasman]

A DLookup() will find the first entry for the criteria?
You are just looking at the the first it finds. You would need to add crtieria of customer type as well?

Order 4 just happens to get the retail value £0.97? In my previous tests I was getting £0.45 for order 1

 

[asteropi]

A DLookup() will find the first entry for the criteria?
You are just looking at the the first it finds. You would need to add crtieria of customer type as well?

Order 4 just happens to get the retail value £0.97? In my previous tests I was getting £0.45 for order 1
View attachment 116096

View attachment 116097

Fo the same exact customer I get 0.45 in the retail (I should be getting 0.97
And 0 in the other products

Actually it seems it takes only the first price (wholesale) of the first product, that's it

 

[Gasman]

Actually it seems it takes only the first price (wholesale) of the first product, that's it

That is what it will do, always, even with extra criteria?
You do not have those last two in your query, so get 0 due to your NZ().
The correct criteria should be able to find the correct price.

I am not even sure how your system is meant to work, as adding a entry increases the number of records in OrderDeatailQ, which is compounding the issue? There should just be one price per customer type and product.

So I am not sure of your logic with that query, as you should only be beringing in what you need for that query?, then perhaps DISTINCT or DISTINCTROW might work, but I think you are bringing in too much clutter into that query.

Only ever bring in exactly what you need, no more, no less.

 

[asteropi]

That is what it will do, always, even with extra criteria?
You do not have those last two in your query, so get 0 due to your NZ().
The correct criteria should be able to find the correct price.

I am not even sure how your system is meant to work, as adding a entry increases the number of records in OrderDeatailQ, which is compounding the issue? There should just be one price per customer type and product.

View attachment 116099
View attachment 116100
So I am not sure of your logic with that query, as you should only be beringing in what you need for that query?, then perhaps DISTINCT or DISTINCTROW might work, but I think you are bringing in too much clutter into that query.

Only ever bring in exactly what you need, no more, no less.

OrderPrice is the the history price that was used in that order so it records the history and saves it in the OrderDetailT

You are referring to Price, which is indeed 1 price per product and type and that also exists in the OrderDetailsQ but comes from the ProductDetailsT

So basically in the query, it takes Price of a specific product from ProductDetailsT which is unique and saves it in OrderPrice in OrderT for history reasons. So if I want to change the Price later, it won't affect the OrderPrice that was made in that specific order

Did you make any changew to the Dlookup? Because I tried adding new criteria but it screws it up. Please send me your new code

 

[Gasman]

No I made no changes to the DLookUP, it still find just the first record for that product, which will always be the last one you used.
Make a mistake, and you will always pick up that mistake.

I would probably have a ProductpriceHistory table.
That would have at minimum
AutoNumber
ProductID
CustomerType
Price
DateFrom
DateTo

Then you just lookup what the price should be on a certain date. Perhaps have the discounts as well if they can change periodically.

Your DLookup() with what you have now should look like

Me.OrderPrice = Nz(DLookup("Price", "OrderDetailsQ", "ProductDetailsID=" & ProductCmb & " AND CustomerType = '" & Me.Parent.CustomerType & "'"), 0)

That still relies on previous values being correct, which they will not be at this moment.