bug or glitch in my acc2k win98se, query

  • Thread starter Thread starter phil
  • Start date Start date
P

phil

I posted once, will try again, most likely I was not clear and now I
have more specific info if anyone has an answer.

win98
acc2000 sr1 (maybe 1a haven't checked yet)
exclusive single station operation. no be/fe.

2 tables as follows with sample data

tbl A
field longint job
field longint yr

3421 2003
3421 2004
7000 2003
7000 2004
8000 2003
8000 2004
9000 2003
9000 2004


tbl B
fld longint job
fld date somedate
fld others...

3421 8/1/2003
7000 8/1/2003
7000 11/1/2004
8000 8/1/2003
9000 8/1/2003
9000 11/1/2004

query:
select A.job, a.yr from B right join A on b.job = a.job
where a.yr = 2004 and B.somedate > #9/30/2003#;

produces;
3421 2004
7000 2004
9000 2004

obviously not picking up the 8000 record in A

if I change the two records from 8000 to 8001 it will pick it up

I tried indexing and retyping the records, compacting the database and
fixing office.

Any ideas on this bug/glitch

Also, I believe I ran into this behavior in another scenerio with a
different db but can not confirm this. I just ignored the problem and
did without whatever I was doing, but I do vaguely recall similar
instance.
 
Dear Phil:

If you remove the filter "B.somedate > #9/30/2003#" and add B.somedate
to the SELECT clause, the value of B.somedate would be 8/1/2003,
right? So, why would you NOT expect this filter to remove that row
from the query.

Now, if you change the rows in tbl A from job = 8000 to job = 8001,
then the value of B.somedate would be NULL for those rows, right? So
now the filter B.somedate > #9/30/2003# does not remove the row. Not
when I try it!

I don't see a bug here. The join is created first, then the filter
applied. The filter removes the row from the query.

I know the join says "Include all records from tblA" but they are only
included up to the point where you filter them out.

Perhaps if you were to explain what it is you expect will be done.

Now, I think what you want is to show all rows from tblA and only
those rows from B where somedate > #9/30/03#. This means you apply
the filter to tblB before you join:

save this as qryB:

SELECT *
FROM tblB
WHERE somedate>#9/30/2003#;

then use this query:

SELECT tblA.job, tblA.yr, qryB.somedate
FROM tblA
LEFT JOIN qryB ON tblA.job = qryB.job;

I'm guessing this is what you were wanting. It applies the filter
first, then the join. This is not an uncommon misconception.

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
thx Tom, I get same results without filter, but yes I did try to use the
filter and null record inclusion by adding somedate > #9/30/03# or
somedate is null

anyway even without the filter it acts the same.

I have a report that I want to show active jobs for the postYr (fy)
whether they have activity in the new year or not.

I noted that 3421 job because that does show the record with the A table
record joined to null records in B tbl.

Yet 8000 does not. some inline comments below

Tom Ellison said:
Dear Phil:

If you remove the filter "B.somedate > #9/30/2003#" and add B.somedate
to the SELECT clause, the value of B.somedate would be 8/1/2003,
right? So, why would you NOT expect this filter to remove that row
from the query.
Click to expand...

leaving in the filter the record I believe should read like the 3421
record
job fy somedate
3421 2004 <null>
7000 2004 11/1/2004
9000 2004 11/1/2004

the missing record is:

8000 2004 <null>

if I change applicable fields I do show:

Now, if you change the rows in tbl A from job = 8000 to job = 8001,
then the value of B.somedate would be NULL for those rows, right? So
now the filter B.somedate > #9/30/2003# does not remove the row. Not
when I try it!
Click to expand...

I change the rows in tbl A *and* B to 8001
I know the join says "Include all records from tblA" but they are only
included up to the point where you filter them out.
Click to expand...

but I am not filtering out the composite record only limiting the tbl B
records, right?


save this as qryB:

SELECT *
FROM tblB
WHERE somedate>#9/30/2003#;

then use this query:

SELECT tblA.job, tblA.yr, qryB.somedate
FROM tblA
LEFT JOIN qryB ON tblA.job = qryB.job;

I'm guessing this is what you were wanting. It applies the filter
first, then the join. This is not an uncommon misconception.

Please let me know if this helped, and if I can be of any other
assistance.
Click to expand...

thanks, this is what we end up doing, and it may be the correct way to
go anyway.

But I still find above comments without the filter and with only the
number 8000 affected to be curious in the least and a possible problem
with my installation at the worse. Which the later I couldn't correct.
 
Dear Phil:

I really don't know what else to say, except to repeat the core of
what I tried to say before.

The filter is being applied AFTER the join is made. If you write the
filter so it excludes rows from the JOIN, then those rows WILL be
missing. If you apply the filter BEFORE the JOIN, as in my sample,
then the rows won't be missing.

I'm not sure the terms "BEFORE" and "AFTER" really have any place in a
non-procedural language, since things aren't really being done in some
order. Rather, there are precedences to what happens, and these give
the appearance of things being done in some sequence, even though that
isn't really the case. But "BEFORE" and "AFTER" are sometimes a
reasonable way to picture the way it works.

I'm glad you clarified about "somedate is null" being part of the
query. You had me worried when your results didn't match the query
you reported.

But, your perception that the filter should not remove the 8000 job is
mistaken. This is not a bug or glitch as you have maintained. Other
SQL engines will do the same as Jet does for this. This is the
behavior defined in the standards for SQL language. The filter takes
precedence. Since the JOIN does find a matching job in tblB, it does
not generate the row where the tblB columns have null values.

As this is the way things work, I can only suggest you relax a bit and
try to get your head wrapped around this instance where things just
aren't working the way you are expecting. Otherwise, you're just
going to bump into this again.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom thanks for help. I knew, that, <grin> ( meaning: I forgot that)

anyway I mentioned that our secretary/bookkeeper/programmer/etc. figured
it out in the same manner you refer to. I just replied before to say
that it still seemed curious that it acts different for the same record,
differing that one is 8000 and the other is not. It still seems like
jet treated my error in a indeterminate way, which doesn't seem to be
proper, and thought a posting may help others in a search.

But anyway please accept my thanks for the refresher.
 
Back
Top