U
uk_firebrand
Hi all,
I currently run 2 independent queries due to an "ambiguous joins" issue when
I attempt to run as a single query and I was hoping someone could assist in
creating a single, merged query.
The first query pulls back the data I need: -
SELECT [01 - Skills DL Table].Site, [01 - Skills DL Table].DATE, Sum([01 -
Skills DL Table].[ACD CALLS]) AS [Associate Calls Answered], Sum([01 - Skills
DL Table].ABAN) AS [Associate Calls Abandoned], IIf(Sum([ACD
CALLS])=0,0,Sum([ASA]*[ACD CALLS])/Sum([ACD CALLS])) AS [Associate ASA],
Sum([ACD1]+[ACD2]+[ACD3]+[ACD4]+[ACD5]) AS NumAns90Secs,
Sum([ABN1]+[ABN2]+[ABN3]+[ABN4]+[ABN5]+[ABN6]+[ABN7]+[ABN8]+[ABN9]+[ABN10])
AS TotAban,
Sum(([ABN1]+[ABN2]+[ABN3]+[ABN4]+[ABN5]+[ABN6]+[ABN7]+[ABN8]+[ABN9]+[ABN10])*[Avg
ABAN TIME]) AS TotAbandTime, IIf(Sum([ACD CALLS])=0,0,Sum([ACD])/Sum([ACD
CALLS])) AS [Avg Talk], IIf(Sum([ACD CALLS])=0,0,Sum([ACW])/Sum([ACD CALLS]))
AS [Avg ACW], IIf(Sum([ACD CALLS])=0,0,Sum([HOLD])/Sum([ACD CALLS])) AS [Avg
Hold], IIf([EAHT]-[Avg Talk]-[Avg ACW]-[Avg Hold]<0,0,[EAHT]-[Avg Talk]-[Avg
ACW]-[Avg Hold]) AS Transfer, IIf(Sum([ACD CALLS])=0,0,Sum([EHT]*[ACD
CALLS])/Sum([ACD CALLS])) AS EAHT, Sum([01 - Skills DL Table].AUX) AS
SumOfAUX, Sum([01 - Skills DL Table].AVAIL) AS SumOfAVAIL, Sum([01 - Skills
DL Table].STAFF) AS SumOfSTAFF
FROM [01 - Skills DL Table] INNER JOIN [99 - Activation Skills Table] ON
([01 - Skills DL Table].Site = [99 - Activation Skills Table].Site) AND ([01
- Skills DL Table].Skill = [99 - Activation Skills Table].Skill)
GROUP BY [01 - Skills DL Table].Site, [01 - Skills DL Table].DATE
ORDER BY [01 - Skills DL Table].DATE;
The second query references the first with a LEFT join on a table containing
all the dates in the current year and, essentially, will pull back all those
dates and any values from the first query where the dates match: -
SELECT [01 - Dates Table].Date, [zDL_Ch_Sk Query].Site, [zDL_Ch_Sk
Query].[Associate Calls Answered], [zDL_Ch_Sk Query].[Associate Calls
Abandoned], [zDL_Ch_Sk Query].[Associate ASA], [zDL_Ch_Sk
Query].NumAns90Secs, [zDL_Ch_Sk Query].TotAban, [zDL_Ch_Sk
Query].TotAbandTime, [zDL_Ch_Sk Query].[Avg Talk], [zDL_Ch_Sk Query].[Avg
ACW], [zDL_Ch_Sk Query].[Avg Hold], [zDL_Ch_Sk Query].Transfer, [zDL_Ch_Sk
Query].EAHT, [zDL_Ch_Sk Query].SumOfAUX, [zDL_Ch_Sk Query].SumOfAVAIL,
[zDL_Ch_Sk Query].SumOfSTAFF
FROM [01 - Dates Table] LEFT JOIN [zDL_Ch_Sk Query] ON [01 - Dates
Table].Date = [zDL_Ch_Sk Query].DATE
ORDER BY [01 - Dates Table].Date;
Is there any way, possibly using a subquery in the first, that I can combine
the merge the two queries? I can do it in SQL but have no idea how to
translate into Access SQL.
Many thanks in advance,
I currently run 2 independent queries due to an "ambiguous joins" issue when
I attempt to run as a single query and I was hoping someone could assist in
creating a single, merged query.
The first query pulls back the data I need: -
SELECT [01 - Skills DL Table].Site, [01 - Skills DL Table].DATE, Sum([01 -
Skills DL Table].[ACD CALLS]) AS [Associate Calls Answered], Sum([01 - Skills
DL Table].ABAN) AS [Associate Calls Abandoned], IIf(Sum([ACD
CALLS])=0,0,Sum([ASA]*[ACD CALLS])/Sum([ACD CALLS])) AS [Associate ASA],
Sum([ACD1]+[ACD2]+[ACD3]+[ACD4]+[ACD5]) AS NumAns90Secs,
Sum([ABN1]+[ABN2]+[ABN3]+[ABN4]+[ABN5]+[ABN6]+[ABN7]+[ABN8]+[ABN9]+[ABN10])
AS TotAban,
Sum(([ABN1]+[ABN2]+[ABN3]+[ABN4]+[ABN5]+[ABN6]+[ABN7]+[ABN8]+[ABN9]+[ABN10])*[Avg
ABAN TIME]) AS TotAbandTime, IIf(Sum([ACD CALLS])=0,0,Sum([ACD])/Sum([ACD
CALLS])) AS [Avg Talk], IIf(Sum([ACD CALLS])=0,0,Sum([ACW])/Sum([ACD CALLS]))
AS [Avg ACW], IIf(Sum([ACD CALLS])=0,0,Sum([HOLD])/Sum([ACD CALLS])) AS [Avg
Hold], IIf([EAHT]-[Avg Talk]-[Avg ACW]-[Avg Hold]<0,0,[EAHT]-[Avg Talk]-[Avg
ACW]-[Avg Hold]) AS Transfer, IIf(Sum([ACD CALLS])=0,0,Sum([EHT]*[ACD
CALLS])/Sum([ACD CALLS])) AS EAHT, Sum([01 - Skills DL Table].AUX) AS
SumOfAUX, Sum([01 - Skills DL Table].AVAIL) AS SumOfAVAIL, Sum([01 - Skills
DL Table].STAFF) AS SumOfSTAFF
FROM [01 - Skills DL Table] INNER JOIN [99 - Activation Skills Table] ON
([01 - Skills DL Table].Site = [99 - Activation Skills Table].Site) AND ([01
- Skills DL Table].Skill = [99 - Activation Skills Table].Skill)
GROUP BY [01 - Skills DL Table].Site, [01 - Skills DL Table].DATE
ORDER BY [01 - Skills DL Table].DATE;
The second query references the first with a LEFT join on a table containing
all the dates in the current year and, essentially, will pull back all those
dates and any values from the first query where the dates match: -
SELECT [01 - Dates Table].Date, [zDL_Ch_Sk Query].Site, [zDL_Ch_Sk
Query].[Associate Calls Answered], [zDL_Ch_Sk Query].[Associate Calls
Abandoned], [zDL_Ch_Sk Query].[Associate ASA], [zDL_Ch_Sk
Query].NumAns90Secs, [zDL_Ch_Sk Query].TotAban, [zDL_Ch_Sk
Query].TotAbandTime, [zDL_Ch_Sk Query].[Avg Talk], [zDL_Ch_Sk Query].[Avg
ACW], [zDL_Ch_Sk Query].[Avg Hold], [zDL_Ch_Sk Query].Transfer, [zDL_Ch_Sk
Query].EAHT, [zDL_Ch_Sk Query].SumOfAUX, [zDL_Ch_Sk Query].SumOfAVAIL,
[zDL_Ch_Sk Query].SumOfSTAFF
FROM [01 - Dates Table] LEFT JOIN [zDL_Ch_Sk Query] ON [01 - Dates
Table].Date = [zDL_Ch_Sk Query].DATE
ORDER BY [01 - Dates Table].Date;
Is there any way, possibly using a subquery in the first, that I can combine
the merge the two queries? I can do it in SQL but have no idea how to
translate into Access SQL.
Many thanks in advance,