Invalid data on Left Join query


N

Nunya

Access 12.0
If I create a left join query that is based on a table and a query with an
expression in it the results of the second query contain data for every
record of the calculated field. Here is the setup
_____________________________________________________________________
Table Data
ID Field1 Field2
1 good
2 bad good
3 good bad
4 bad
5 good
6 bad
7 good
8 bad
9 good
10 bad
_____________________________________________________________________
Table header
ID Field1
1 use
2 use
3 use
4 use
5 use
6 not me
7 not me
8 not me
9 not me
10 not me
_____________________________________________________________________
Query1
SELECT data.ID, IIf(Right([field1] & [field2],2)="ad","failed","passed") AS
Expr1
FROM data
WHERE (((IIf(Right([field1] &
[field2],2)="ad","failed","passed"))="passed"));
produces
ID Expr1
1 passed
2 passed
5 passed
7 passed
9 passed
_____________________________________________________________________
Query2
SELECT header.ID, header.Field1, Query1.ID, Query1.Expr1
FROM header LEFT JOIN Query1 ON header.ID = Query1.ID;
produces
header.ID Field1 Query1.ID Expr1
1 use 1 passed
2 use 2 passed
3 use passed
4 use passed
5 use 5 passed
6 not me passed
7 not me 7 passed
8 not me passed
9 not me 9 passed
10 not me passed

Expr1 in query 2 should only have a value for ID 1,2,5,7,9 not every record.
These queries ran fine in 2003 but now that we have upgraded to 12.0 they
don't work. Any advice would be appreciated.
 
Ad

Advertisements

K

KARL DEWEY

I do not understand it but here is a fix ---
SELECT Header.ID, Header.Field1, Query1.ID, IIf([Query1].[ID] Is
Null,Null,[Expr1]) AS Expr2
FROM Header LEFT JOIN Query1 ON Header.ID = Query1.ID;
 
Ad

Advertisements

N

Nunya

I don't understand it either. I found some info in the KB about problems
with left outer joins but nothing close to this. I found that if I create a
table from query1 and use the made table I get the correct results so it must
be the expression causing the problems. How do you go about reporting a bug
to MS?

KARL DEWEY said:
I do not understand it but here is a fix ---
SELECT Header.ID, Header.Field1, Query1.ID, IIf([Query1].[ID] Is
Null,Null,[Expr1]) AS Expr2
FROM Header LEFT JOIN Query1 ON Header.ID = Query1.ID;

--
KARL DEWEY
Build a little - Test a little


Nunya said:
Access 12.0
If I create a left join query that is based on a table and a query with an
expression in it the results of the second query contain data for every
record of the calculated field. Here is the setup
_____________________________________________________________________
Table Data
ID Field1 Field2
1 good
2 bad good
3 good bad
4 bad
5 good
6 bad
7 good
8 bad
9 good
10 bad
_____________________________________________________________________
Table header
ID Field1
1 use
2 use
3 use
4 use
5 use
6 not me
7 not me
8 not me
9 not me
10 not me
_____________________________________________________________________
Query1
SELECT data.ID, IIf(Right([field1] & [field2],2)="ad","failed","passed") AS
Expr1
FROM data
WHERE (((IIf(Right([field1] &
[field2],2)="ad","failed","passed"))="passed"));
produces
ID Expr1
1 passed
2 passed
5 passed
7 passed
9 passed
_____________________________________________________________________
Query2
SELECT header.ID, header.Field1, Query1.ID, Query1.Expr1
FROM header LEFT JOIN Query1 ON header.ID = Query1.ID;
produces
header.ID Field1 Query1.ID Expr1
1 use 1 passed
2 use 2 passed
3 use passed
4 use passed
5 use 5 passed
6 not me passed
7 not me 7 passed
8 not me passed
9 not me 9 passed
10 not me passed

Expr1 in query 2 should only have a value for ID 1,2,5,7,9 not every record.
These queries ran fine in 2003 but now that we have upgraded to 12.0 they
don't work. Any advice would be appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top