PRODUCT calculation in queries

[Guest]

Hi,

I'm having trouble creating a query/ expression that will multiply all of
the values in a field together (i.e return the Product of those numbers.)

I have a table with several records, Field3 of which contains numbers (with
a few decimal places.) Ultimately, I want to be able to calculate, for each
record in the table/query, the product of all of the other Field3 values
excluding its own (i.e the expression will return for Record #1 the product
of Field3 in records # 2-10, the expression for record #2 will return the
product of records#1,3-10 etc)

At the moment, however, I'm simply struggling to be able to return any sort
of product at all. I don't seem to be able to locate any equivalent of the
Excel PRODUCT function.

Cany anyone with better maths skills than me help!?
Joe

 

[Guest]

Furthermore,

I'de really like to create a PRODUCT custom function that I can use in a
query, report or a table. Anyone clever enough to come up with the VBA Code
for this?

Joe

 

[Allen Browne]

This is untested aircode, but you should be able to save the function below
into a standard module (Modules tab of Database window). Then in a query,
type an expression like this into a fresh column in the Field row:
Product("Table1", "Field3", "ID <> " & [ID])

Function Product(strField As String, strTable As String, _
Optional strCriteria As String) As Double
Dim rs As DAO.Recordset
Dim strSql As String
Dim dblResult As Double

'Build the SQL string.
strSql = "SELECT " & strField & " FROM " & strTable & _
" WHERE (" & strField & " Is Not Null) "
If strCriteria <> vbNullString Then
strSql = strSql & " AND (" & strCriteria & ")"
End If

'Loop through the recordset.
Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")
If rs.RecordCount > 0 Then
dblResult = rs(strField)
rs.MoveNext
Do While Not rs.EOF
dblResult = dblResult * rs(strField)
rs.MoveNext
Loop
End If
rs.Close

Product = dblResult
End Function

 

[Guest]

Hi,

Thanks for the reponse, I've given it a whirl, but it throws up a runtime
error. I'm sure the problem lies with the limitations of my instructions,
together with my relative ineptitude with VBA and with maths in general.

What I really need is a basic generic Product function that can simply be
dropped into any query. After that I can simply use queries to fiddle with
the critera aspects of things. I would have assumed that a function based on
similar code/maths principles as the existing series-based functions (Count,
Sum etc) shouldn't be that difficult to write.

Is there any way to see the underlying code behind the standard functions
like Sum - if you could take this particular function and modify the code by
changing the addition operator to a multiplication operator that would do the
trick.

All of these assumptions are made by someone with a very basic maths
education so feel free to highlight my naivety!


Joe

This is untested aircode, but you should be able to save the function below
into a standard module (Modules tab of Database window). Then in a query,
type an expression like this into a fresh column in the Field row:
Product("Table1", "Field3", "ID <> " & [ID])

Function Product(strField As String, strTable As String, _
Optional strCriteria As String) As Double
Dim rs As DAO.Recordset
Dim strSql As String
Dim dblResult As Double

'Build the SQL string.
strSql = "SELECT " & strField & " FROM " & strTable & _
" WHERE (" & strField & " Is Not Null) "
If strCriteria <> vbNullString Then
strSql = strSql & " AND (" & strCriteria & ")"
End If

'Loop through the recordset.
Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")
If rs.RecordCount > 0 Then
dblResult = rs(strField)
rs.MoveNext
Do While Not rs.EOF
dblResult = dblResult * rs(strField)
rs.MoveNext
Loop
End If
rs.Close

Product = dblResult
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm having trouble creating a query/ expression that will multiply all of
the values in a field together (i.e return the Product of those numbers.)

I have a table with several records, Field3 of which contains numbers
(with
a few decimal places.) Ultimately, I want to be able to calculate, for
each
record in the table/query, the product of all of the other Field3 values
excluding its own (i.e the expression will return for Record #1 the
product
of Field3 in records # 2-10, the expression for record #2 will return the
product of records#1,3-10 etc)

At the moment, however, I'm simply struggling to be able to return any
sort
of product at all. I don't seem to be able to locate any equivalent of the
Excel PRODUCT function.

Cany anyone with better maths skills than me help!?
Joe

 

[Allen Browne]

What error are you getting?
Which line generates the error?

After pasting it into the code window, choose Compile on the Debug menu.
Does it compile okay?

If you are using Access 2000 or 2002, you may need to add a reference to the
Microsoft DAO Library. More on references:
http://allenbrowne.com/ser-38.html

Once you have it running, you can use it just like one of the built-in
functions in any query.

You cannot reverse-engineer the built-in functions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reponse, I've given it a whirl, but it throws up a runtime
error. I'm sure the problem lies with the limitations of my instructions,
together with my relative ineptitude with VBA and with maths in general.

What I really need is a basic generic Product function that can simply be
dropped into any query. After that I can simply use queries to fiddle with
the critera aspects of things. I would have assumed that a function based
on
similar code/maths principles as the existing series-based functions
(Count,
Sum etc) shouldn't be that difficult to write.

Is there any way to see the underlying code behind the standard functions
like Sum - if you could take this particular function and modify the code
by
changing the addition operator to a multiplication operator that would do
the
trick.

All of these assumptions are made by someone with a very basic maths
education so feel free to highlight my naivety!


Joe

This is untested aircode, but you should be able to save the function
below
into a standard module (Modules tab of Database window). Then in a query,
type an expression like this into a fresh column in the Field row:
Product("Table1", "Field3", "ID <> " & [ID])

Function Product(strField As String, strTable As String, _
Optional strCriteria As String) As Double
Dim rs As DAO.Recordset
Dim strSql As String
Dim dblResult As Double

'Build the SQL string.
strSql = "SELECT " & strField & " FROM " & strTable & _
" WHERE (" & strField & " Is Not Null) "
If strCriteria <> vbNullString Then
strSql = strSql & " AND (" & strCriteria & ")"
End If

'Loop through the recordset.
Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")
If rs.RecordCount > 0 Then
dblResult = rs(strField)
rs.MoveNext
Do While Not rs.EOF
dblResult = dblResult * rs(strField)
rs.MoveNext
Loop
End If
rs.Close

Product = dblResult
End Function

Hi,

I'm having trouble creating a query/ expression that will multiply all
of
the values in a field together (i.e return the Product of those
numbers.)

I have a table with several records, Field3 of which contains numbers
(with
a few decimal places.) Ultimately, I want to be able to calculate, for
each
record in the table/query, the product of all of the other Field3
values
excluding its own (i.e the expression will return for Record #1 the
product
of Field3 in records # 2-10, the expression for record #2 will return
the
product of records#1,3-10 etc)

At the moment, however, I'm simply struggling to be able to return any
sort
of product at all. I don't seem to be able to locate any equivalent of
the
Excel PRODUCT function.

Cany anyone with better maths skills than me help!?
Joe

 

[Douglas J. Steele]

Another way to calculate the product of the fields is to add their logs, and
then take the exponent of the result.

For example, if I've got a table named Factorial, with a field IntegerValue
in it that's strictly integer values 1, 2, 3, etc, I can calculate the value
of, say, 4! (four factorial, which is 4*3*2*1) as:

?Exp(DSum("Log([IntegerValue])", "[Factorial]", "[IntegerValue] <= 4"))
24

 

[Guest]

Hi,

Once pasted the code compiles fine.
I get the following runtime error:

Run-time error '3078':

The Microsoft Jet database engine could not find the input table or query
'Field3'.
Make sure it exists and that its name is spelled correctly.

When I click debug the following line is thrown up:

Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")

I'm using Access 2003 and the DAO library is definately referenced.

Joe

What error are you getting?
Which line generates the error?

After pasting it into the code window, choose Compile on the Debug menu.
Does it compile okay?

If you are using Access 2000 or 2002, you may need to add a reference to the
Microsoft DAO Library. More on references:
http://allenbrowne.com/ser-38.html

Once you have it running, you can use it just like one of the built-in
functions in any query.

You cannot reverse-engineer the built-in functions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reponse, I've given it a whirl, but it throws up a runtime
error. I'm sure the problem lies with the limitations of my instructions,
together with my relative ineptitude with VBA and with maths in general.

What I really need is a basic generic Product function that can simply be
dropped into any query. After that I can simply use queries to fiddle with
the critera aspects of things. I would have assumed that a function based
on
similar code/maths principles as the existing series-based functions
(Count,
Sum etc) shouldn't be that difficult to write.

Is there any way to see the underlying code behind the standard functions
like Sum - if you could take this particular function and modify the code
by
changing the addition operator to a multiplication operator that would do
the
trick.

All of these assumptions are made by someone with a very basic maths
education so feel free to highlight my naivety!


Joe

This is untested aircode, but you should be able to save the function
below
into a standard module (Modules tab of Database window). Then in a query,
type an expression like this into a fresh column in the Field row:
Product("Table1", "Field3", "ID <> " & [ID])

Function Product(strField As String, strTable As String, _
Optional strCriteria As String) As Double
Dim rs As DAO.Recordset
Dim strSql As String
Dim dblResult As Double

'Build the SQL string.
strSql = "SELECT " & strField & " FROM " & strTable & _
" WHERE (" & strField & " Is Not Null) "
If strCriteria <> vbNullString Then
strSql = strSql & " AND (" & strCriteria & ")"
End If

'Loop through the recordset.
Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")
If rs.RecordCount > 0 Then
dblResult = rs(strField)
rs.MoveNext
Do While Not rs.EOF
dblResult = dblResult * rs(strField)
rs.MoveNext
Loop
End If
rs.Close

Product = dblResult
End Function

Hi,

I'm having trouble creating a query/ expression that will multiply all
of
the values in a field together (i.e return the Product of those
numbers.)

I have a table with several records, Field3 of which contains numbers
(with
a few decimal places.) Ultimately, I want to be able to calculate, for
each
record in the table/query, the product of all of the other Field3
values
excluding its own (i.e the expression will return for Record #1 the
product
of Field3 in records # 2-10, the expression for record #2 will return
the
product of records#1,3-10 etc)

At the moment, however, I'm simply struggling to be able to return any
sort
of product at all. I don't seem to be able to locate any equivalent of
the
Excel PRODUCT function.

Cany anyone with better maths skills than me help!?
Joe

 

[Allen Browne]

Is your field named Field3? Or is it Field 3 with a space (in which case you
will need to add square brackets):
Product("Table1", "[Field 3]", "ID <> " & [ID])

Likewise, Table1 represents the name of your table, and ID represents the
name of your primary key autonumber field. Use your actual names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

Once pasted the code compiles fine.
I get the following runtime error:

Run-time error '3078':

The Microsoft Jet database engine could not find the input table or
query
'Field3'.
Make sure it exists and that its name is spelled correctly.

When I click debug the following line is thrown up:

Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")

I'm using Access 2003 and the DAO library is definately referenced.

Joe

What error are you getting?
Which line generates the error?

After pasting it into the code window, choose Compile on the Debug menu.
Does it compile okay?

If you are using Access 2000 or 2002, you may need to add a reference to
the
Microsoft DAO Library. More on references:
http://allenbrowne.com/ser-38.html

Once you have it running, you can use it just like one of the built-in
functions in any query.

You cannot reverse-engineer the built-in functions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reponse, I've given it a whirl, but it throws up a
runtime
error. I'm sure the problem lies with the limitations of my
instructions,
together with my relative ineptitude with VBA and with maths in
general.

What I really need is a basic generic Product function that can simply
be
dropped into any query. After that I can simply use queries to fiddle
with
the critera aspects of things. I would have assumed that a function
based
on
similar code/maths principles as the existing series-based functions
(Count,
Sum etc) shouldn't be that difficult to write.

Is there any way to see the underlying code behind the standard
functions
like Sum - if you could take this particular function and modify the
code
by
changing the addition operator to a multiplication operator that would
do
the
trick.

All of these assumptions are made by someone with a very basic maths
education so feel free to highlight my naivety!


Joe

:

This is untested aircode, but you should be able to save the function
below
into a standard module (Modules tab of Database window). Then in a
query,
type an expression like this into a fresh column in the Field row:
Product("Table1", "Field3", "ID <> " & [ID])

Function Product(strField As String, strTable As String, _
Optional strCriteria As String) As Double
Dim rs As DAO.Recordset
Dim strSql As String
Dim dblResult As Double

'Build the SQL string.
strSql = "SELECT " & strField & " FROM " & strTable & _
" WHERE (" & strField & " Is Not Null) "
If strCriteria <> vbNullString Then
strSql = strSql & " AND (" & strCriteria & ")"
End If

'Loop through the recordset.
Set rs = DBEngine(0)(0).OpenRecordset(strSql & ";")
If rs.RecordCount > 0 Then
dblResult = rs(strField)
rs.MoveNext
Do While Not rs.EOF
dblResult = dblResult * rs(strField)
rs.MoveNext
Loop
End If
rs.Close

Product = dblResult
End Function

Hi,

I'm having trouble creating a query/ expression that will multiply
all
of
the values in a field together (i.e return the Product of those
numbers.)

I have a table with several records, Field3 of which contains
numbers
(with
a few decimal places.) Ultimately, I want to be able to calculate,
for
each
record in the table/query, the product of all of the other Field3
values
excluding its own (i.e the expression will return for Record #1 the
product
of Field3 in records # 2-10, the expression for record #2 will
return
the
product of records#1,3-10 etc)

At the moment, however, I'm simply struggling to be able to return
any
sort
of product at all. I don't seem to be able to locate any equivalent
of
the
Excel PRODUCT function.

Cany anyone with better maths skills than me help!?
Joe

 

[John Vinson]

The Microsoft Jet database engine could not find the input table or query
'Field3'.

How are you calling the function, Joe? It sounds like you're passing
'Field3' in the argument where the function is expecting a tablename.


John W. Vinson[MVP]

 

[Guest]

Yeah, I was - the original post from Allen Browne had the arguments for the
function the wrong way around. Once I stopped being lazy and actually read
the code that he gave me it was a simple question of swapping them around!
Small price to pay.

Once again the MVPs deliver in fine style. I salute you all!
Joe