MORE THEN ONE QUERIES-- HELP--

T

TYE

want to group more than one campaign results together.

I have 3 queries for each campaign to get the results then the 4th queries
format some of the fields

What I want like to do is enter the data ones not more then ones if I update
and append.
This is the first query
SELECT dbo_AGTSTATS.AgentID, dbo_Magent.AgentName,
CDbl(Sum((DatePart('h',[Pausetime])*3600)+(DatePart('n',[Pausetime])*60)+DatePart('s',[Pausetime]))/86400)
AS PTime,
CDbl(Sum((DatePart('h',[Waittime])*3600)+(DatePart('n',[Waittime])*60)+DatePart('s',[Waittime]))/86400) AS WTime
FROM dbo_Magent RIGHT JOIN dbo_AGTSTATS ON dbo_Magent.AgentID =
dbo_AGTSTATS.AgentID
WHERE (((dbo_AGTSTATS.Date) Between [Start Date] And [End Date]) AND
((dbo_AGTSTATS.Campaign)=279) AND ((dbo_Magent.AgentID)<>0 And
(dbo_Magent.AgentID) Is Not Null))
GROUP BY dbo_AGTSTATS.AgentID, dbo_Magent.AgentName;

2nd query
SELECT dbo_CALLRSLT.Campaign, dbo_CALLRSLT.AgentID,
Count(dbo_CALLRSLT.UniqueKey) AS NumRecs,
Sum(IIf(dbo_MCALCODE.CountasLead<>0,1,0)) AS NumLeads,
Sum(IIf(dbo_MCALCODE.Presentation<>0,1,0)) AS NumPresentations,
CDbl(Sum((DatePart('h',[CallWrapUpTime])*3600)+(DatePart('n',[CallWrapUpTime])*60)+DatePart('s',[CallWrapUpTime]))/86400)
AS WUTime,
CDbl(Sum(((DatePart('h',[CallCompletionTime])*3600)+(DatePart('n',[CallCompletionTime])*60)+DatePart('s',[CallCompletionTime]))/86400)-(Sum((DatePart('h',[CallWrapUpTime])*3600)+(DatePart('n',[CallWrapUpTime])*60)+DatePart('s',[CallWrapUpTime]))/86400))
AS TTime, Sum(IIf(dbo_MCALCODE.CallResultCode Not In (9,13,22,23,28),1,0)) AS
AgentNumLeads
FROM dbo_CALLRSLT LEFT JOIN dbo_MCALCODE ON dbo_CALLRSLT.CallResultCode =
dbo_MCALCODE.CallResultCode
WHERE (((dbo_CALLRSLT.DateofCall) Between [Start Date] And [End Date]) AND
((dbo_CALLRSLT.Campaign)=279) AND ((dbo_CALLRSLT.AgentID)<>0 And
(dbo_CALLRSLT.AgentID) Is Not Null))
GROUP BY dbo_CALLRSLT.Campaign, dbo_CALLRSLT.AgentID
ORDER BY dbo_CALLRSLT.AgentID;

3rd query
SELECT dbo_CALLRSLT.Campaign, Count(dbo_CALLRSLT.UniqueKey) AS NumDials
FROM dbo_CALLRSLT LEFT JOIN dbo_MCALCODE ON dbo_CALLRSLT.CallResultCode =
dbo_MCALCODE.CallResultCode
WHERE (((dbo_CALLRSLT.DateofCall) Between [Start Date] And [End Date]))
GROUP BY dbo_CALLRSLT.Campaign
HAVING (((dbo_CALLRSLT.Campaign)=279));

4th query
SELECT tblAccumulativeAgtStats.AgentID, tblAccumulativeAgtStats.AgentName AS
Name, tblAccumulativeCallResults.AgentNumLeads AS [Total Connects],
tblAccumulativeCallResults.NumPresentations AS DMCS,
tblAccumulativeCallResults.NumLeads AS Sales,
IIf([DiallerHours1]>0,([NumLeads]/[DiallerHours1]),0) AS SPH,
IIf([DiallerHours1]>0,([NumPresentations]/[DiallerHours1]),0) AS DPH,
(([WTime])*24) AS WaitTime, (([TTime])*24) AS TalkTime, (([WUTime])*24) AS
WrapupTime, (([PTime])*24) AS PauseTime,
(([PTime]+[WTime]+[WUTime]+[TTime])*24) AS ManHours1,
(([WTime]+[WUTime]+[TTime])*24) AS DiallerHours1, TFormat([TTime]) AS [Time
Talk], TFormat([WUTime]) AS [Wrapup Time], TFormat([WTime]) AS [Wait Time],
TFormat([PTime]) AS [Pause Time], TFormat([WUTime]+[WTime]+[TTime]) AS
[Dialler Hours], TFormat([PTime]+[WTime]+[WUTime]+[TTime]) AS [Man Hours]
INTO ColdTeamsAccumulative
FROM (tblAccumulativeAgtStats INNER JOIN tblAccumulativeCallResults ON
tblAccumulativeAgtStats.AgentID = tblAccumulativeCallResults.AgentID) INNER
JOIN tblAccumulativeCallTotals ON tblAccumulativeCallResults.Campaign =
tblAccumulativeCallTotals.Campaign
ORDER BY tblAccumulativeAgtStats.AgentName;

These are the queries that I’m using but this is just for one Campaign when
we work on more then one campaign we would like to have all the result
combined.

I have the above queries for each of the campaign I have tried to update and
append but I have to enter the data in more then ones

Can you please HELP?
 
D

Duane Hookom

Don't use parameter prompts. Use controls on forms to provide your criteria
values.

--
Duane Hookom
MS Access MVP


TYE said:
want to group more than one campaign results together.

I have 3 queries for each campaign to get the results then the 4th queries
format some of the fields

What I want like to do is enter the data ones not more then ones if I
update
and append.
This is the first query
SELECT dbo_AGTSTATS.AgentID, dbo_Magent.AgentName,
CDbl(Sum((DatePart('h',[Pausetime])*3600)+(DatePart('n',[Pausetime])*60)+DatePart('s',[Pausetime]))/86400)
AS PTime,
CDbl(Sum((DatePart('h',[Waittime])*3600)+(DatePart('n',[Waittime])*60)+DatePart('s',[Waittime]))/86400)
AS WTime
FROM dbo_Magent RIGHT JOIN dbo_AGTSTATS ON dbo_Magent.AgentID =
dbo_AGTSTATS.AgentID
WHERE (((dbo_AGTSTATS.Date) Between [Start Date] And [End Date]) AND
((dbo_AGTSTATS.Campaign)=279) AND ((dbo_Magent.AgentID)<>0 And
(dbo_Magent.AgentID) Is Not Null))
GROUP BY dbo_AGTSTATS.AgentID, dbo_Magent.AgentName;

2nd query
SELECT dbo_CALLRSLT.Campaign, dbo_CALLRSLT.AgentID,
Count(dbo_CALLRSLT.UniqueKey) AS NumRecs,
Sum(IIf(dbo_MCALCODE.CountasLead<>0,1,0)) AS NumLeads,
Sum(IIf(dbo_MCALCODE.Presentation<>0,1,0)) AS NumPresentations,
CDbl(Sum((DatePart('h',[CallWrapUpTime])*3600)+(DatePart('n',[CallWrapUpTime])*60)+DatePart('s',[CallWrapUpTime]))/86400)
AS WUTime,
CDbl(Sum(((DatePart('h',[CallCompletionTime])*3600)+(DatePart('n',[CallCompletionTime])*60)+DatePart('s',[CallCompletionTime]))/86400)-(Sum((DatePart('h',[CallWrapUpTime])*3600)+(DatePart('n',[CallWrapUpTime])*60)+DatePart('s',[CallWrapUpTime]))/86400))
AS TTime, Sum(IIf(dbo_MCALCODE.CallResultCode Not In (9,13,22,23,28),1,0))
AS
AgentNumLeads
FROM dbo_CALLRSLT LEFT JOIN dbo_MCALCODE ON dbo_CALLRSLT.CallResultCode =
dbo_MCALCODE.CallResultCode
WHERE (((dbo_CALLRSLT.DateofCall) Between [Start Date] And [End Date]) AND
((dbo_CALLRSLT.Campaign)=279) AND ((dbo_CALLRSLT.AgentID)<>0 And
(dbo_CALLRSLT.AgentID) Is Not Null))
GROUP BY dbo_CALLRSLT.Campaign, dbo_CALLRSLT.AgentID
ORDER BY dbo_CALLRSLT.AgentID;

3rd query
SELECT dbo_CALLRSLT.Campaign, Count(dbo_CALLRSLT.UniqueKey) AS NumDials
FROM dbo_CALLRSLT LEFT JOIN dbo_MCALCODE ON dbo_CALLRSLT.CallResultCode =
dbo_MCALCODE.CallResultCode
WHERE (((dbo_CALLRSLT.DateofCall) Between [Start Date] And [End Date]))
GROUP BY dbo_CALLRSLT.Campaign
HAVING (((dbo_CALLRSLT.Campaign)=279));

4th query
SELECT tblAccumulativeAgtStats.AgentID, tblAccumulativeAgtStats.AgentName
AS
Name, tblAccumulativeCallResults.AgentNumLeads AS [Total Connects],
tblAccumulativeCallResults.NumPresentations AS DMCS,
tblAccumulativeCallResults.NumLeads AS Sales,
IIf([DiallerHours1]>0,([NumLeads]/[DiallerHours1]),0) AS SPH,
IIf([DiallerHours1]>0,([NumPresentations]/[DiallerHours1]),0) AS DPH,
(([WTime])*24) AS WaitTime, (([TTime])*24) AS TalkTime, (([WUTime])*24) AS
WrapupTime, (([PTime])*24) AS PauseTime,
(([PTime]+[WTime]+[WUTime]+[TTime])*24) AS ManHours1,
(([WTime]+[WUTime]+[TTime])*24) AS DiallerHours1, TFormat([TTime]) AS
[Time
Talk], TFormat([WUTime]) AS [Wrapup Time], TFormat([WTime]) AS [Wait
Time],
TFormat([PTime]) AS [Pause Time], TFormat([WUTime]+[WTime]+[TTime]) AS
[Dialler Hours], TFormat([PTime]+[WTime]+[WUTime]+[TTime]) AS [Man Hours]
INTO ColdTeamsAccumulative
FROM (tblAccumulativeAgtStats INNER JOIN tblAccumulativeCallResults ON
tblAccumulativeAgtStats.AgentID = tblAccumulativeCallResults.AgentID)
INNER
JOIN tblAccumulativeCallTotals ON tblAccumulativeCallResults.Campaign =
tblAccumulativeCallTotals.Campaign
ORDER BY tblAccumulativeAgtStats.AgentName;

These are the queries that I'm using but this is just for one Campaign
when
we work on more then one campaign we would like to have all the result
combined.

I have the above queries for each of the campaign I have tried to update
and
append but I have to enter the data in more then ones

Can you please HELP?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filling in null data for Pivotchart 0
Access Bug for comlex queries? 2
Help to merge 2 access queries 1
combining queries into 1 8
Apparently Bogus "could refer to more than one. 9
total time 4
Need to Make Query Run Faster - SLQ? 3
Unions and Joins don't seem to work, any suggestions? 0

Top