Union Query Example Two
Summing the Total of Two Fields in the Same Table

This tip Union Query Example Two Summing the Total of Two Fields in the Same Table uses a question as its basis, but explains how to sum the total of two fields that are in the same table, by the use of a Union Query.

Question:

 I have a selection table like this:

id	colour
100	Blue
112	White
125	Yellow
488	Green
489	Red
500	Pale Blue
800	Grey
900	Brown
1000	Dark Gr

And I also have another table where the information is introduced via a form:

id	Month	Code of color1	Code of color2	Cost of color1	Cost of color2
1	January	112	112	100	15
2	January	125	900	12	54
3	February	488		47
5	March	1000		15
6	March	800		26
7	April	800		10

Currently I want to do a report to do a balance of the cost for each month, in addition to this I would like the description of colour to appear.

 
I would like to have a report like this:

Month	Code of colour	Description	Sum of cost caused by this colour
January	112	white	$115.00
	125	yellow	$12.00
	900	brown	$54.00
		total cost	$181.00
February	488	green	$47.00
		total cost	$47.00

Etc...

 
Answer:


To do achieve the above will take a few steps:

 
First of all you will need to create a union query to put all the colors into one field and the associated cost into another field, instead of the two fields you have for each currently.

To do this:

To build a union query:

*****

Microsoft Access 2003 or earlier

Query > New > Design View > Close

(i.e. do not add any tables)

Then, on the toolbar, > Query > SQL specific > Union

A window will open for you to write a SQL query.

Microsoft Access 2007 or later

On the Ribbon > Create > Design View > Close

(i.e. do not add any tables)

Then, on the ribbon, Query Type, Union

A window will open for you to write a SQL query.

*****

I will give you a few definitions before I give you the SQL, which will solve the above two problems.
 

SELECT = the fields you wish to select for the query. Notice the [] brackets. These need to be used and are separated by a comma.

FROM = the table where the fields are located.

WHERE = is like setting the criteria in a query.

 
In the resulting query, the field names will be those contained in the first SELECT statement. The second Union Select, the field names will be ignored.

The field names in the second SELECT statement can be different, but must have the same data type.

Add the following SQL syntax.
 

Select [Month],[CodeofColor1],[CostForColor1]etc

FROM [tblname]

 

UNION ALL SELECT [Month],[CodeofColor2],[CostForColor2]etc

FROM[tblName];

 

Change tblName to the name of the table and check the spelling for the above field types.

Once you have created the above query, run it to check that you do not receive any errors, and the the data looks like it should.  i.e. all the colours in the one column, and the associated cost.


Then, create another query:


This time it will be a normal select query, but it will be based on the first query. Include all the fields.

I am presuming knowledge of how to create a select query, but if you need help, download the Free Tutorial on Microsoft Access from this page and read the section on queries.

With this query in Design view, click on the Totals button on the toolbar - it looks like a funny E. This will add a new row to your query design grid, 'Total'.


Leave the 'total' row as Group By for the month column and the colorcode column then, in the the colorcost column, change group by to 'Sum'


This will group all your months and codes and sum how much each color costs for each month.


Create a report, based on the above query, adding all the fields: (Once again if not sure how to do this, download my free tutorial on Microsoft Access as per above)

Choose to group the report (when prompted to do so) by "Month" or whatever the field name is that stores the month data.
 

This will create a report with each month grouped together.

Finally, to sum the total cost for each month, in the footer for month (to view, click on the Sorting Grouping button on the toolbar, select month and choose footer in the group properties section) and add an unbound text box with the following:

 =Sum(NameOfCostField)

Change the NameOfCostField to the text box name of the sum of the individual colors. This will display the sum of the costs for the month for all the colors.

When you are finished open your report to check you get the results you expect.

Thanks for reading Union Query Example Two Summing the Total of Two Fields in the Same Table hope it is of some help, if not a related tip, you may find of interest/use:

Union Query Example One

---

---