How do you ignore entire tables in an Inner Join Query?

Martin Prunty wrote:

I’m trying to build an application that analyzes up to ten (10) tables that
contain information about unique types of telephone calls that are received
in half-hour increments throughout the day. Each table contains the same
information about calls (and identical fields) including how many calls are
received of each type every half-hour, the average length of calls in
seconds, etc. For each table, I have also created a query that performs
certain calculations on each table.

I’ve built another query that combines each of the other queries and
performs some additional calculations on them. When I have data in all ten
tables, the application works as designed.

When I have data in less than ten (10) tables, the last query doesn’t run
because I haven’t been able to figure out how to include a null statement in
case 1 or more of the other ten queries is empty. My question is “What do I
need to change in the SQL code to ignore any one of the ten queries if some
of the ten (10) tables are empty?”

Following is the SQL code in its current form for the query in question.
Thank you in advance for your assistance.

Martin

SELECT [Skill01 Query].RecordID, [Skill01 Query].CallDate, [Skill01
Query].Department, [Skill01 Query].Skill, [Skill01 Query].Interval, [Skill01
Query]![IncCalls]+[Skill02Query]![IncCalls]+[Skill03Query]![IncCalls]+[Skill04Query]![IncCalls]+[Skill05Query]![IncCalls]+[Skill06Query]![IncCalls]+[Skill07Query]![IncCalls]+[Skill08Query]![IncCalls]+[Skill10Query]![IncCalls]
AS CombinedCalls, [Skill01
Query]![ICallLoad]+[Skill02Query]![ICallLoad]+[Skill03Query]![ICallLoad]+[Skill04Query]![ICallLoad]+[Skill05Query]![ICallLoad]+[Skill06Query]![ICallLoad]+[Skill07Query]![ICallLoad]+[Skill08Query]![ICallLoad]+[Skill09Query]![ICallLoad]+[Skill10Query]![ICallLoad]
AS ILoad, [Skill01
Query]![OCallLoad]+[Skill02Query]![OCallLoad]+[Skill03Query]![OCallLoad]+[Skill04Query]![OCallLoad]+[Skill05Query]![OCallLoad]+[Skill06Query]![OCallLoad]+[Skill07Query]![OCallLoad]+[Skill08Query]![OCallLoad]+[Skill09Query]![OCallLoad]+[Skill10Query]![OCallLoad]
AS OLoad, [Skill01
Query]![SeatedAgents]+[Skill02Query]![SeatedAgents]+[Skill03Query]![SeatedAgents]+[Skill04Query]![SeatedAgents]+[Skill05Query]![SeatedAgents]+[Skill06Query]![SeatedAgents]+[Skill07Query]![SeatedAgents]+[Skill08Query]![SeatedAgents]+[Skill09Query]![SeatedAgents]+[Skill10Query]![SeatedAgents]
AS SeatedAgents,
Agents([SLO]![ServiceLevelPercent],[SLO]![ServiceTime],[CombinedCalls]*2,([ILoad]+[OLoad])/[CombinedCalls]) AS CombinedAgents
FROM SLO, (((((((([Skill01 Query] INNER JOIN Skill02Query ON [Skill01
Query].RecordID = Skill02Query.RecordID) INNER JOIN Skill03Query ON
Skill02Query.RecordID = Skill03Query.RecordID) INNER JOIN Skill04Query ON
Skill03Query.RecordID = Skill04Query.RecordID) INNER JOIN Skill05Query ON
Skill04Query.RecordID = Skill05Query.RecordID) INNER JOIN Skill06Query ON
Skill05Query.RecordID = Skill06Query.RecordID) INNER JOIN Skill07Query ON
Skill06Query.RecordID = Skill07Query.RecordID) INNER JOIN Skill08Query ON
Skill07Query.RecordID = Skill08Query.RecordID) INNER JOIN Skill09Query ON
Skill08Query.RecordID = Skill09Query.RecordID) INNER JOIN Skill10Query ON
Skill09Query.RecordID = Skill10Query.RecordID;


You need to use LEFT JOIN instead of INNER JOIN.

Also, it the first table in the join list might not have all
possible record IDs, add a table the does and put it first
in the list of joins.

--
Marsh
MVP [MS Access]

Thanks for your response. In my original query, the Inner Join function was
addressed in the following SQL Code was:

FROM SLO, (((((((([Skill01 Query] INNER JOIN Skill02Query ON [Skill01
Query].RecordID = Skill02Query.RecordID) INNER JOIN Skill03Query ON
Skill02Query.RecordID = Skill03Query.RecordID) INNER JOIN Skill04Query ON
Skill03Query.RecordID = Skill04Query.RecordID) INNER JOIN Skill05Query ON
Skill04Query.RecordID = Skill05Query.RecordID) INNER JOIN Skill06Query ON
Skill05Query.RecordID = Skill06Query.RecordID) INNER JOIN Skill07Query ON
Skill06Query.RecordID = Skill07Query.RecordID) INNER JOIN Skill08Query ON
Skill07Query.RecordID = Skill08Query.RecordID) INNER JOIN Skill09Query ON
Skill08Query.RecordID = Skill09Query.RecordID) INNER JOIN Skill10Query ON
Skill09Query.RecordID = Skill10Query.RecordID;

I've tried replacing "inner join" with "left join" in my query, but that
doesn't work. It's clear that I don't understand how to use left join when
there are multiple queries being joined. Can you show me how to change this
SQL code to replace the "inner join" statements with the Left Join statements
with these ten queries?

Thanks for your assistance.

"Marshall Barton" wrote:

Martin Prunty wrote:

I’m trying to build an application that analyzes up to ten (10) tables that
contain information about unique types of telephone calls that are received
in half-hour increments throughout the day. Each table contains the same
information about calls (and identical fields) including how many calls are
received of each type every half-hour, the average length of calls in
seconds, etc. For each table, I have also created a query that performs
certain calculations on each table.

I’ve built another query that combines each of the other queries and
performs some additional calculations on them. When I have data in all ten
tables, the application works as designed.

When I have data in less than ten (10) tables, the last query doesn’t run
because I haven’t been able to figure out how to include a null statement in
case 1 or more of the other ten queries is empty. My question is “What do I
need to change in the SQL code to ignore any one of the ten queries if some
of the ten (10) tables are empty?�

Following is the SQL code in its current form for the query in question.
Thank you in advance for your assistance.

Martin

SELECT [Skill01 Query].RecordID, [Skill01 Query].CallDate, [Skill01
Query].Department, [Skill01 Query].Skill, [Skill01 Query].Interval, [Skill01
Query]![IncCalls]+[Skill02Query]![IncCalls]+[Skill03Query]![IncCalls]+[Skill04Query]![IncCalls]+[Skill05Query]![IncCalls]+[Skill06Query]![IncCalls]+[Skill07Query]![IncCalls]+[Skill08Query]![IncCalls]+[Skill10Query]![IncCalls]
AS CombinedCalls, [Skill01
Query]![ICallLoad]+[Skill02Query]![ICallLoad]+[Skill03Query]![ICallLoad]+[Skill04Query]![ICallLoad]+[Skill05Query]![ICallLoad]+[Skill06Query]![ICallLoad]+[Skill07Query]![ICallLoad]+[Skill08Query]![ICallLoad]+[Skill09Query]![ICallLoad]+[Skill10Query]![ICallLoad]
AS ILoad, [Skill01
Query]![OCallLoad]+[Skill02Query]![OCallLoad]+[Skill03Query]![OCallLoad]+[Skill04Query]![OCallLoad]+[Skill05Query]![OCallLoad]+[Skill06Query]![OCallLoad]+[Skill07Query]![OCallLoad]+[Skill08Query]![OCallLoad]+[Skill09Query]![OCallLoad]+[Skill10Query]![OCallLoad]
AS OLoad, [Skill01
Query]![SeatedAgents]+[Skill02Query]![SeatedAgents]+[Skill03Query]![SeatedAgents]+[Skill04Query]![SeatedAgents]+[Skill05Query]![SeatedAgents]+[Skill06Query]![SeatedAgents]+[Skill07Query]![SeatedAgents]+[Skill08Query]![SeatedAgents]+[Skill09Query]![SeatedAgents]+[Skill10Query]![SeatedAgents]
AS SeatedAgents,
Agents([SLO]![ServiceLevelPercent],[SLO]![ServiceTime],[CombinedCalls]*2,([ILoad]+[OLoad])/[CombinedCalls]) AS CombinedAgents
FROM SLO, (((((((([Skill01 Query] INNER JOIN Skill02Query ON [Skill01
Query].RecordID = Skill02Query.RecordID) INNER JOIN Skill03Query ON
Skill02Query.RecordID = Skill03Query.RecordID) INNER JOIN Skill04Query ON
Skill03Query.RecordID = Skill04Query.RecordID) INNER JOIN Skill05Query ON
Skill04Query.RecordID = Skill05Query.RecordID) INNER JOIN Skill06Query ON
Skill05Query.RecordID = Skill06Query.RecordID) INNER JOIN Skill07Query ON
Skill06Query.RecordID = Skill07Query.RecordID) INNER JOIN Skill08Query ON
Skill07Query.RecordID = Skill08Query.RecordID) INNER JOIN Skill09Query ON
Skill08Query.RecordID = Skill09Query.RecordID) INNER JOIN Skill10Query ON
Skill09Query.RecordID = Skill10Query.RecordID;


You need to use LEFT JOIN instead of INNER JOIN.

Also, it the first table in the join list might not have all
possible record IDs, add a table the does and put it first
in the list of joins.

--
Marsh
MVP [MS Access]
.

"that doesn't work" dosen't give me a clue about what it did
or why that isn't what you want.

Do you have a table with a field for all the different
RecordID values?

What are these RecordID values?

What the bleep is the SLO table/query?

Why is there a space in the name Skill01 Query?
--
Marsh
MVP [MS Access]


Martin Prunty wrote:
In my original query, the Inner Join function was
addressed in the following SQL Code was:

FROM SLO, (((((((([Skill01 Query] INNER JOIN Skill02Query ON [Skill01
Query].RecordID = Skill02Query.RecordID) INNER JOIN Skill03Query ON
Skill02Query.RecordID = Skill03Query.RecordID) INNER JOIN Skill04Query ON
Skill03Query.RecordID = Skill04Query.RecordID) INNER JOIN Skill05Query ON
Skill04Query.RecordID = Skill05Query.RecordID) INNER JOIN Skill06Query ON
Skill05Query.RecordID = Skill06Query.RecordID) INNER JOIN Skill07Query ON
Skill06Query.RecordID = Skill07Query.RecordID) INNER JOIN Skill08Query ON
Skill07Query.RecordID = Skill08Query.RecordID) INNER JOIN Skill09Query ON
Skill08Query.RecordID = Skill09Query.RecordID) INNER JOIN Skill10Query ON
Skill09Query.RecordID = Skill10Query.RecordID;

I've tried replacing "inner join" with "left join" in my query, but that
doesn't work. It's clear that I don't understand how to use left join when
there are multiple queries being joined. Can you show me how to change this
SQL code to replace the "inner join" statements with the Left Join statements
with these ten queries?


"Marshall Barton" wrote:
Martin Prunty wrote:
I’m trying to build an application that analyzes up to ten (10) tables that
contain information about unique types of telephone calls that are received
in half-hour increments throughout the day. Each table contains the same
information about calls (and identical fields) including how many calls are
received of each type every half-hour, the average length of calls in
seconds, etc. For each table, I have also created a query that performs
certain calculations on each table.

Sorry about that. As you have probably figured out, I'm not an Access guru
and I apologize if my note didn't give you adequate information.

My application is designed to combine tables of call information for up to
ten groups of calls. Instead of simply combining the fields, it adds records
like call volume together, or averages the average length of calls, then
performs certain other calculations. Essentially, it is used to calculate
how many staff are required to handle calls for each of the independent
groups, then performs another calculation on how many staff are required if
each group, of up to ten, were combined into a single group.

A query was created to combine and calculate data in each of the ten (10)
tables. Then another query, the one in question with the original SQL code I
provided, was created to combine the other queries. When I used an inner
join, this new query works properly, but only when there is data in each of
the ten (10) tables. However, when I have fewer than ten (10) tables, it
doesn't work because of the Inner Join.

You had suggested in your earlier post that I should use a left join, but I
haven't been able to figure out how to write the code for a left join when
there are ten (10) queries to join. I tried replacing all of the Inner Join
entries with a Left Join in the SQL code, but that doesn't work. I've also
looked all over the Microsoft site to try to find an example of a left join
that combines multiple tables or queries, but I can only find examples that
combine 2 tables or queries.

RecordID

The tool I'm building will be used for a variety of clients, so I plan to
add records to tables, then delete them and import new data for different
clients. The reason I created a RecordID field in these records is that I
found that the when I used the primary key field, the numbers don't restart
at 1 after you delete records and import new ones, and therefore the
relationships I had established didn't work. The RecordID field is being
used to create a common record for each of the files so the relationship can
be easily established. RecordID fields always start with 1 and align the
time of day calls are received in each table every time a new set of records
is imported so the relationship between tables will work.

SLO Table

The SLO table is simply a table that defines a parameter used in the
calculations for each of the tables. The same SLO values apply to every
table the same way.

The space in the Skill01 query was a typo. It doesn't actually have a space.

Now that I've provided you with more information, can you please steer me in
the right direction? I'm at a loss for knowing how to change the SQL code
from an inner join to a left join so that any combination of tables, up to
ten, can be combined regardless of whether there is data in each. Data will
always be added beginning with Skill01, then Skill02, etc. up to Skill10.
Therefore, if only two tables have data, for example, they will be entered in
Skill01 and Skill02.

Here's the complete SQL code showing the Inner Join. Thank you in advance
for your assistance.

SELECT [Skill01 Query].RecordID, [Skill01 Query].CallDate, [Skill01
Query].Department, [Skill01 Query].Skill, [Skill01 Query].Interval, [Skill01
Query]![IncCalls]+[Skill02Query]![IncCalls]+[Skill03Query]![IncCalls]+[Skill04Query]![IncCalls]+[Skill05Query]![IncCalls]+[Skill06Query]![IncCalls]+[Skill07Query]![IncCalls]+[Skill08Query]![IncCalls]+[Skill10Query]![IncCalls]
AS CombinedCalls, [Skill01
Query]![ICallLoad]+[Skill02Query]![ICallLoad]+[Skill03Query]![ICallLoad]+[Skill04Query]![ICallLoad]+[Skill05Query]![ICallLoad]+[Skill06Query]![ICallLoad]+[Skill07Query]![ICallLoad]+[Skill08Query]![ICallLoad]+[Skill09Query]![ICallLoad]+[Skill10Query]![ICallLoad]
AS ILoad, [Skill01
Query]![OCallLoad]+[Skill02Query]![OCallLoad]+[Skill03Query]![OCallLoad]+[Skill04Query]![OCallLoad]+[Skill05Query]![OCallLoad]+[Skill06Query]![OCallLoad]+[Skill07Query]![OCallLoad]+[Skill08Query]![OCallLoad]+[Skill09Query]![OCallLoad]+[Skill10Query]![OCallLoad]
AS OLoad, [Skill01
Query]![SeatedAgents]+[Skill02Query]![SeatedAgents]+[Skill03Query]![SeatedAgents]+[Skill04Query]![SeatedAgents]+[Skill05Query]![SeatedAgents]+[Skill06Query]![SeatedAgents]+[Skill07Query]![SeatedAgents]+[Skill08Query]![SeatedAgents]+[Skill09Query]![SeatedAgents]+[Skill10Query]![SeatedAgents]
AS SeatedAgents,
Agents([SLO]![ServiceLevelPercent],[SLO]![ServiceTime],[CombinedCalls]*2,([ILoad]+[OLoad])/[CombinedCalls]) AS CombinedAgents
FROM SLO, (((((((([Skill01 Query] INNER JOIN Skill02Query ON [Skill01
Query].RecordID=Skill02Query.RecordID) INNER JOIN Skill03Query ON
Skill02Query.RecordID=Skill03Query.RecordID) INNER JOIN Skill04Query ON
Skill03Query.RecordID=Skill04Query.RecordID) INNER JOIN Skill05Query ON
Skill04Query.RecordID=Skill05Query.RecordID) INNER JOIN Skill06Query ON
Skill05Query.RecordID=Skill06Query.RecordID) INNER JOIN Skill07Query ON
Skill06Query.RecordID=Skill07Query.RecordID) INNER JOIN Skill08Query ON
Skill07Query.RecordID=Skill08Query.RecordID) INNER JOIN Skill09Query ON
Skill08Query.RecordID=Skill09Query.RecordID) INNER JOIN Skill10Query ON
Skill09Query.RecordID=Skill10Query.RecordID;






"Marshall Barton" wrote:

"that doesn't work" dosen't give me a clue about what it did
or why that isn't what you want.

Do you have a table with a field for all the different
RecordID values?

What are these RecordID values?

What the bleep is the SLO table/query?

Why is there a space in the name Skill01 Query?
--
Marsh
MVP [MS Access]


Martin Prunty wrote:
In my original query, the Inner Join function was
addressed in the following SQL Code was:

FROM SLO, (((((((([Skill01 Query] INNER JOIN Skill02Query ON [Skill01
Query].RecordID = Skill02Query.RecordID) INNER JOIN Skill03Query ON
Skill02Query.RecordID = Skill03Query.RecordID) INNER JOIN Skill04Query ON
Skill03Query.RecordID = Skill04Query.RecordID) INNER JOIN Skill05Query ON
Skill04Query.RecordID = Skill05Query.RecordID) INNER JOIN Skill06Query ON
Skill05Query.RecordID = Skill06Query.RecordID) INNER JOIN Skill07Query ON
Skill06Query.RecordID = Skill07Query.RecordID) INNER JOIN Skill08Query ON
Skill07Query.RecordID = Skill08Query.RecordID) INNER JOIN Skill09Query ON
Skill08Query.RecordID = Skill09Query.RecordID) INNER JOIN Skill10Query ON
Skill09Query.RecordID = Skill10Query.RecordID;

I've tried replacing "inner join" with "left join" in my query, but that
doesn't work. It's clear that I don't understand how to use left join when
there are multiple queries being joined. Can you show me how to change this
SQL code to replace the "inner join" statements with the Left Join statements
with these ten queries?


"Marshall Barton" wrote:
Martin Prunty wrote:
I’m trying to build an application that analyzes up to ten (10) tables that
contain information about unique types of telephone calls that are received
in half-hour increments throughout the day. Each table contains the same
information about calls (and identical fields) including how many calls are
received of each type every half-hour, the average length of calls in
seconds, etc. For each table, I have also created a query that performs
certain calculations on each table.
.

On Sun, 18 Apr 2010 14:49:01 -0700, Martin Prunty
wrote:

Thanks for your help and patience. I think I understand your comments and
this time I'll be brief.

I've changed the query using the Access Query Builder creating the Left
Joins as you suggested using "Skill01Query" as the master. Here's that part
of the code:

FROM SLO, ((((((((Skill01Query LEFT JOIN Skill02Query ON
Skill01Query.RecordID = Skill02Query.RecordID) LEFT JOIN Skill03Query ON
Skill01Query.RecordID = Skill03Query.RecordID) LEFT JOIN Skill04Query ON
Skill01Query.RecordID = Skill04Query.RecordID) LEFT JOIN Skill05Query ON
Skill01Query.RecordID = Skill05Query.RecordID) LEFT JOIN Skill06Query ON
Skill01Query.RecordID = Skill06Query.RecordID) LEFT JOIN Skill07Query ON
Skill01Query.RecordID = Skill07Query.RecordID) LEFT JOIN Skill08Query ON
Skill01Query.RecordID = Skill08Query.RecordID) LEFT JOIN Skill09Query ON
Skill01Query.RecordID = Skill09Query.RecordID) LEFT JOIN Skill10Query ON
Skill01Query.RecordID = Skill10Query.RecordID;

Now I receive an error message "Join Expression Not Supported." If I change
from "Left Join" to "Inner Join," the query works fine. Any suggestions?

A lot depends on what you expect to see and what you're trying to accomplish.
This will - if it can be made to work at all! - return no records if there is
*any* query which returns no records. The left join stack is heirarchical, in
that (for example) if there is no record in Skill04 then there is nothing to
link Skill05 or any later query *to*.

I'm wondering if you instead want a UNION query, reassembling all ten queries
into a single recordset - all 5 records from Skill01, nothing from Skill02
(since there's no records in that query), all 8 records from Skill03, and so
on. If so use the SQL window (the grid can't do it) to edit

SELECT Skill01Query.*
UNION ALL
SELECT Skill02Query.*
UNION ALL
SELECT Skill03Query.*


etc. through all ten.
--

John W. Vinson [MVP]

John W. Vinson wrote:
On Sun, 18 Apr 2010 14:49:01 -0700, Martin Prunty
wrote:

Thanks for your help and patience. I think I understand your
comments and this time I'll be brief.

I've changed the query using the Access Query Builder creating the
Left Joins as you suggested using "Skill01Query" as the master.
Here's that part of the code:

FROM SLO, ((((((((Skill01Query LEFT JOIN Skill02Query ON
Skill01Query.RecordID = Skill02Query.RecordID) LEFT JOIN
Skill03Query ON Skill01Query.RecordID = Skill03Query.RecordID) LEFT
JOIN Skill04Query ON Skill01Query.RecordID = Skill04Query.RecordID)
LEFT JOIN Skill05Query ON Skill01Query.RecordID =
Skill05Query.RecordID) LEFT JOIN Skill06Query ON
Skill01Query.RecordID = Skill06Query.RecordID) LEFT JOIN
Skill07Query ON Skill01Query.RecordID = Skill07Query.RecordID) LEFT
JOIN Skill08Query ON Skill01Query.RecordID = Skill08Query.RecordID)
LEFT JOIN Skill09Query ON Skill01Query.RecordID =
Skill09Query.RecordID) LEFT JOIN Skill10Query ON
Skill01Query.RecordID = Skill10Query.RecordID;

Now I receive an error message "Join Expression Not Supported." If
I change from "Left Join" to "Inner Join," the query works fine.
Any suggestions?

A lot depends on what you expect to see and what you're trying to
accomplish. This will - if it can be made to work at all! - return no
records if there is *any* query which returns no records. The left
join stack is heirarchical, in that (for example) if there is no
record in Skill04 then there is nothing to link Skill05 or any later
query *to*.

Are you sure John? That's why I advised him to link evrything to
Skill01Query . As long as Skill01Query returns all the recordids he's
interested in, this should work.

In any case, the left joins on their own should not lead to that error...
I'm wondering if the cross-join with SLO is what's generating that error. I
should have tested that. Maybe Jest doesn't like across join combined with
left joins ...

Yes, that's it. this query works fine (no errors):
SELECT Skill01.RecordID, Skill02.RecordID, Skill03.RecordID,
Skill04.RecordID
FROM ((Skill01 left JOIN Skill02 ON Skill01.RecordID = Skill02.RecordID)
left JOIN Skill03 ON Skill01.RecordID = Skill03.RecordID) left JOIN Skill04
ON Skill01.RecordID = Skill04.RecordID;

As soon as I add in a cross join to a table called SLO, I get the same
error.

So, the issue is coming up with an alternative to the cross join. How a
DLookup? I bet that would work. This works:

SELECT Skill01.RecordID, Skill02.RecordID, Skill03.RecordID,
Skill04.RecordID,dlookup("[parm]","SLO","1=1") as parm
FROM ((Skill01 left JOIN Skill02 ON Skill01.RecordID = Skill02.RecordID)
left JOIN Skill03 ON Skill01.RecordID = Skill03.RecordID) left JOIN Skill04
ON Skill01.RecordID = Skill04.RecordID;

I tested with a query named SQL that returns a column called [parm]

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

John W. Vinson wrote:
On Sun, 18 Apr 2010 14:49:01 -0700, Martin Prunty
I'm wondering if you instead want a UNION query, reassembling all ten
queries into a single recordset - all 5 records from Skill01, nothing
from Skill02 (since there's no records in that query), all 8 records
from Skill03, and so on. If so use the SQL window (the grid can't do
it) to edit

SELECT Skill01Query.*
UNION ALL
SELECT Skill02Query.*
UNION ALL
SELECT Skill03Query.*


Oh, and I did meant to add, John might be correct. if Skill01 does not
contain all the RecordIDs that you need to report about, you probably do
need a union query, perhaps followed by a crosstab.


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

On Sun, 18 Apr 2010 20:12:03 -0400, "Bob Barrows"
wrote:

Are you sure John? That's why I advised him to link evrything to
Skill01Query . As long as Skill01Query returns all the recordids he's
interested in, this should work.

sorry! You're right - I was misreading it as a "ladder" join, 01 to 02, 02 to
03 and so on. You're suggesting a "sea urchin" with everything joined to 01.
That would work *if* 01 contained all possible ID's. It might be necessary to
use the unfiltered table itself as the first query in the massive join.

The whole structure seems questionable to me, though. It sounds like he's
taking a table apart into ten chunks and then putting it back together again
(as it was or differently, I can't tell).

--

John W. Vinson [MVP]

John,

Thanks for your suggestion. Can you give me an idea how to handle the
various calculations that are included in the query? The original query had
this code:

SELECT Skill01Query.CallDate, Skill01Query.Department, Skill01Query.Skill,
Skill01Query.Interval,
[Skill01Query]!IncCalls+Skill02Query!IncCalls+Skill03Query!IncCa lls+Skill04Query!IncCalls+Skill05Query!IncCalls+Sk ill06Query!IncCalls+Skill07Query!IncCalls+Skill08Q uery!IncCalls+Skill10Query!IncCalls
AS CombinedCalls,
[Skill01Query]!ICallLoad+Skill02Query!ICallLoad+Skill03Query!ICa llLoad+Skill04Query!ICallLoad+Skill05Query!ICallLo ad+Skill06Query!ICallLoad+Skill07Query!ICallLoad+S kill08Query!ICallLoad+Skill09Query!ICallLoad+Skill 10Query!ICallLoad
AS ILoad,
[Skill01Query]!OCallLoad+Skill02Query!OCallLoad+Skill03Query!OCa llLoad+Skill04Query!OCallLoad+Skill05Query!OCallLo ad+Skill06Query!OCallLoad+Skill07Query!OCallLoad+S kill08Query!OCallLoad+Skill09Query!OCallLoad+Skill 10Query!OCallLoad
AS OLoad,
[Skill01Query]!SeatedAgents+Skill02Query!SeatedAgents+Skill03Que ry!SeatedAgents+Skill04Query!SeatedAgents+Skill05Q uery!SeatedAgents+Skill06Query!SeatedAgents+Skill0 7Query!SeatedAgents+Skill08Query!SeatedAgents+Skil l09Query!SeatedAgents+Skill10Query!SeatedAgents
AS SeatedAgents,
Agents([SLO]![ServiceLevelPercent],[SLO]![ServiceTime],[CombinedCalls]*2,([ILoad]+[OLoad])/[CombinedCalls]) AS CombinedAgents

Many thanks,

Martin

"John W. Vinson" wrote:

On Sun, 18 Apr 2010 14:49:01 -0700, Martin Prunty
wrote:

Thanks for your help and patience. I think I understand your comments and
this time I'll be brief.

I've changed the query using the Access Query Builder creating the Left
Joins as you suggested using "Skill01Query" as the master. Here's that part
of the code:

FROM SLO, ((((((((Skill01Query LEFT JOIN Skill02Query ON
Skill01Query.RecordID = Skill02Query.RecordID) LEFT JOIN Skill03Query ON
Skill01Query.RecordID = Skill03Query.RecordID) LEFT JOIN Skill04Query ON
Skill01Query.RecordID = Skill04Query.RecordID) LEFT JOIN Skill05Query ON
Skill01Query.RecordID = Skill05Query.RecordID) LEFT JOIN Skill06Query ON
Skill01Query.RecordID = Skill06Query.RecordID) LEFT JOIN Skill07Query ON
Skill01Query.RecordID = Skill07Query.RecordID) LEFT JOIN Skill08Query ON
Skill01Query.RecordID = Skill08Query.RecordID) LEFT JOIN Skill09Query ON
Skill01Query.RecordID = Skill09Query.RecordID) LEFT JOIN Skill10Query ON
Skill01Query.RecordID = Skill10Query.RecordID;

Now I receive an error message "Join Expression Not Supported." If I change
from "Left Join" to "Inner Join," the query works fine. Any suggestions?

A lot depends on what you expect to see and what you're trying to accomplish.
This will - if it can be made to work at all! - return no records if there is
*any* query which returns no records. The left join stack is heirarchical, in
that (for example) if there is no record in Skill04 then there is nothing to
link Skill05 or any later query *to*.

I'm wondering if you instead want a UNION query, reassembling all ten queries
into a single recordset - all 5 records from Skill01, nothing from Skill02
(since there's no records in that query), all 8 records from Skill03, and so
on. If so use the SQL window (the grid can't do it) to edit

SELECT Skill01Query.*
UNION ALL
SELECT Skill02Query.*
UNION ALL
SELECT Skill03Query.*


etc. through all ten.
--

John W. Vinson [MVP]
.

On Sun, 18 Apr 2010 19:28:01 -0700, Martin Prunty
wrote:

Thanks for your suggestion. Can you give me an idea how to handle the
various calculations that are included in the query? The original query had
this code:

If you can't predict or rely on the ten queries all having a record for the
same ID - or even if only a few are missing - I don't think any reasonable
combination of Joins will work. You may need to use multiple Subqueries or
DLookUps.
--

John W. Vinson [MVP]