I want to get the total product for ABC and XYZ. Therefore, ABC will equal 2000 (= 10 x 10 x 20) and XYZ will equal 1000 (= 50 x 20). Can this be done in Access? I'm assuming this will have to be done using code. If so, any suggestions on how to write the code?
Thanks in advance, any help is greatly appreciated!
Paste the below code into a module.
Change Field2 with the exact name of the field
Change Field1 with the exact name of the field
Change TableName with the name of your table
Function Multi(ValueToMultiply) As Double
Dim myrs As DAO.Recordset
Set myrs = CurrentDb.OpenRecordset("Select Field2 From TableName Where Field1 = '" & ValueToMultiply & "';")
Multi = 1
Do Until .EOF: Multi = Multi * !Field2: .MoveNext: Loop
Set myrs = Nothing
You can now use this function in any query you want, just like any built-in function.
Create a new query. Switch to SQL View. Paste the following SQL string:
SELECT Field1 , Multi(Field1) AS Result from tablename
GROUP BY Field1;
You should however keep in mind that you may get overflow error messages if you have many rows with large amounts...