Adding two queries

[Amin]

I have two queries. Query "A" has the following information as overtime

Name Duration
Mark 1
Ted 2
Bill 2
Alex 1

Query "B" has the following information as lost time:

Name Duration
Mark 1
Ted 3
John 5
Eric 6

What I would like to do is overtime minus lost time (A-B) to return the
following:

Mark 0
Ted -1
Bill 2
Alex 1
John -5
Eric -6

I know this should be simple, but I cannot figure this out. When I join, it
obviously loses the people that aren't on both tables. A simple subtract
without a join obviously does not work. And I cannot figure out how to apply
an IIF statement here.

Thanks in advance!
Amin

 

[Amin]

I am using Access 2003, otherwise I guess an OUTER JOIN would work, but I
keep getting an error using it in Jet.

 

[Clifford Bass]

Hi Amin,

What you are describing is a full outer join. Unfortunately Access
does not support that. Fortunately, you can get around it. Try something
like this:

Create a new query (QueryX) that creates a distinct listing of all of
the people you want in your report (select distinct Name from
SomeTableOrQuery). Then use it jointly with your other two queries.

select QueryX.Name, Nz(QueryA.Duration, 0) - Nz(QueryB.Duration, 0) as
Result
from (QueryX left outer join QueryA on QueryA.Name = QueryX.Name) left
outer join QueryB on QueryB.Name = QueryX.Name)

Hope that helps,

Clifford Bass

 

[KARL DEWEY]

Try these two queries --
qryNameList --
SELECT Name, Duration AS Overtime, 0 AS [Lost Time]
FROM QueryA
UNION ALL SELECT Name, 0 AS Overtime, Duration AS [Lost Time]
FROM QueryB;

SELECT Name, Sum(Overtime - [Lost Time] AS SomeName)
FROM qryNameList
GROUP BY Name;

 

[John Spencer]

Do you have a table with all the names in it?

IF so you should be able to left join to the two tables . If not

SELECT [Name]
FROM [QueryA]
UNION
SELECT [Name]
FROM [QueryB]

Now use that saved Union query and your queryA and QueryB to build a query
that looks like:

SELECT U.[Name], [QUeryA].Duration, [QueryB].Duration
, NZ([QUeryA].Duration,0) - NZ([QueryB].Duration,0) As TheDiff
FROM (UnionQuery as U LEFT JOIN [QueryA]
ON U.[Name] = [QueryA].[Name])
LEFT JOIN [QueryB]
ON U.[Name] = [QueryB].[Name]

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County