# Thread: How to multiply the data in one column but across many rows??

1. Registered User
Join Date
Jul 2002
Posts
2

## Unanswered: How to multiply the data in one column but across many rows??

Hi,

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!

MR

2. Registered User
Join Date
Jul 2002
Location
Romania
Posts
122
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
With myrs
Do Until .EOF: Multi = Multi * !Field2: .MoveNext: Loop
.Close
End With
Set myrs = Nothing
End Function

You can now use this function in any query you want, just like any built-in function.
Example:
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...

Regards,

Dan

3. Registered User
Join Date
Jul 2002
Posts
2
thanks! I'll give it a try...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•