Suffering braindeath on a query 1

[MasterRacker]

I have a flat table with the following fields relating to phone calls:
Dept, User, Direction, Duration
where Direction can be Inbound or Outbound

My desired end result is a single row per user showing:
Dept, User, Total Inbound, Total Inbound Time, Avg. Inbound Duration, Total Outbound, Total Outbound Time, Avg. Outbound Duration

Think it would be more useful down the road, I created 2 summary queries, 1 for each direction showing:
Dept, User, Total Inbound, Total Inbound Time, Avg. Inbound Duration
and
Dept, User, Total Outbound, Total Outbound Time, Avg. Outbound Duration

Both of these work, but I'm suffering a block on how to combine them into a single overall summary. I can't simply join across both without losing records, since any given user may not have calls in both directions in a given time period. I was expecting this to be trivial,but I must be missing something that's staring me in the face.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[FancyPrairie]

What about a Union query?

Select * from Query1
Union Select * from Query2

 

[PHV]

What about something like this ?

SELECT Dept, User
,Sum(IIf(Direction='Inbound',1,0)) AS [Total Inbound]
,Sum(IIf(Direction='Inbound',Duration,0)) AS [Total Inbound Time]
,Sum(IIf(Direction='Inbound',Duration,0))/Sum(IIf(Direction='Inbound',1,Null)) AS [Avg. Inbound Duration]
,Sum(IIf(Direction='Outbound',1,0)) AS [Total Outbound]
,Sum(IIf(Direction='Outbound',Duration,0)) AS [Total Outbound Time]
,Sum(IIf(Direction='Outbound',Duration,0))/Sum(IIf(Direction='Outbound',1,Null)) AS [Avg. Outbound Duration]
FROM [i]yourTable[/i]
GROUP BY Dept, User

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MasterRacker]

The union query gives me two rows per user with the outbound totals in the same columns as the inbound.

PHV's solution seems to give what I need. I hadn't thought of manually calculating everything using the IIFs. Thanks.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]