Custom Total Row

Question

In an access table I know you can add a Totals row and it allows you to do sum, avg, and other calculations.  However I want to put a custom calculation because some the rows need formula to calculate the total correctly. For example I want an actual Profit Margin (Net Profit/Total Profit), but I cant seem to find a away to do this on the table.  Sum and Average will not work.  Does anyone have any ideas?

---

Demecio Molina

Answer 1

Everything I can find points to NO. You may be able to gleen something from what Allen Brown says in this link.

HTH

---

Chris Ward

Answer 2

Do not try in a table but in queries and forms.

If you explain what you are trying to accomplish thenothers may have proposals for you.

Answer 3

You are better off using a Form in datasheet view.

If you don't mind breaking the rules of database normalization you could add a caculated field in your Table. You'll hear everyone I know tell you how wrong that is including me.

---

Chris Ward

Answer 4

I have automated the database I am working on and will be using Microsoft Web Developer to link this table to an internal website.  Its going to be a kind of flash card. How do you calculate a row in a querie?

---

Demecio Molina

Answer 5

By entering an expression either in the SQL or from the GUI grid screen in a column.

---

Chris Ward

Answer 6

You can aggregate on expressions, such as


SUM( [Net Profit] / [Total Profit] )

Note 1:  since your fields names are ill formed, you need to use [ ]  around them. You can't use:  SUM(Net Profit/Total Profit), as example.

Note 2:  better to have a WHERE clause which will remove records with  [Total Profit] = 0. The WHERE clause beinf evaluated before the aggregation occurs, that would remove potential error (division by 0, here).

Answer 7

As an example you could either use SQL like this;

SELECT Products.PEBNumber, Sum(TestFees.Fees) AS SumOfFees, Sum(nz([TestFees].[Fees],0)) AS SumOfTestFees
FROM TestFees RIGHT JOIN Products ON TestFees.TestID = Products.cboTestFees.Value
GROUP BY Products.PEBNumber;

Or in the GUI like this;

Keep in mind I am using A2007 so cannot show you exactly the way it looks in A2010 but the function is the same.

---

Chris Ward

Answer 8

I have a Profit Margin Column Already.  What I want to do is create a row "Total" that will show me the overall PM for that column as well as some other fields.  Sum and Average will not work for this.

---

Demecio Molina

Answer 9

oKay, I see the real question is not what you posted when Vanderghast and I responded. I don't think you can have a total row in a query but you can in a Report or you can fake it in a Form.

I say not in a Query because you have the same limitations on a Total Row as in a Table. AFAIK

---

Chris Ward

Answer 10

We can simulate a  ROLLUP, using a join and a GROUP BY on expressions (or with a UNION query). Assuming that you want a Rollup over cities and countries, someone can use:

SELECT CHOOSE(iota, country, country, null) AS OverCountry, 
             CHOOSE(iota,  city, null, null) AS OverCity,
             SUM( someExpression )
FROM data , iotas
WHERE iotas.iota IN( 1, 2, 3)
GROUP BY CHOOSE(iota, country,  country, null), CHOOSE( iota, city, null, null)
ORDER BY  CHOOSE(iota, country, country, null), CHOOSE( iota, city, null, null)

As for a rollup, if the value is null, for a country or for a city, that indicates a SUM for all (country, or cities in the not null country).

Table Iotas has one field, iota, the primary key, with at least three records (with values 1, 2 and 3). I use the IN construction to help remembering it, but iota <= 3 could be better. 

The ORDER BY clause is facultative, but may help debugging.

----------------

Changed the initial SWITCH to CHOOSE.

Changed the initial inner join to a cross join.