Select Query with Totals - Find Product?

[sarahjg]

I would like to do a select query that uses the totals to group records together.

When you turn the totals on the choices you have for the value are things like sum, first, last, var, stdev, etc. I am interested in getting the multiplicative product. Is this possible?

Any help will be appreciated.

 

[David R]

Can you give an example data set? I'm not sure I understand what you're asking...

 

[sarahjg]

Sorry I wasn't very clear....

If I starting with the data:
IL 2002 1.05
IL 2002 1.10
IL 2002 0.95

I would like to get the result:
IL 2002 1.09725

Where 1.09725 = 1.05*1.10*0.95

 

[David R]

Oh, wow. Where do you need this data? In a form or report? I think you can loop through a set of records with code and get the multiplicative product, but it won't be a straightforward query field.

Here's an example to get you started:

Function MultiplySubentries(PK As String) As Double
Dim m As Double
Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("SELECT ThisField FROM tableName WHERE PK = '" & PK & "';"
    rst.MoveLast
    rst.MoveFirst
    
    m = 1
    
    Do While Not rst.EOF
    m = m * Nz(rst!ThisField, 1)
    rst.MoveNext
    Loop

    MultiplySubentries = m
    
    rst.Close
    
End Function

Where:
ThisField is the field you want to multiply
PK is the field that identifies the records you want to sum (IL or 1002 or something, in your example). It is actually a Foreign Key to your main table, I imagine.
tableName is the table you're pulling this data from.

In addition if you're running Acc2k or later, you have to enable the DAO 3.6 library in References, or convert the code to ADO, else you will get a Type Mismatch (13).

Good luck,
David R


[This message has been edited by David R (edited 05-22-2002).]

 

[llkhoutx]

In your query, create a new field on containing your multiplicative product, setting your criteria appropriately.

 

[David R]

Please enlighten us, llkhoutx, how will this get the multiplicative result of one-or-several subentries?

 

[sarahjg]

Thanks for the help David R. I was hoping that there was a way to do this without using recordsets.

A more detailed example of what I'm trying to do is....starting with that data:
IL 2002 1.05
IL 2002 1.10
IL 2002 0.95
PA 2001 1.08
TN 2001 1.02
TN 2002 0.95
TN 2002 0.85

I would like to output the results of these calculations into another table.....to get the results:
IL 2002 1.09725 (1.09725=1.05*1.10*0.95)
PA 2001 1.08
TN 2001 1.02
TN 2002 0.8075 (0.8075=0.95*0.85)

In other words, when the first and second columns are the same group them together and calculate the product of the last column.

 

[David R]

Ugh. Is there an upper limit to how many of these entries there will be per state/year combination? If so you might be able to export the data from a crosstab query into an excel spreadsheet, then calculate with something like this: =B3*(IF(C3,C3,1))*(IF(D3,D3,1))*(IF(E3,E3,1)), etc.

I'm not sure if that helps. The other solution is to create a recordset that identifies all the unique State/Year data sets, then loops through them all and writes the result to a new table using the function I gave you above. But I'm not sure my VBA skills are up to that task. You might try posting a synopsis of the problem in the Modules/VBA forum.

By the way, the WHERE clause above will change if you're using both fields, of course.

Function MultiplySubentries(StateValue As String, YearValue As Integer) As Double
Dim m As Double
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ThisField FROM tableName WHERE [StateField] = '" & StateValue & "' AND [YearField] = " & YearValue
etc..

[This message has been edited by David R (edited 05-28-2002).]