Left join query produces invalid results

  • Thread starter Thread starter Nunya
  • Start date Start date
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.
 
Back
Top