multiplying in a report

[Dr. Chill]

I am tyring to multiple a column of data in a footer of a report. Is there a way to do this? I thought it woud just be a function like product or something like sum.

 

[Andrew Fergus]

Hi

I'm not sure I understand the problem 100%. If you want to add up the values shown for a field on your report and show the total in the report footer, create an unbound text box in the report footer and use this as the control source :

=Sum([Your_Field_Name])

Note : please change the [Your_Field_Name] to the name of the field that you want to add.

To display the result of one field multiplied by another on your report, create an unbound text box (in the detail section of your report) and use something like this for the control source :

=IIf(IsNull([Field1]),0,[Field1])*IIf(IsNull([Field2]),0,[Field2])

To add the product of two fields in the footer of your report, create an unbound text box and use this as the control source :

=Sum(IIf(IsNull([Field1]),0,[Field1])*IIf(IsNull([Field2]),0,[Field2]))

HTH, Andrew.

 

[Dr. Chill]

What I am trying to do is to mulitply in a footer like you can sum in a footer. Instead of adding up or totaling a column I want to multiply each item for one number. Like if [values] had 1,2,3 I want to be able to go 1*2*3 and put the result in my footer. Hopefully that explains it better.

 

[Andrew Fergus]

Ah, now I understand....
I don't think this can be done on the report itself (someone please correct me if I am wrong), rather I think it could (or should) be done in the query that you have based the report on. There is a method of referring to a previous line in a query - a bit like a "running sum" but you want a "running product". The method eludes me for the moment.
Andrew

 

[Dr. Chill]

If it can be done in a query that is cool too. I just can't figure out how. I would think there should be a way to do it. In case it matters I am using 97.

 

[Andrew Fergus]

Hi Dr. Chill

Apologies in advance for the long answer. This can be done with three queries - this may be one or two queries too many but I split it up to allow what is happening to be better understood.

The first query sorts your values (I have assumed the values will be sorted in ascending order) that you want on the report, so be sure to include all of the fields you want in your report - you may already have a query that does this. Don't worry about the sort order at this point in time, you can re-sort the fields on the report irrespective of how they are sorted in the queries.

The second query is based on the first query and it assigns a "ranking" to the values from 0 to n-1 (where n is the number of records in the first query). Add the fields you want on the report into the new query, click View -> Properties, Select the "Sorted Values Query" (or whatever you have called it) in the top half of the query screen and change the "Alias" to something like "Sorted_Values_Alias" (without the quotes). Add a new field into the query using the following :

Ranking: (Select Count(*) from Q_Values_Sorted where [value] < [Sorted_Values_Alias].[value]; )

Please note that I used the query Q_Values_Sorted, gave it the alias "Sorted_Values_Alias" and I used the field name [value].

This query will assign a ranking to the values, which we will use in the next query. Save the query (I used the name "Q_Values_Ranked".

FYI the SQL for my first query is as follows :

SELECT Sorted_Values_Alias.value, (Select Count(*) from Q_Values_Sorted where [value] < [Sorted_Values_Alias].[value]; ) AS Ranking
FROM Q_Values_Sorted AS Sorted_Values_Alias;

NB : you could probably avoid these first two queries if you had an autonumber field in your table (an autonumber field would have a similar effect as the ranking field) and you knew you wanted to include all records on the report, but I try not to use autonumbers so have based this solution on a structure that does not have autonumbers.

The third query is based on the 2nd query, add all of the fields you want on the report, include the ranking field and then add a new field with the following :

Product: IIf([Ranking]=0, [value], [value] * (DLookUp("[Product]", "Q_Values_Products", "Ranking = " & [Ranking] -1 )))

This will give you a running product for each line in the query, a bit like a factorial, where it multiplies the [value] by the product of the previous values. I used the field names [value] &[Ranking] and I used the query name "Q_Values_Products" (without the quotes). Save this query - note the query name and the 2nd argument in the DLookup function must be the same (I used Q_Values_Products). Also, the new field name (i.e. Product) must be the same as the first field in the Dlookup function. The quotes and square brackets are necessary in the Dlookup function.

The SQL for the query I tested this on follows :

SELECT Q_Values_Ranked.value, Q_Values_Ranked.Ranking, IIf([Ranking]=0,[value],[value]*(DLookUp("[Product]","Q_Values_Products","Ranking = " & [Ranking]-1))) AS Product
FROM Q_Values_Ranked
ORDER BY Q_Values_Ranked.Ranking;

If you base your report on this query (you might have developed one already, so you could change the record source of your existing report to the new query, provided the field names have not changed), add a new unbound text box into the footer of the report with the following as the control source :

=Max([Product])

This will put the product of the column of values into the report footer. In actual fact it is seeking the largest [product] value which equals the product of all values multiplied together. Please be aware that where you add this into the footer, the running products, in the detail section of the report, appear to have funny values - I'm not sure why but I figure that it doesn't matter for the moment.

A lot of work for such a small item on the report!

HTH, Andrew.

 

[Dr. Chill]

Thanks sorry I haven't checked back all weekend. That is a nice long answer. I have to read it and I will let you know if I have any questions.