equijoin of two queries results in no result rows

[John Seghers]

I'm helping a friend with a homework assignment and we've run into something
that makes no sense to us.

We have a normalized database of several tables. We have several queries
that aggregate data in useful ways, pulling together sums and counts of
data.

There are two queries that each have an ID and a few columns of information
that we want to join into one query to be used for a report.

A simplified version of the tables and queries is:

tblNames: ID, Name
1 Name1
2 Name2
3 Name3
4 Name4

Q1: Aggregate some data from other tables
Q2: further aggregate the results from Q1. This produces a result set of:
ID Name value1 value2
3 Name3 8 7
2 Name2 7 4
4 Name4 7 4
1 Name1 5 1

Q3: Performs another aggregation resulting in the result set:
ID Name valueX valueY Total
3 Name3 2 1 3
2 Name2 0 3 3
4 Name4 2 1 3
1 Name1 2 1 3

As you can see, the result sets each have all four of the IDs present.

The final Query joins Q2 with Q3:

SELECT Q3.Name, Q3.Total, Q2.value1, Q2.value2
FROM Q3, Q2
WHERE Q3.ID=Q2.ID;

This returns an empty result set.

Eliminating the WHERE (and adding columns for the IDs from each of
Q2 and Q3clause returns the expected 16 row result set,
of which four rows clearly have identical IDs.

Indeed, using Q2 and Q3 as Make Table queries, and then performing the
same join query on the tables works as expected. Yet trying to perform
the join between Q3 and T2 (the table produced from Q2) results in the
empty set. I didn't try Q2 with T3...

Any ideas as to what might be happening?
- John

 

[Michel Walsh]

Hi,

one of the ID is not a LOOKUP that displays a number rather than the actual
data?


Vanderghast, Access MVP

 

[John Seghers]

Hi,

one of the ID is not a LOOKUP that displays a number rather than the actual
data?

No. In both Q2 and Q3, the ID field (and Name field) is pulled directly
from the tblNames table and is specified in the GROUP BY statement. Both
Q2 and Q3 group by the same two items.

I was going to post the queries here...I had an odd thought.

The GROUP BY clause in both of these was:
GROUP BY tblNames.Name, tblNames.ID

Since the only reason both are there is so that they can both be in the
SELECT
portion along with the aggregate functions, I hadn't thought too much about
the
order of the two, other than that that order would be a bit less efficient
than
the reverse.

I tried reversing them in both Q2 and Q3--such as:
SELECT tblNames.ID, tblNames.Name, SUM(.....)
FROM tblNames, Q1
GROUP BY tblNames.ID, tblNames.Name;

And it works. I'm now getting the propper results.

Both queries GROUP BY clauses were in the same order. There's a 1:1
correlation
between tblNames.ID and tblNames.Name. Why does it matter (other than that
the
ID is indexed, and therefore more efficient) what order they are in?

- John

 

[Michel Walsh]

Hi,

Should not influence the result (but greatly the time required to solve the
query). At execution time, to see if the group already exist, we can imagine
a test has to be made (through an index or otherwise) and it is faster,
under Windows, to test together two numbers than two strings (collating
sequences has to be taken into account, such that "A" ="a", database-wise,
so a simple bit to bit comparison is not doable, when strings are involved).
Having the group asserted "of the group" through a "criteria" only made of
numbers would be faster... next would be if the "index" can easily spot the
group a candidate belong to, or not, and that, beginning with numbers,
rather than string, can help too.


The "Name" is not excessively long (a memo, more than 255 char) ? There is
surely a limit on how long the "group" identifier (what makes the group
"distinct") can be, but *if* some internal sort is implied... and *if* this
internal sort has the same limitation that normal sort, it is limited to a
"total of 255 char on one or more fields" in some version... :-( Better
starting with numbers, maybe not just for speed, after all, and if you have
a personal experimentation that tell you to do so... Sounds to me you don't
need more encouragement to do so...


Hoping it may help,
Vanderghast, Access MVP

 

[John Seghers]

Hi,

Should not influence the result

At this point it is purely an academic question since we now have an answer
that
works for this assignment. Considering the small sets we were working with
(the names were all Access Text fields of the default 50 chars, and only 4
records
in that table) the execution time wasn't going to be a problem. Primarily I
was
letting the student write the queries and helping with problems.

The "Name" is not excessively long ... Better
starting with numbers, maybe not just for speed, after all
Agreed.


and if you have
a personal experimentation that tell you to do so... Sounds to me you don't
need more encouragement to do so...

I'm still perplexed as to why the join failed...and wonder if it's a bug in
Access?

- John

 

[Michel Walsh]

Hi,


If the result is reproducible, after a fresh database compaction, with
just the involved tables and queries pasted in a fresh new db, you probably
have a bug there, yes. I guess you can send a bug report (through the
ContactUs from Microsoft web page, ... last time it was on the bottom of
their main page) and/or send it to me at vanderghast at msn, sure it is dot
com, so that I can make further check and use the MVP channel of
communication with Microsoft to get a bug confirmation (and a KB article
about it, if not a patch for next patch release).


Vanderghast, Access MVP

 

[John Seghers]

If the result is reproducible, after a fresh database compaction, with
just the involved tables and queries pasted in a fresh new db, you probably
have a bug there, yes.

It is reproduceable and I've emailed the pared-down version of the database
to you.

Thank you!

- John

 

[Michel Walsh]

Hi,


Got it.

Is it possible you modified the table field name of tblTeams.Team_ID
AFTER you wrote the first queries? ( I suspect a possible interaction
with the feature tracking fields name modifications, not a fact, just a
suspicion).

If you cut and paste the SQL statement from the bad queries, into brand new
queries, the bug disappears (keeping any order of the fields in the GROUP BY
statement).


Vanderghast, Access MVP

 

[John Seghers]

Is it possible you modified the table field name of tblTeams.Team_ID
AFTER you wrote the first queries? ( I suspect a possible interaction
with the feature tracking fields name modifications, not a fact, just a
suspicion).

If you cut and paste the SQL statement from the bad queries, into brand new
queries, the bug disappears (keeping any order of the fields in the GROUP BY
statement).

It is possible. I know that the table and query names changed at one point.
I believe that table was originally named "Table: Teams" and queries were
of a similar form "Query: ...."

As for the individual field name.... I'm not sure.

- John