Add values from diff. queries

[Biv]

Hi

I have 2 different queries that generate results for
salesman1 and salesman 2. The same Salesperson can be
found in both queries, depending on how the sale was
structured. I need to generate a report or query that will
add the values from each table for the same salesperson.

Right now i have a 2 seperate reports for salesman 1 and
2. Is it possible to combine the values from the different
reports or queries into one query or report?

Any help would be greatly appreciated.

Thanx

 

[Gina]

Sounds like you need to use a Union Query. A Union Query
enables you to pull the information for two different
queries as one query, but you should be pulling the same
fields. Then you would just run a report to total for
each salesman. Hope this helps.

 

[Biv]

Problem is that the fields are know as Salesman 1 and
Salesman2 in the table and in the form. So access looks at
them individually as they have different field names. The
only way is to somehow add them, in a report or query or
maybe even a form generated from a query. Any clues??

 

[John Spencer (MVP)]

Create a UNION All query and then use that as the source of Totals QUery

QryUnionSales:
SELECT Salesman1, SalesAmount
FROM TableName
UNION ALL
SELECT SalesMan2, SalesAmount
FROM TableName

Then
SELECT Salesman1, Sum(SalesAmount)
FROM QryUnionSales
GROUP BY Salesman1

That should give you the total sales amount for each salesman.