Inconsistent return

Hi All,

Here’s my situation using Access 2003. I have a linked table to a VFP data
source (using ODBC for VFP). I have a query that trims the fields so I can
join one field to another key field of a local table. Before I use this
query, I have a series of other queries. 3 queries with same structure (each
with 3 tables using left outer joins). These 3 queries are unioned together.
The union query is joined to the VFP query mentioned above as well as
another local table. This final query is suppose to show me any records from
the unioned query that don’t show up in either of the other 2 tables. This
is returning inconsistent results. I will get anywhere from 1 to 3
additional records when I run it repeatedly (not necessarily the same
records). Sometimes it’s accurate as far as I (there is one prevalent
return). The union queries have appropriate indexes as does the VFP table.
I have another database using a Pass-Through query to VFP tables that is
doing the same thing. Can someone help me? Is it the VFP database (server),
the VFP driver, Access or possibly the network (not so reliable). The
additional (errant) records are in the VFP table and should not have been
returned in the query. Here’s the final query:

SELECT qryUnits.UID, qryCon.TID, qryUnits.Location, qryUnits.Date,
tblPlate.SID
FROM (qryUnits LEFT JOIN qryCon ON qryUnits.FIN = qryCon.UID) LEFT JOIN
tblPlate ON qryUnits.FIN = tblPlate.SID
WHERE (((qryCon.TID) Is Null) AND ((tblPlate.SID) Is Null));

Thanks for any help/advise.
LeAnn

I believe I have found the answer. There is a "FetchInBackgroud" option when
creating the ODBC driver. If unchecked, the query returns accurately.

"LeAnn" wrote:

Hi All,

Here’s my situation using Access 2003. I have a linked table to a VFP data
source (using ODBC for VFP). I have a query that trims the fields so I can
join one field to another key field of a local table. Before I use this
query, I have a series of other queries. 3 queries with same structure (each
with 3 tables using left outer joins). These 3 queries are unioned together.
The union query is joined to the VFP query mentioned above as well as
another local table. This final query is suppose to show me any records from
the unioned query that don’t show up in either of the other 2 tables. This
is returning inconsistent results. I will get anywhere from 1 to 3
additional records when I run it repeatedly (not necessarily the same
records). Sometimes it’s accurate as far as I (there is one prevalent
return). The union queries have appropriate indexes as does the VFP table.
I have another database using a Pass-Through query to VFP tables that is
doing the same thing. Can someone help me? Is it the VFP database (server),
the VFP driver, Access or possibly the network (not so reliable). The
additional (errant) records are in the VFP table and should not have been
returned in the query. Here’s the final query:

SELECT qryUnits.UID, qryCon.TID, qryUnits.Location, qryUnits.Date,
tblPlate.SID
FROM (qryUnits LEFT JOIN qryCon ON qryUnits.FIN = qryCon.UID) LEFT JOIN
tblPlate ON qryUnits.FIN = tblPlate.SID
WHERE (((qryCon.TID) Is Null) AND ((tblPlate.SID) Is Null));

Thanks for any help/advise.
LeAnn