Re: Repost - help to merge 2 queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi John,

Thanks for the reply. As in my response to Clifford, your solution lended
itself to my working knowlege of SQL+ and, after the slightly arduous task of
renaming some tables / fields, I got it to work based on your
information/guidance.

If you're interested, this is what the final query looks like: -

SELECT
c.DVal,
Results.Associate_Calls_Received,
Results.Associate_Calls_Answered,
Results.Associate_Calls_Abandoned,
Results.Associate_Abandon_Rate,
Results.Associate_ASA,
Results.Answered_Within_90_Secs,
Results.Average_Abandon_Time,
Results.Avg_Talk,
Results.Avg_ACW,
Results.Avg_Hold,
Results.Transfer,
Results.EAHT,
Results.Occupancy
FROM
01_Dates_Table as c LEFT JOIN
(SELECT
a.DVal,
Sum(a.ACD_CALLS+a.ABAN) AS Associate_Calls_Received,
Sum(a.ACD_CALLS) AS Associate_Calls_Answered,
Sum(a.ABAN) AS Associate_Calls_Abandoned,
IIf(Sum(a.ABAN+a.ACD_CALLS)=0,0,Sum(a.ABAN)/Sum(a.ABAN+a.ACD_CALLS)) AS
Associate_Abandon_Rate,
IIf(Sum(a.ACD_CALLS)=0,0,Sum(a.ASA*a.ACD_CALLS)/Sum(a.ACD_CALLS)) AS
Associate_ASA,
IIf(Sum(a.ACD_CALLS)=0,0,Sum(a.ACD1+a.ACD2+a.ACD3+a.ACD4+a.ACD5)/Sum(a.ACD_CALLS)) AS Answered_Within_90_Secs,
IIf(Sum(a.ABN1+a.ABN2+a.ABN3+a.ABN4+a.ABN5+a.ABN6+a.ABN7+a.ABN8+a.ABN9+a.ABN10)=0,0,Sum((a.ABN1+a.ABN2+a.ABN3+a.ABN4+a.ABN5+a.ABN6+a.ABN7+a.ABN8+a.ABN9+a.ABN10)*Avg_ABAN_TIME)/Sum(a.ABN1+a.ABN2+a.ABN3+a.ABN4+a.ABN5+a.ABN6+a.ABN7+a.ABN8+a.ABN9+a.ABN10)) AS Average_Abandon_Time,
IIf(Sum(a.ACD_CALLS)=0,0,Sum(a.ACD)/Sum(a.ACD_CALLS)) AS Avg_Talk,
IIf(Sum(a.ACD_CALLS)=0,0,Sum(a.ACW)/Sum(a.ACD_CALLS)) AS Avg_ACW,
IIf(Sum(a.ACD_CALLS)=0,0,Sum(a.HOLD)/Sum(a.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(a.ACD_CALLS)=0,0,Sum(a.EHT*a.ACD_CALLS)/Sum(a.ACD_CALLS)) AS EAHT,
IIf((Sum(a.Staff)-Sum(a.Aux))=0,0,1-(Sum(a.Avail)/(Sum(a.Staff)-Sum(a.Aux)))) AS Occupancy
FROM
01_Skills_DL_Table As a INNER JOIN
99_Act_Skills_Table as b ON (a.Site = b.Site) AND
(a.Skill = b.Skill) GROUP BY a.DVal) as Results
ON c.DVal=Results.DVal
ORDER BY c.DVal;


Many thanks for your help. It's very much appreciated.

Regards,
--
Mike Peate


"John Spencer" wrote:

IF your table and field names follow the naming conventions (Letters, Numbers
and Underscore characters only) and none of them are reserved words - such as
Date, Time, Name, etc then it is possible to do this all in one query using a
subquery for 1stQuery.

SELECT c.Date, 1stQuery.1stField, 1stQuery.etc...
FROM c LEFT JOIN
(SELECT a.Site, a.DATE, Sum(a.ACD_CALLS), etc...
FROM a INNER JOIN b ON (a.Site = b.Site) AND (a.Skill = b.Skill)
GROUP BY a.Site, a.DATE) as 1stQuery
ON c.Date = 1stQuery.DATE
ORDER BY c.Date;

The restriction on naming is due to the fact that Access (JET) SQL won't allow
square brackets in a sub-query that is used in the FROM clause. So you can
have a field such as [First Name], while you can have a field FirstName.

IF you fully qualify the field names with the TableName.FieldName syntax you
can often (Not always) get away with reserved words as field or table names.
So, C.Date may work, while just Date would require [Date]. And [Date] breaks
the naming convention and in this case the rule of not having square brackets
in the sub-query (in a FROM clause).

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

uk_firebrand wrote:
Hi all,

I've simplified my original post as very long winded - apologies for that;
it was a busy day at work.

I want to run a single query that has inner joins across 2 tables:2 fields
and a left join on 1 table:1 field. Due to an "ambiguous joins" issue when I
attempt to run as a single query, I have had to split this into 2 queries
i.e. inner joins in 1 query, left join in another.

I can perform this type of query in an SQL+ environment however, I'm having
issues porting the same logic to Access and I was hoping someone could assist
in creating a single, merged query.

There are 3 tables involved

a - raw data
b - criteria list for return values where 2 joined fields must be equal
(a.field=b.field)
c - a list of dates in the current year; used as left join on first query to
return all dates from c and the 1st query's results where they exist


The first query pulls back the data I need: -

SELECT a.Site, a.DATE, Sum(a.ACD_CALLS), etc...
FROM a INNER JOIN b ON (a.Site = b.Site) AND (a.Skill = b.Skill)
GROUP BY a.Site, a.DATE
ORDER BY a.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 c.Date, 1stQuery.1stField, 1stQuery.etc...
FROM c LEFT JOIN 1stQuery ON c.Date = 1stQuery.DATE
ORDER BY c.Date;


Is there any way, possibly using a subquery in the first, that I can the
merge the two queries into one?

Many thanks in advance,

.

Relevant Pages

  • Re: CONTAINSTABLE fails on AND NOT
    ... I have not seen this addressed using the CONTAINSTABLE query. ... submission of SQL across the wire, or the use of dynamic SQL on the ... In my case CONTAINSTABLE('table_name',*,'string1 AND NOT string2') does ... none of which are in the 1028 from the first query. ...
    (microsoft.public.sqlserver.fulltext)
  • query to SQL server failed using FreeTDS
    ... I used isql untility to connect to a remote sql server. ... First query was also successful. ...
    (microsoft.public.sqlserver.connect)
  • Re: how do i count consecutive records with a field value >=1
    ... "Michel Walsh" wrote: ... The first query consider for each record how many records in table ... The second query keep only the ranks where bal <0. ... but i don't have a clue how your sql got the results i needed. ...
    (microsoft.public.access.queries)
  • Re: how do i count consecutive records with a field value >=1
    ... The first query consider for each record how many records in table ... The second query keep only the ranks where bal <0. ... but i don't have a clue how your sql got the results i needed. ...
    (microsoft.public.access.queries)
  • Re: SQL Query for unique entries and sums by group
    ... be able to reduce it all to a single query, but I only have Access 97 on ... Create a query that unions your two queries: ... VendorName Parts TotalPurchase ... ***end SQL ...
    (microsoft.public.access.formscoding)