Group Records and Find Product

[sarahjg]

I am trying to group data from Table1 so that when the the first 5 columns are the same the values in the 6th column is multiplied together. (See Example Below).

Table1:
PPA IL CO ST 2000 1.10
PPA IL CO ST 2000 1.05
PPA IL EX PR 2001 0.95
PPA PA CO ST 2000 1.08
PPA PA EX ST 2000 0.88
PPA PA EX ST 2001 1.09
PPA PA EX ST 2001 1.11


Table2:
PPA IL CO ST
PPA IL EX PR
PPA PA CO ST
PPA PA EX ST

Desired Results:
PPA IL CO ST 2000 1.155
(1.155=1.10*1.05)
PPA IL EX PR 2001 0.95
PPA PA CO ST 2000 1.08
PPA PA EX ST 2000 0.88
PPA PA EX ST 2001 1.2099
(1.2099=1.09*1.11)

I would like to output the desired results into another table.

 

[David R]

If I may ask, why are you storing the first 5 columns in both tables?

 

[The_Doc_Man]

Well, there might be a way, though it is very ugly. It involves at least one MakeTable to make a temporary table for what you need.

Make a new query in query-grid (design) view. Pull in the fields by name in your table. Sort ascending for the first five fields. For the sixth field, don't pull in the value. Pull in Log(Value). Provide a name of LogNumber, as...
LogNumber:Log(Value)
Turn it into a MakeTable query. Make table Temp1.

Now, create a new query based on Temp1 that pulls in all of the columns. Find the Greek letter Sigma in your toolbar. (Not your menu bar.) Click that so it looks like it is pressed in. A new row, Total, will be added to the grid just above the Sort row.

For your first five columns, in this new row you will use the drop-down to select GroupBy. Select Sum of LogNumber in the sixth column. Give it a name of SumLogValue using the same syntax shown earlier. Save this query as Qry2. (In the worst possible case, you might have to make a second MakeTable query out of it, I had some trouble when I tried this because of the implied function calls that were not very pretty.)

Now the payoff. Make one last query that is based on Qry2 (or TempTable2). For the first five columns, pull in the literal values. For the sixth column, pull in Exp(SumLogValue).

Because this uses logarithms, there will be a small amount of rounding error. So it would be a good idea to do some formatting to trim the column width to a specific number of decimal places.

Also, to make this work best, you should start with Double numbers instead of Single numbers. (Less roundoff likely in the first several digits.)

Finally, for the numbers you have shown, this technique will work fine. But if any of those numbers is EVER negative or zero, you cannot use this technique. You will have to do something else.


[This message has been edited by The_Doc_Man (edited 06-06-2002).]

 

[sarahjg]

David R - You can just ignore Table2. I am asking the same question as I asked in the Queries forum.

 

[sarahjg]

The_Doc_Man: Pretty slick trick....I think this just might work for what I'm trying to do.

 

[Pat Hartman]

Great idea Doc, but you should be able to do it with one or two (tops) queries and no temp table by nesting functions.

In query1 you do this:
Sum(Log(fld)) AS SumOfLog

and in query2 you do this:

Exp(SumOfLog) AS Product

 

[The_Doc_Man]

Pat, before I posted as I did, I tried exactly what you suggested. But it didn't like it until I split it into three queries. Don't ask me why, and I'll admit I could have fat-fingered something, but I would swear that I didn't. I even counted the parentheses by hand and stuff like that.

It was my original intent to suggest exactly as you said. Just that it didn't work when I tried it. And it would be bad form for me to give advice on an unworkable method.

 

[Pat Hartman]

I actually got it to work with a single query:

SELECT Field1, Field3, Field5, Field7, Field9, Exp(Sum(Log(Amt))) AS SumOfAmt
FROM Untitled
GROUP BY Field1, Field3, Field5, Field7, Field9;