Repost - help to merge 2 queries

[uk_firebrand]

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,

 

[Clifford Bass]

Hi Mike,

You actually probably need to make them all left joins, even though
the one between a and b is really an inner join. Try the following
(untested):

SELECT c.Date, a.Site, Sum(a.ACD_CALLS)
FROM (c LEFT JOIN a ON c.Date = a.DATE) LEFT JOIN b ON b.Site = a.Site and
b.Skill = a.Skill
GROUP BY c.Date, a.Site;

Note the reversal of the grouping. In theory it allows you to drop the
order by clause when you want the information sorted in the same order as the
grouping.

Clifford Bass

 

[uk_firebrand]

Thanks for your reply, Clifford - most appreciated.

It sounds counterintuitive but I will give it a go when I return to work in
an hour or so. Will post again to confirm if worked ok.

 

[uk_firebrand]

Hi again,

I tried your method, Clifford, and unfortunately it doesn't work. Having
left joins on table b brings back results for the entire data set, not just
for the skill/site criteria combo concerned.

Any further suggestions would be welcomed?

Thanks,

 

[John Spencer]

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

 

[Clifford Bass]

Hi,

Hmmm... I think I am missing something here. Could you provide some
sample data for each of the tables and what you would expect as a result?

Thanks,

Clifford Bass

 

[uk_firebrand]

Hi Clifford,

Again, appreciate the reply. I am not in work but sample data / construct as
follows: -

Table A (Raw Data)
Date Site Skill ASA Calls
01/01/2008 1 1 1 10
01/01/2008 1 2 1 10
01/01/2008 1 3 1 10
01/01/2008 2 1 1 10
01/01/2008 2 2 1 10
01/01/2008 2 3 1 10
01/01/2008 3 1 1 10
01/01/2008 3 2 1 10
01/01/2008 3 3 1 10

Table B (Criteria i.e. a department)
Inner Joins on Table A
Site Skill
1 1
1 2
2 1
3 2
3 3

Table C (Dummy Table)
Left Join Table A
Date
01/01/2008
02/01/2008
etc
etc
etc

Expected results are as seen when using 2 separate queries i.e: -
1st query with inner joins between table A and B (Skill/Site Fields)
2nd query with left join on Table C and 1st query (Date field)

Results Query
Date ASA Calls
01/01/2008 1 50
02/01/2008 Blank Blank
03/01/2008 etc etc
etc etc etc


Date - All dates from Table C i.e. left join on Table A to show results
where exist, nulls if no results for a date
Results where Skill & Site in Table A/B match (inner joins)

Calculated fields: ASA - Equivalent of SumProduct(ASA,Calls)/Sum(Calls)
Calls - Sum(Calls)

I hope the above makes sense.

I've quickly read the reply from John Spencer and what he has written lends
itself to my knowledge of SQL - with a bit of MS syntax shenanigans thrown in
there to confuse us poor souls - but, as I said, I am not in work to test
this. I will do tomorrow. Thanks John!!

Thanks again - like everyone else in these forums, I really appreciate the
generosity of all the people who donate their time / expertise in answering
these posts.

Best regards to you all. . .

 

[Clifford Bass]

Hi Mike,

You are welcome. Here is something to try:

SELECT C.Date,
Sum([A].[ASA]*IIf(IsNull(.[Site]),Null,[A].[Calls]))/Sum(IIf(IsNull(.[Site]),Null,[A].[Calls]))
AS ASA, Sum(IIf(IsNull(.[Site]),Null,[A].[Calls])) AS Calls
FROM (C LEFT JOIN A ON C.Date = A.Date) LEFT JOIN B ON (A.Site = B.Site) AND
(A.Skill = B.Skill)
GROUP BY C.Date;

I will be interested if it works for more than the test data. The key
seems to be to do both of the left joins and to check the value of B.Site (or
B.Skill) while doing the summing in order to tell it which values to
include/exclude.

Nice question!

Clifford Bass

 

[uk_firebrand]

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

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

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,

 

[uk_firebrand]

Hi again,

I thought I would post a quick update to this just in case anyone uses a
similar method then links to Access as an external data source in Excel.

Whilst the method works fine in general, if there is a Where clause in the
Access query, Excel (MS Query) will bomb out and complain of "Too Few
Parameters: Expected <n>". This is the case even if you use a condition in a
calculated field. It's a bit odd as the Where clause / calculated field
conditions I attempted to use were hard-coded and should not even have been a
consideration; but there you have it.

If you need to use a Where clause in this circumstance, make sure the
field/s you are setting the condition/s against is/are visible (selected in
the outer query) to MS Query and set the condition/s there, rather than in
the original Access query.

Hope this helps,