Macro to fill blank cells with a calculated value

[Lannce]

Hi Everyone!

Im really new user of Access so i think i need a litle of help to do this macro.

I have the following Table1:

Type	Date	Product	Quantity	Unit_Cost	Total_Cost
one	2014/10/01	A	2	1000	2000
two	2014/10/01	A	3	1100	3300
one	2014/10/02	A	2	1000	2000
X	2014/10/03	A	2
two	2014/10/01	B	2	1000	2000
one	2014/10/02	B	2	1000	2000
X	2014/10/04	B	3

what i need is run a macro who fill the data when the Type = "X"
the value of Unit_Cost will be =sum(Unit_Cost)/sum(Quantity) where Product is equal to "A or B" and Date < Date_of_AorB

I try to do a macro with this info, but do nothing..

For each record in Table1
where condition = [Table1].[Type] = "Sale"

SetLocalVar
Name varProd
Expression = [Table1].[Product]

SetLocalVar
Name varDate
Expression = [Table1].[Date]

SetLocalVar
Name varCost
Expression = [Table1].[Unit_Cost]

For each record in Table1
where condition = [Table1].[Product] = varProd
SetLocalVar
Name varCost
Expression = varCost + [Table1].[varCost]

Edit Record
SetField Unit_Cost
value = [varCost]

(yes i know that is not the final value that i want, but i was trying to view if the macro work or not)

I'm missing something?

Thanks for your help!

btw im using Access 2010

[June7]

Cannot reference tables or queries directly like that.

Saving calculated data, especially aggregate data, is usually a bad idea. Do calcs when need.

Access 2010 has a new type of field - Calculated. What you show in the table is just multiplying Quantity * Unit_Cost. This can be done in table or query and does not use the Sum() function

Sum() function is an aggregate function and is used in an aggregate (GROUP BY) Totals query to do calcs on columns of data.

[Lannce]

I want to call this table in an Excel sheet, directly connected to this BD.

This table is the main, center for another 4 tables who have differents kinds of "Types" so then this table have the resume of the info but the field of Unit_Cost and Total_Cost have to be calculated by the previous rows.

I can do this directly in the excel sheet, but the high amount of data make this almost impossible (due long times), that's why i was looking a way to calculate the field directly in Access so then call the data direclty in a sheet in excel.

[June7]

Should not design a table with records for summary data mixed with records of raw data. EXTREMELY bad design.

Could build a report using Grouping & Sorting feature with aggregate calcs in group footer. This will allow display of detail records and summary calcs.

The report could be exported to Excel but maybe don't even need the export to Excel. Why is Excel involved?

[Lannce]

Yes you have right, i will try to do that then.
Thanks for the help!

[Lannce]

Cannot reference tables or queries directly like that.

Saving calculated data, especially aggregate data, is usually a bad idea. Do calcs when need.

Access 2010 has a new type of field - Calculated. What you show in the table is just multiplying Quantity * Unit_Cost. This can be done in table or query and does not use the Sum() function

Btw, the problem is about the different kind of calculation in the field Unit_Cost and Total_Cost.

If the Type = X the total_cost is not (unit_cost * Quantity).. is the average of all previous Total_Cost for that Product

[June7]

The X record is the aggregate calc. Do that in the report. Set up a grouping on the Product field. Do aggregate calc in textbox in group footer. The expression would be:

=Sum([Quantity] * [Unit_Cost])

or

=Avg([Quantity] * [Unit_Cost])