Search results

Thanks very much for your continued assistance :) I'm happy to also store the ACVO in the products table, that makes sense. But can I ask why it's necessary to store the history in a products subtable? The plan was to store it in the deliveries table, which is linked to the purchase items...

Thank you for having a look. 1. I removed the suppliers table because it wasn't relevant to the sample database. 2. I don't understand what you mean by this as the purchase order items table is in the query. Unless you mean customer order items, if so, I deemed it not necessary to the query...

@gemma-the-husky have you had a chance to look at the sample database I attached? I appreciate any help you can provide with this :)

That's probably the reason DMax didn't work for me then and I had more success with DLast. I have attached a test version of the database with the same data we have been focusing on in this thread. I have included my version of the PreviousACP function, however, feel free to start it again if...

I'm trying DMax in the new function in the hope that it will work correctly with the loop code. I've made a start with it but as this is my first function I'm a bit stuck. This is what I have so far: Public Function PreviousACP() As Long Dim lngACP As Long lngACP =...

I initially tried DMax in the expression but DLast worked better. Not sure why it did but I stuck with it. The expression does exactly what I expect it to do to calculate the average cost but maybe it isn't working with the loop code. Due to my lack of coding knowledge/experience, I...

I hope I am interpreting your questions correctly in my answers here. 1. The average price is both stored and calculated each time. It is stored in the Average_Cost_Price field and calculated using the 'Previous ACP' and 'ACP' calculated fields in the query. The Average_Cost_Price field is...

There must be an issue somewhere. As shown in the below screenshot, I have taken away some of the data from the Average_Cost_Price column so the SQL only returns the rows where the Product_Price value is £7.52. This means the Average_Cost_Price value should be £7.52 because the average price of...

Thanks, I added the format out of desperation to get the code to work. I have taken the format away but the code isn't doing what I want. It looks like the ORDER BY isn't working or there is something else incorrect. As you can see in the screenshot below, Average_Cost_Price on the highlighted...

Thanks very much for your feedback on this subject, it has been very helpful :) . I'll speak with my managers regarding some of the key points you have raised.

Thanks for your suggestion, I have added ORDER BY to the statement but I'm still not getting the desired results, maybe I haven't added it correctly. My code is now: Dim dbs As DAO.Database Dim rstDeliveries As DAO.Recordset Dim strSQL As String On Error GoTo ErrorHandler Set dbs = CurrentDb...

I see what you are asking but I'm still not 100% sure how to word the answer. I believe ACP is a variable. As for your "Where is it from" question, the only way I know to answer this is that ACP is a calculated field in the Deliveries_Query query. Sorry for making this difficult 😕

I'm a little confused by this as my understanding of AVCO is that the current AVCO is calculated from the cost and quantities of all deliveries of that item, depending on sales and purchase quantities. Doesn't that mean if there is a amendment to a delivery record this could affect the current...

I am still learning code, so I have taken this from https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb243789(v=office.12)?redirectedfrom=MSDN and modified it. I don't fully understand it, hence why I said: and Please can you reword the question :)

strSQL = "SELECT * FROM Deliveries_Query 'Deliveries_Query' is the query in question

ACP is a field in the query, as shown in the screenshot of the query.

There is a chance this scenario never occurs but through testing this AVCO feature it seemed a possibility. Another possibility is if a purchase needs to be retrospectively entered, i.e. if a purchase delivery was not recorded on the day it arrived. Like I say, this might never happen but when...

Thanks very much for this info, I was definitely over complicating it thinking I needed a function. I have been able to use your calculation to get the AVCO via a calculation field in a query. It works great when a new purchase record is added as the last record in the query but not so well when...

Thanks, I definitely want to stay away from storing the AVCO. I have been instructed by management to introduce the average cost for each product in such a way that reduces the requirement of updating values manually. We have a history of our prices being inputted incorrectly and therefore...

Hi all, I'm hoping for some assistance to help me build a function which calculates the average cost price (ACP) of our stocked products. I have had a look online to see if anything already exists but couldn't find anything suitable. I have attached 3 examples taken from an Excel workbook to...