How do i calculate the Product of several fields???

[Milkstr]

Hi
I have a query off a database that works fins and displays a list of 4 numbers in the query in seperate fields, however i only want 1 field to be displayed and that wants to be the PRODUCT of the other 4 i.e. field1 * field2 * field3 * field4.....at some point this could need to be more numbers. Excel has an easy function "=PRODUCT" is there away of doing this in Acess?????

[ADezii]

Hi
I have a query off a database that works fins and displays a list of 4 numbers in the query in seperate fields, however i only want 1 field to be displayed and that wants to be the PRODUCT of the other 4 i.e. field1 * field2 * field3 * field4.....at some point this could need to be more numbers. Excel has an easy function "=PRODUCT" is there away of doing this in Acess?????

__1. Create a New Query.
__2. Place the [Field1], [Field2], [Field3], and [Field4] Fields into the Query Grid.
__3. Create a Calculated Field called Total (see code snippet).
__4. Deselect the Show Box for Fields 1, 2, 3, and 4.
__5. Run the Query ==> Only the Total Field will be visible and will be the Product of the 4 other Fields.

Total: ([Field1]*[Field2]*[Field3]*[Field4])

[Milkstr]

Thanks for the speedy reply, but not quite what i was after, the fields i want the product of are on seperate records, so in the query there could just be for example 2 records filtered off, or there could be 5 filtered off and i want the product of the field regardless of how many potential records there is, hope that makes sense. Any Ides???

[ADezii]

Thanks for the speedy reply, but not quite what i was after, the fields i want the product of are on seperate records, so in the query there could just be for example 2 records filtered off, or there could be 5 filtered off and i want the product of the field regardless of how many potential records there is, hope that makes sense. Any Ides???

More complicated scenario. Post Query results illustrating exactly what you are looking for and I'll check back in later.

[Milkstr]

Multiple | FixtureID | GameDate | Odds | ResultHomeAwayDraw | Win
5 | 1 24/03/2007 140 Home 0
10 1 24/03/2007 140 Home 0
10 7 24/03/2007 130 Home 0
70 1 24/03/2007 140 Home 1
70 5 24/03/2007 200 Home 0
70 6 24/03/2007 120 Home 0

This is the list of the current query, where the "multiple" colum = 70 for example i want the 140 * 200 * 120 displayed.....i.e. where the "Multiple" Colum = 10 then i want 140 * 130 displayed.........etc so then i can run another query with just the product of the odds colum, if all that makes sense, i thought it would be easy!!!!! but its not!!!

[Milkstr]

Multiple | FixtureID | GameDate | Odds | ResultHomeAwayDraw |
5 | 1 | 24/03/2007 | 140 | Home
10 | 1 | 24/03/2007 | 140 | Home
10 | 7 | 24/03/2007 | 130 | Home
70 | 5 | 24/03/2007 | 140 | Home
70 | 5 | 24/03/2007 | 200 | Home
70 | 6 | 24/03/2007 | 120 | Home

[ADezii]

Multiple | FixtureID | GameDate | Odds | ResultHomeAwayDraw |
5 | 1 | 24/03/2007 | 140 | Home
10 | 1 | 24/03/2007 | 140 | Home
10 | 7 | 24/03/2007 | 130 | Home
70 | 5 | 24/03/2007 | 140 | Home
70 | 5 | 24/03/2007 | 200 | Home
70 | 6 | 24/03/2007 | 120 | Home

Let me think it over for a little and I'll get back to you.

[ADezii]

Multiple | FixtureID | GameDate | Odds | ResultHomeAwayDraw |
5 | 1 | 24/03/2007 | 140 | Home
10 | 1 | 24/03/2007 | 140 | Home
10 | 7 | 24/03/2007 | 130 | Home
70 | 5 | 24/03/2007 | 140 | Home
70 | 5 | 24/03/2007 | 200 | Home
70 | 6 | 24/03/2007 | 120 | Home

__1. I don't know the name of your Table but for the sake of simplicity, I'll call it tblGames.
__2. Create a Query call qryProduct whic will consist of 1 Field for now, namely [Multiple].
__3. Set the Sort Order of the [Multiple] Field in qryProduct to Ascending.
__4. Set the Unique Values Property to yes for qryProduct. This Query will now generate a list of 'Unique' [Multiple] values.
__5. Copy and Paste the following Function to a Standard Code Module.

Public Function fCalculateProduct(intMultiple As Long) As String
Dim intNoOfMultiples As Integer, intCounter As Integer, lngProduct As Long
 
'Calculate the # of Records for the [Multiple]
intNoOfMultiples = DCount("*", "tblGames", "[Multiple]=" & intMultiple)
 
lngProduct = 1  'Initialize
 
If intNoOfMultiples = 1 Then
  'Only 1 Record - just return [Odds]
  fCalculateProduct = Format$(DLookup("[Odds]", "tblGames", "[Multiple]=" & intMultiple), "Standard")
Else
  'Now we got work to do
  Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  Set MyDB = CurrentDb()
  Set MyRS = MyDB.OpenRecordset("SELECT * From tblGames WHERE [Multiple] =" & intMultiple, dbOpenSnapshot)
  MyRS.MoveFirst
    Do While Not MyRS.EOF
      lngProduct = lngProduct * MyRS![Odds]
      MyRS.MoveNext
    Loop
      fCalculateProduct = Format$(lngProduct, "Standard")
      MyRS.Close
End If
End Function

__6. Create a Calculate Field in qryProduct called Product. This Calculated Field will call the fCalculateFunction() and pass to it the [Multiple] Field. It will look like this in the QBE Grid.

Multiple                      Product: fCalculateProduct([Multiple])
tblGames
Ascending

__7. Run the Query.

OUTPUT:

Multiple    Product
5    140.00
10    18,200.00
70    3,360,000.00
89    1,521,275,736.00

NOTE: 89 was my own little Test Value. If you have any other questions, please feel free to ask.

[Milkstr]

Thanks for your help there!!! I just can't quite get it to work, i get an error from the standard Module, on the line "myRs.MoveFirst" it errors there saying "no current record" any ideas what it could be? i've used all the same names as you in your example??

[ADezii]

Thanks for your help there!!! I just can't quite get it to work, i get an error from the standard Module, on the line "myRs.MoveFirst" it errors there saying "no current record" any ideas what it could be? i've used all the same names as you in your example??

That Error indicates that the SQL Statement did not return any Records but if you made it to this line, there logically must be because intNoOfMultiples cannot = 0 since it is being passed to the Function(), and the case has already been accounted for where intNoOfMultiples = 1. Are [Multiple] and [Odds] Required Fields? Do Records actually exist in tblGames and are the Field Names identical? Also, check for a Typo in the OpenRecordset() line. Let me know how you made out.