Tags: access, across, column, database, example, follows, microsoft, multiply, multiplying, mysql, oracle, organized, rows, rowsfor, sql, table
How to multiply the data in one column but across many rows?
How do I go about multiplying the data in a column across many rows?
For example, let's say I have the data in a table organized as follows:
Field 1 Field2
row1 ABC 10
row2 ABC 10
row3 ABC 20
row4 XYZ 50
row5 XYZ 20
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!
Leave a comment...
- 2 Comments
- 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...
Dan#1; Tue, 11 Dec 2007 19:24:00 GMT
- thanks! I'll give it a try...#2; Tue, 11 Dec 2007 19:25:00 GMT