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
