Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002

    Unanswered: 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!


  2. #2
    Join Date
    Jul 2002
    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
    End With
    Set myrs = Nothing
    End Function

    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...



  3. #3
    Join Date
    Jul 2002
    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