query upon query

  • Thread starter Thread starter jas0n
  • Start date Start date
J

jas0n

I have a single table of mobile phone data, the dates and times and cost
and duration of each call, etc.

On one query I want to pull out all calls for a particular region that
fall outside of working hours, list them by user and total up the calls.

Would that normally be done by a few seperate queries as I cant seem to
get it all into the one query.

So far, I have a select query to pull the regional data out, then
another one based on that where the criteria is used and then another
one to total it all up.

Doesnt seem efficient to me but I dont know any better at the moment.
 
This query use table with User, Area, Call Date, Duration, and Cost
Call Date is a DateTime field. The query prompts for Area and returns
result of calls before 8 AM and after 5 PM.

SELECT [Mobile Phone].User, Sum([Mobile Phone].Duration) AS SumOfDuration,
Sum([Mobile Phone].Cost) AS SumOfCost, [CALL dATE]-Int([Call Date]) AS X
FROM [Mobile Phone]
WHERE ((([Mobile Phone].Aera)=[Enter Area]))
GROUP BY [Mobile Phone].User, [CALL dATE]-Int([Call Date])
HAVING ((([CALL dATE]-Int([Call Date])) Not Between 0.33333333335759 And
0.70833333335759));
 
One Query should do. since you didn't post your Table details, I can only
guess and the SQL String of the Query should be something like:

(assuming working hours 09:00 to 17:00)

****
SELECT PU.User, Sum(PU.Duration) AS OOHPDuration,
Sum(PU.Cost) AS OOHPCost
FROM [PhoneUsage] AS PU
WHERE ( PU.Region=[Enter Required Region:] )
AND ( TimeValue(PU.CallDateTime) Not
BETWEEN #09:00:00# AND #17:00:00# )
GROUP BY PU.User
****

HTH
Van T. Dinh
MVP (Access)
 
Back
Top