Not sure how to accomplish this.

[Alvard]

I have 2 tables with data, one titled Products and one titled purchases. I would like to have a process the finds all the cost columns in the purchases table for each product, average them, then fill the results into the Average cost column of the Products table. Note there are obviously multiple entries for each product in the purchases table. Any help would be appreciated.

[CJ_London]

welcome to the forum, but you need to provide more information if you want anything more than a guess.

For a start - what are the fields in your tables, what is the relationship between them? what do you mean by process? why are their multiple cost columns in the purchases table for each product?

Best I can guess at the moment is either use a query or use vba code

[Alvard]

Ok a little more background. I have products I purchase from different locations. The price can vary between each location. I record all my purchases in the purchases table. The purchases table contains the fields ID, Purchase Date, MSKU, Product Name, Buyer, Cost, Sales Tax Rate, Cost of Goods. The Products table contains the columns ID, MSKU, Product Name, Brand Name, UPC, ASIN, Parent ASIN, MSRP, Avarage cost of goods. I am looking for a way to automatically calculate the Average Cost of Gods in the Product table by using the information in the Purchases table. So say a product has 5 purchases listed in the purchases table I would like it to average the cost of goods column for each of those product purchases and populate the result in the average cost of good in the products table.

[CJ_London]

I don't see the relationship between the tables - unless you are using MSKU and/or product name. You haven't answered all my questions so I'm still guessing

Also not clear why you would want to store the average when it can be calculated as and when. It is normally bad practice to store calculated values

no idea what you mean by 'automatically update' - when? you have a new purchase? its a Monday?

to calculate the average will require a group by query, and you cannot use sql to update an existing record using aggregate queries, so you will need to use vba code

something like this in principle, you can adapt it to run on a button or other event

dim sqlstr as string
dim rst as dao.recordset

sqlstr="SELECT productname, avg(cost) as avcost FROM purchases GROUP BY productname"
set rst=currentdb.openrecordset(sqlstr)
while not rst.eof
    currentdb.execute("UPDATE products SET avCost=" & rst!avcost & " WHERE productname='" & rst!productname & "'")
    rst.movenext
wend
rst.close
set rst=nothing

for future reference, be clear and provide all relevant data so people can provide a more focused response. I suspect the above is still not enough to meet your requirements, but due to other commitments I am no longer in a position to respond.

good luck with your project