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

Question:

I have a selection table like this:

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

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

January

February

Etc...

Answer:

To do what you ask you will need to do perform 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:

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.

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 field names in the second SELECT statement can be different, but must have the same data type.

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.

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.

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:

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.

Do you like this tip? Subscribe to my free Newsletter to receive tips via email. Click Here to subscribe.