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?

On Database » Microsoft Access

1,682 words with 2 Comments; publish: Wed, 28 Nov 2007 19:13:00 GMT; (25094.73, « »)

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

All Comments

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

      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

      #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