Absolutely Stumped on Query

[JimS]

I have a query -- massively large, several layers deep, but still
straightforward. It works fine by gathering 5 different types of spending
summed up by project and equipment. As I said, it works fine.

My client says "I forgot the sixth" type of spending. No big deal, it's in
an excel table (like many of the other ones) you can import and hold for
reporting. Like several of the others, I set up a simple import, and "made" a
table in access to hold the imported data. I've used that table many times
for other purposes, just fine.

When I went to add this table to the massive composite query (using a query
to translate a text "Project Nbr" to a "ProjectID", and setting the other key
field "MICAPID" to zero), I put the table in the query in a left join --
meaning it would contribute to the massive query, but not constrain it.
Here's the little query:

SELECT CLng(tblProjects.ID) AS ProjectID, CLng(0) AS MICAPID, CLng(40) AS
DisciplineID, Round([PCARD],2) AS PCardSpend
FROM AllPCardImport INNER JOIN tblProjects ON AllPCardImport.[PROJ #] =
tblProjects.ProjectNbr;

When I joined this query to the massive query, in exactly the same way I
joined the other 5 or so subqueries, its results were exactly as if I'd
joined it in an inner join.

After much experimentation, I found that if I took the original massive
composite query and turned it into a flat table, then joined this query to it
(using a left join), I got the same result (hence why I don't include the
massive query here).

But, if I then turned this query into a flat table and joined it to the
massive query, it works JUST FINE. It populates the PCard Spending Column and
properly includes all records from the massive query, even if there are no
matching keys.

Obviously, I have a workaround, but does anyone have any idea why this
behavior is happening?

 

[Ken Snell \(MVP\)]

Very difficult to give suggestions about why you see the results you do
because we cannot see the entire data structure nor the queries' designs,
but perhaps it's because your initial "little" query is using an INNER JOIN,
which then may not "play well" with the LEFT JOIN that you use against it.
Try changing the first query's join to LEFT JOIN and see what happens.

 

[JimS]

I did exactly what you said. Made sense to try. Result is strange.
Connecting the "made" table (comp1) to the query (adjusted to contain a left
join) with an inner join netted 283 records. When I made it a left join (see
sql below), I got 284 records. I should have gotten around 500 records (the
record count of the "comp1" table).

Final Query:
SELECT comp1.*, qryPCardSpend.*
FROM comp1 LEFT JOIN qryPCardSpend ON (comp1.MICAPID =
qryPCardSpend.MICAPID) AND (comp1.ProjectID = qryPCardSpend.ProjectID);

PCardSpend Query (qryPCardSpend)

SELECT CLng(tblProjects.ID) AS ProjectID, CLng(0) AS MICAPID, CLng(40) AS
DisciplineID, Round([PCARD],2) AS PCardSpend
FROM AllPCardImport LEFT JOIN tblProjects ON AllPCardImport.[PROJ #] =
tblProjects.ProjectNbr
WHERE (((tblProjects.ID) Is Not Null));

"comp1" contains rows that have many MICAPIDs per ProjectID, including at
least one that has a "0" MICAPID.
--
Jim

Very difficult to give suggestions about why you see the results you do
because we cannot see the entire data structure nor the queries' designs,
but perhaps it's because your initial "little" query is using an INNER JOIN,
which then may not "play well" with the LEFT JOIN that you use against it.
Try changing the first query's join to LEFT JOIN and see what happens.
--

Ken Snell
<MS ACCESS MVP>

I have a query -- massively large, several layers deep, but still
straightforward. It works fine by gathering 5 different types of spending
summed up by project and equipment. As I said, it works fine.

My client says "I forgot the sixth" type of spending. No big deal, it's in
an excel table (like many of the other ones) you can import and hold for
reporting. Like several of the others, I set up a simple import, and
"made" a
table in access to hold the imported data. I've used that table many times
for other purposes, just fine.

When I went to add this table to the massive composite query (using a
query
to translate a text "Project Nbr" to a "ProjectID", and setting the other
key
field "MICAPID" to zero), I put the table in the query in a left join --
meaning it would contribute to the massive query, but not constrain it.
Here's the little query:

SELECT CLng(tblProjects.ID) AS ProjectID, CLng(0) AS MICAPID, CLng(40) AS
DisciplineID, Round([PCARD],2) AS PCardSpend
FROM AllPCardImport INNER JOIN tblProjects ON AllPCardImport.[PROJ #] =
tblProjects.ProjectNbr;

When I joined this query to the massive query, in exactly the same way I
joined the other 5 or so subqueries, its results were exactly as if I'd
joined it in an inner join.

After much experimentation, I found that if I took the original massive
composite query and turned it into a flat table, then joined this query to
it
(using a left join), I got the same result (hence why I don't include the
massive query here).

But, if I then turned this query into a flat table and joined it to the
massive query, it works JUST FINE. It populates the PCard Spending Column
and
properly includes all records from the massive query, even if there are no
matching keys.

Obviously, I have a workaround, but does anyone have any idea why this
behavior is happening?

 

[Ken Snell \(MVP\)]

Well, it was a good try!

It's very difficult to debug your query without much more details about the
other queries and the data structure and values. If you can post more
information, we may be able to provide more helpful suggestions.

--

Ken Snell
<MS ACCESS MVP>

I did exactly what you said. Made sense to try. Result is strange.
Connecting the "made" table (comp1) to the query (adjusted to contain a
left
join) with an inner join netted 283 records. When I made it a left join
(see
sql below), I got 284 records. I should have gotten around 500 records
(the
record count of the "comp1" table).

Final Query:
SELECT comp1.*, qryPCardSpend.*
FROM comp1 LEFT JOIN qryPCardSpend ON (comp1.MICAPID =
qryPCardSpend.MICAPID) AND (comp1.ProjectID = qryPCardSpend.ProjectID);

PCardSpend Query (qryPCardSpend)

SELECT CLng(tblProjects.ID) AS ProjectID, CLng(0) AS MICAPID, CLng(40) AS
DisciplineID, Round([PCARD],2) AS PCardSpend
FROM AllPCardImport LEFT JOIN tblProjects ON AllPCardImport.[PROJ #] =
tblProjects.ProjectNbr
WHERE (((tblProjects.ID) Is Not Null));

"comp1" contains rows that have many MICAPIDs per ProjectID, including at
least one that has a "0" MICAPID.
--
Jim

Very difficult to give suggestions about why you see the results you do
because we cannot see the entire data structure nor the queries' designs,
but perhaps it's because your initial "little" query is using an INNER
JOIN,
which then may not "play well" with the LEFT JOIN that you use against
it.
Try changing the first query's join to LEFT JOIN and see what happens.
--

Ken Snell
<MS ACCESS MVP>

I have a query -- massively large, several layers deep, but still
straightforward. It works fine by gathering 5 different types of
spending
summed up by project and equipment. As I said, it works fine.

My client says "I forgot the sixth" type of spending. No big deal, it's
in
an excel table (like many of the other ones) you can import and hold
for
reporting. Like several of the others, I set up a simple import, and
"made" a
table in access to hold the imported data. I've used that table many
times
for other purposes, just fine.

When I went to add this table to the massive composite query (using a
query
to translate a text "Project Nbr" to a "ProjectID", and setting the
other
key
field "MICAPID" to zero), I put the table in the query in a left
join --
meaning it would contribute to the massive query, but not constrain it.
Here's the little query:

SELECT CLng(tblProjects.ID) AS ProjectID, CLng(0) AS MICAPID, CLng(40)
AS
DisciplineID, Round([PCARD],2) AS PCardSpend
FROM AllPCardImport INNER JOIN tblProjects ON AllPCardImport.[PROJ #] =
tblProjects.ProjectNbr;

When I joined this query to the massive query, in exactly the same way
I
joined the other 5 or so subqueries, its results were exactly as if I'd
joined it in an inner join.

After much experimentation, I found that if I took the original massive
composite query and turned it into a flat table, then joined this query
to
it
(using a left join), I got the same result (hence why I don't include
the
massive query here).

But, if I then turned this query into a flat table and joined it to the
massive query, it works JUST FINE. It populates the PCard Spending
Column
and
properly includes all records from the massive query, even if there are
no
matching keys.

Obviously, I have a workaround, but does anyone have any idea why this
behavior is happening?

 

[JimS]

Feels alot like a rat hole. I was able to add fields to the original table
and update it to contain those FK fields, then join it and it worked. I just
do the update of the fields (actually only one...) during the import. Thanks
for your help, we'll just have to let this one go, I think.
--
Jim

Well, it was a good try!

It's very difficult to debug your query without much more details about the
other queries and the data structure and values. If you can post more
information, we may be able to provide more helpful suggestions.

--

Ken Snell
<MS ACCESS MVP>

I did exactly what you said. Made sense to try. Result is strange.
Connecting the "made" table (comp1) to the query (adjusted to contain a
left
join) with an inner join netted 283 records. When I made it a left join
(see
sql below), I got 284 records. I should have gotten around 500 records
(the
record count of the "comp1" table).

Final Query:
SELECT comp1.*, qryPCardSpend.*
FROM comp1 LEFT JOIN qryPCardSpend ON (comp1.MICAPID =
qryPCardSpend.MICAPID) AND (comp1.ProjectID = qryPCardSpend.ProjectID);

PCardSpend Query (qryPCardSpend)

SELECT CLng(tblProjects.ID) AS ProjectID, CLng(0) AS MICAPID, CLng(40) AS
DisciplineID, Round([PCARD],2) AS PCardSpend
FROM AllPCardImport LEFT JOIN tblProjects ON AllPCardImport.[PROJ #] =
tblProjects.ProjectNbr
WHERE (((tblProjects.ID) Is Not Null));

"comp1" contains rows that have many MICAPIDs per ProjectID, including at
least one that has a "0" MICAPID.
--
Jim

Very difficult to give suggestions about why you see the results you do
because we cannot see the entire data structure nor the queries' designs,
but perhaps it's because your initial "little" query is using an INNER
JOIN,
which then may not "play well" with the LEFT JOIN that you use against
it.
Try changing the first query's join to LEFT JOIN and see what happens.
--

Ken Snell
<MS ACCESS MVP>



I have a query -- massively large, several layers deep, but still
straightforward. It works fine by gathering 5 different types of
spending
summed up by project and equipment. As I said, it works fine.

My client says "I forgot the sixth" type of spending. No big deal, it's
in
an excel table (like many of the other ones) you can import and hold
for
reporting. Like several of the others, I set up a simple import, and
"made" a
table in access to hold the imported data. I've used that table many
times
for other purposes, just fine.

When I went to add this table to the massive composite query (using a
query
to translate a text "Project Nbr" to a "ProjectID", and setting the
other
key
field "MICAPID" to zero), I put the table in the query in a left
join --
meaning it would contribute to the massive query, but not constrain it.
Here's the little query:

SELECT CLng(tblProjects.ID) AS ProjectID, CLng(0) AS MICAPID, CLng(40)
AS
DisciplineID, Round([PCARD],2) AS PCardSpend
FROM AllPCardImport INNER JOIN tblProjects ON AllPCardImport.[PROJ #] =
tblProjects.ProjectNbr;

When I joined this query to the massive query, in exactly the same way
I
joined the other 5 or so subqueries, its results were exactly as if I'd
joined it in an inner join.

After much experimentation, I found that if I took the original massive
composite query and turned it into a flat table, then joined this query
to
it
(using a left join), I got the same result (hence why I don't include
the
massive query here).

But, if I then turned this query into a flat table and joined it to the
massive query, it works JUST FINE. It populates the PCard Spending
Column
and
properly includes all records from the massive query, even if there are
no
matching keys.

Obviously, I have a workaround, but does anyone have any idea why this
behavior is happening?