HTML

Comparing Subtotals Using a 3D HTML Recordset

The main aim of this article is to see how you can compare subtotals using a 3D HTML recordset. Subtotals result from aggregate functions such as Sum, Avg and Count on database tables. This article is the third part of a four-part series.

Author Info:

By: Chrysanthus Forcha

Poor Best

Rating:

/ 2

November 25, 2008

TABLE OF CONTENTS:

I will use a hypothetical example here. Imagine that you own a bookshop with three branches in three different states in the U.S.A. Each of the bookshops has a database. Each database has a table of products ordered. Let us consider the following three tables as the tables you have in the three branches. They are very simplified for clarity.

ID	Name	Category	Number	Cost($)
1	BIC	Pen	75	25
2	BEST	Pen	75	25
3	Text Books	Book	40	600
4	Novels	Book	40	600
5	Exercise Bks	Book	20	300

ID	Name	Category	Number	Cost($)
1	BIC	Pen	105	35
2	BEST	Pen	105	35
3	Test Books	Book	50	750
4	Novels	Book	50	750
5	Exercise Bks	Book	25	400

ID	Name	Category	Number	Cost
1	BIC	Pen	110	38
2	BEST	Pen	110	38
3	Text Books	Book	45	700
4	Novels	Book	45	700
5	Exercise Bks	Book	45	700

For each of the above tables you can find the sum of the numbers ordered or costs for each category (pen, book). If you do that you will have the following subtotal tables respectively:

	Sub. T. No.	Sub. T. Costs($)
Pens	150	50
Books	100	1500

	Sub. T. No.	Sub. T. Costs($)
Pens	210	70
Books	125	1900

	Sub. T. No.	Sub. T. Costs($)
Pens	220	76
Books	135	2100

You may want to know the overall totals; that is the total number of pens you have ordered in all of your branches, the total number of books you have ordered in all of your branches, the total costs of pens in all of your branches and the total costs of books in all of your branches. Would it not be nice if you have the above subtotal tables in a 3D recordset, the first one in the first vertical pane, the next one in the next vertical pane and the third one in the third vertical plane? Then you can add by looking at a line of cells through the vertical planes.

You can do this conveniently with subtotals. However, a 3D recordset is not a direct solution to comparing cells or rows between ordinary tables. This is because the number of rows between ordinary tables will hardly be the same.

Next: Calculating Totals with a 3D Recordset >>