adding two columns (1 Viewer)

[bauer]

Hi all!

I'm trying to add together one column from four queries that are the same. They all have an id, and are all a count from another table.
What I want to do now, is add up the count of the four tables.
I've tried a union, but I didn't get a total of the count (Though I'm not too familar with unions, so I could've done something wrong).
Thank you all for your help

bauer

 

[Pat Hartman]

Use a union query to get the other four queries into a single recordset. Then build a totals query based on the union query to sum the columns.

 

[bauer]

Thank you for your reply, however, when I tried the union query, it gave me only one instance of each type of the field that I wanted to group by, and not a total of the other column, but just one instance of it.

In the end, what I did was I made a table of my two feilds, and did an append query for each of my four queries, and then did a query on that one to get the total.

I'm not sure if this is the most effiecient way (probably not), but it works. The only problem I now have, which is just more annoying that anything else, is that I have to call all of these queries each time I want to get an accurate count, with of coures deleting the original contents each time. However, it works, so why not.

 

[Pat Hartman]

You might need to use union all rather than just union. Union eliminates duplicate rows, union all does not.