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

John W. Vinson wrote:
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.

Not hard to do without proper formatting, so that's partially my bad. Also,
that's what it originally was.

I do like the tems you used for these strategies. :-)
"sea urchin" indeed. :-)
Similar to a star schema, but not quite the same.
--
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"

The more I think about this, the more I would be inclined to use a union
query such as John selected to create a normalized view of this data. Then I
would create a grouping query to do the sums. And lastly, a saved query that
used a cross join between the saved grouping query and SLO to do the
calculations.

The main difference is that I would include a column in the union query to
indicate the source of the data, just for debugging purposes, like this:

SELECT 1 As SkillSource, Skill01Query.*
UNION ALL
SELECT 2, Skill02Query.*
UNION ALL
SELECT 3, Skill03Query.*

Save it as NormalizedSkills

Then create SkillAggregations using sql like this:

SELECT RecordID,CallDate, Department, Skill, Interval
,Sum(IncCalls) As CombinedCalls
,Sum(CallLoad) As ILoad
,Sum(OCallLoad) As OLoad
, Sum(SeatedAgents) As SeatedAgents
FROM NormalizedSkills
GROUP BY RecordID,CallDate, Department, Skill, Interval

And lastly:

SELECT RecordID,CallDate, Department, Skill, Interval
, CombinedCalls, ILoad, OLoad, SeatedAgents
,Agents([SLO]![ServiceLevelPercent],[SLO]![ServiceTime],[CombinedCalls]*2,([ILoad]+[OLoad])/[CombinedCalls])
AS CombinedAgents
FROM SkillAggregations, SLO

It's much simpler isn't it?


Martin Prunty wrote:
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]
.

--
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"

Bob Barrows wrote:

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.


I was going to suggest using a separate table that contained
all the record IDs as the master table. But then, I don't
see where Martin ever said anything about where all the
record IDs were known. If they are arbitrary instead of a
known list, then a UNION query would be required to create
such a list.

This whole scenario strikes me as being an unormalized mess
and I'm wondering what the starting point is. Maybe it all
started from a single table and the 10 queries are what's
getting in the way??

--
Marsh
MVP [MS Access]

On Mon, 19 Apr 2010 06:51:23 -0400, "Bob Barrows"
wrote:

"sea urchin" indeed. :-)

I'd call it uni sushi but that's probably a bit too obscure... :-{)
--

John W. Vinson [MVP]