Data missing from 2 table query

[jburger9]

Hi all,

I am attempting to run a query based on 2 tables.

In the query there are 3 fields from table 1 and 2 fields from table 2.

The common field in both is Change.ID

The query is working however there is about 70% of the data missing from the 2 fields in table 2

I have checked and the data missing is definitely there if I check manually in table 2.

I have tried the 3 different joins available, and only join that works is number 3 (include all records from table 1 and only those records from table 2 where the joined fields are equal.

The SQL is as below

SELECT Table1.Approver_Group, Table1.Approval_Status, Table1.Change_ID, Table2.Brief_Change_Details
FROM Table2 RIGHT JOIN Table1 ON Table2.Change_ID = Table1.Change_ID
WHERE (((Table1.Approver_Group) Like "example"))
ORDER BY Table1.Change_ID DESC
WITH OWNERACCESS OPTION;


I have searched for days, but cannot solve this one.

Thanks in advance
Gareth

 

[DCrake]

1.Try using wildcards round the example %example% or *example*
2. try changing Like to =

Try bringing down the primary key from each table and verify their existance when run as seperate views.

 

[jburger9]

Really appreciate the reply.

I tried this however still didnt work unfortunately.
There are no primary keys as the tables are ODBC links and I have checked and the missing data is definitely in the tables.

Thanks

 

[DCrake]

Have you tried removing the With Owner Access options. There may be restrictions on certain records

 

[jburger9]

Just tried it now and did the same

Its about 2/3 of the column thats blank, the rest are OK. Its not as if there were missing records sporadically, there is a clear divide.....Its very strange.

 

[DCrake]

Try importing the SQL data into access seperately for the records that contain example then try and make a query between the two using the access tables. Another trial and elimination exercise.