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

M

Martin Prunty

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;
 
M

Martin Prunty

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 said:
Martin said:
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;
Click to expand...


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.
Click to expand...
 
M

Martin Prunty

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 said:
"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 said:
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 said:
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.
Click to expand...
Click to expand...
.
Click to expand...
 
B

Bob Barrows

Wow.. You wrote a dissertation and you never answered Marshall's question.
:)
See below.

Martin said:
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.
Click to expand...

Why? Or, perhaps more to the point, _what was the symptom that made you
realize it "didn't work"_?
An error message?
The wrong results?
Your computer exploded? ;-)

We're not looking over your shoulder. We cannot somehow deduce what you mean
by "didn't work". You need to describe your symptoms without using generic
terms like "didn't work" or "no dice" or the like.
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.
Click to expand...

The best thing to do is use the Access Query Builder to create your query.
Why? Because multiple-table joins get messy. The more tables, the more
parentheses that need to be kept track of. It's easy to fail to close one of
the sets of parentheses and difficult to figure out which one is unclosed.
Also, you need to pay attention to the "direction" of your joins.
From Access online help (from the section entitled "Microsoft Jet SQL
Reference" in the table of contents):
******************************************************************************
You can also nest JOIN statements using the following syntax:

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER
JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.
******************************************************************************
(compopr is short for comparison operator)

The last sentence is critical.

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN,

permissible:
table1 inner join (table2 left join table3)

but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.
not permissible:
table1 left join (table2 inner join table3)
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,
Click to expand...

Oh, you probably meant "autonumber" field rather than "primary key field".
"autonumber" is not synonymous with "primary key" even though an autonumber
field is often used for a table's primary key. It is certainly possible to
designate a non-autonumber field, or even a combination of fields, as a
table's primary key.
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
Click to expand...

What's the point of that? If they all start with "1", then the "1" isn't
really necessary, is it?
You don't need to answer this. It's just something for you to think about.
The answer will do me absolutely no good since I will likely have nothing to
do with this application after this. :)
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.
Click to expand...

I don't quite follow this, but again, it probably doesn't matter :)
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.
Click to expand...

I think what you're saying is that Skill01 will always have all the
RecordIDs that you need to report on, correct? If not, then a join is not
the answer.

Let's look at your from clause:
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)
etc.
Click to expand...

I think the problem is the failure to establish a "master" table (my term),
i.e., the table that always has all the RecordIDs you are interested in and
the table to which all the other table are joined. It seems to me based on
what I think I read above, that Skill01 will be the master table, so you
should be using that table as the basis for all your ON criteria. You really
don't care that Skill03's RecordID matches Skill02's do you? There might not
be a record in Skill02 for it to match to. You should be matching it to a
RecordID in Skill01.

So here's a starting point for you:

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


And you really should consider eliminating all spaces from table, query and
field names. It will eliminate the need for brackets making your sql a bit
more "readable"
 
M

Martin Prunty

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?

Thank you.

Bob Barrows said:
Wow.. You wrote a dissertation and you never answered Marshall's question.
:)
See below.

Martin said:
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.
Click to expand...

Why? Or, perhaps more to the point, _what was the symptom that made you
realize it "didn't work"_?
An error message?
The wrong results?
Your computer exploded? ;-)

We're not looking over your shoulder. We cannot somehow deduce what you mean
by "didn't work". You need to describe your symptoms without using generic
terms like "didn't work" or "no dice" or the like.
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.
Click to expand...

The best thing to do is use the Access Query Builder to create your query.
Why? Because multiple-table joins get messy. The more tables, the more
parentheses that need to be kept track of. It's easy to fail to close one of
the sets of parentheses and difficult to figure out which one is unclosed.
Also, you need to pay attention to the "direction" of your joins.
From Access online help (from the section entitled "Microsoft Jet SQL
Reference" in the table of contents):
******************************************************************************
You can also nest JOIN statements using the following syntax:

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER
JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.
******************************************************************************
(compopr is short for comparison operator)

The last sentence is critical.

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN,

permissible:
table1 inner join (table2 left join table3)

but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.
not permissible:
table1 left join (table2 inner join table3)
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,
Click to expand...

Oh, you probably meant "autonumber" field rather than "primary key field".
"autonumber" is not synonymous with "primary key" even though an autonumber
field is often used for a table's primary key. It is certainly possible to
designate a non-autonumber field, or even a combination of fields, as a
table's primary key.
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
Click to expand...

What's the point of that? If they all start with "1", then the "1" isn't
really necessary, is it?
You don't need to answer this. It's just something for you to think about.
The answer will do me absolutely no good since I will likely have nothing to
do with this application after this. :)
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.
Click to expand...

I don't quite follow this, but again, it probably doesn't matter :)
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.
Click to expand...

I think what you're saying is that Skill01 will always have all the
RecordIDs that you need to report on, correct? If not, then a join is not
the answer.

Let's look at your from clause:
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)
etc.
Click to expand...

I think the problem is the failure to establish a "master" table (my term),
i.e., the table that always has all the RecordIDs you are interested in and
the table to which all the other table are joined. It seems to me based on
what I think I read above, that Skill01 will be the master table, so you
should be using that table as the basis for all your ON criteria. You really
don't care that Skill03's RecordID matches Skill02's do you? There might not
be a record in Skill02 for it to match to. You should be matching it to a
RecordID in Skill01.

So here's a starting point for you:

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


And you really should consider eliminating all spaces from table, query and
field names. It will eliminate the need for brackets making your sql a bit
more "readable"

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



.
Click to expand...
 
J

John W. Vinson

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?
Click to expand...

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.
 
B

Bob Barrows

John said:
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*.
Click to expand...

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]
 
B

Bob Barrows

John said:
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.*
Click to expand...
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.
 
J

John W. Vinson

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.
Click to expand...

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).
 
M

Martin Prunty

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!IncCalls+Skill04Query!IncCalls+Skill05Query!IncCalls+Skill06Query!IncCalls+Skill07Query!IncCalls+Skill08Query!IncCalls+Skill10Query!IncCalls
AS CombinedCalls,
[Skill01Query]!ICallLoad+Skill02Query!ICallLoad+Skill03Query!ICallLoad+Skill04Query!ICallLoad+Skill05Query!ICallLoad+Skill06Query!ICallLoad+Skill07Query!ICallLoad+Skill08Query!ICallLoad+Skill09Query!ICallLoad+Skill10Query!ICallLoad
AS ILoad,
[Skill01Query]!OCallLoad+Skill02Query!OCallLoad+Skill03Query!OCallLoad+Skill04Query!OCallLoad+Skill05Query!OCallLoad+Skill06Query!OCallLoad+Skill07Query!OCallLoad+Skill08Query!OCallLoad+Skill09Query!OCallLoad+Skill10Query!OCallLoad
AS OLoad,
[Skill01Query]!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

Many thanks,

Martin
 
J

John W. Vinson

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:
Click to expand...

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.
 
B

Bob Barrows

John said:
sorry! You're right - I was misreading it as a "ladder" join, 01 to
02, 02 to 03 and so on.
Click to expand...

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.
 
B

Bob Barrows

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 said:
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!IncCalls+Skill04Query!IncCalls+Skill05Query!IncCalls+Skill06Query!IncCalls+Skill07Query!IncCalls+Skill08Query!IncCalls+Skill10Query!IncCalls
AS CombinedCalls,
[Skill01Query]!ICallLoad+Skill02Query!ICallLoad+Skill03Query!ICallLoad+Skill04Query!ICallLoad+Skill05Query!ICallLoad+Skill06Query!ICallLoad+Skill07Query!ICallLoad+Skill08Query!ICallLoad+Skill09Query!ICallLoad+Skill10Query!ICallLoad
AS ILoad,
[Skill01Query]!OCallLoad+Skill02Query!OCallLoad+Skill03Query!OCallLoad+Skill04Query!OCallLoad+Skill05Query!OCallLoad+Skill06Query!OCallLoad+Skill07Query!OCallLoad+Skill08Query!OCallLoad+Skill09Query!OCallLoad+Skill10Query!OCallLoad
AS OLoad,
[Skill01Query]!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

Many thanks,

Martin

John W. Vinson said:
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.
Click to expand...
Click to expand...
 
Top