Re: Criteria/Relationship problems in MSAccess97 query on Oracle7 linked table

"Robert Gorski" <rhaje1_at_yahoo.com> wrote in message news:9869de5e.0201041306.3a3b94c0_at_posting.google.com...
> I am trying to query an Oracle7 table from Access97. My criteria is
> on a numeric field (LOT_NO) in a large table of production data
> (LOT_PROD). I'm trying to get all records that have a LOT_NO > 8700.
> I put ">8700" in the criteria in the Access query, and I get records
> back starting with LOT_NO 7626. This query used to work perfectly. I
> figured maybe I had an Access glitch, and tried rebuilding the query
> from scratch, with the same result.
>
> I logged in to our database server, fired up sqlplus, and ran SELECT
> COUNT(*) FROM LOT_PROD; and got 9768543, then I ran SELECT COUNT(*)
> FROM LOT_PROD WHERE LOT_NO > 8700; and got 473235. I know that there
> should be a valid subset. I also know that the records I'm looking
> for should have a PROD_DATE of 12/14/01 at the earliest.
>
> I have another table (LOT_DATA) that uses LOT_NO as its primary key,
> so I set up a relationship in Access between the LOT_NO fields, added
> the LOT_NO field from LOT_DATA to the query, and tried using that as
> my criteria. Then end result was odd:
>
> LOT_PROD.LOT_NO LOT_DATA.LOT_NO PROD_DATE
> 7630 8701 8/27/01
> 7626 8701 8/27/01
> 7632 8701 8/28/01
> 7626 8701 8/27/01
> 7630 8701 8/27/01
> 7724 8702 9/7/01
> 7633 8702 8/28/01
> 7633 8702 8/28/01
> 7633 8702 8/28/01
> etc...

I assume that you link the table with ODBC. Try this: Delete the link and relink the Oracle table. During the process, you will have to specify the primary key. You need to select the proper columns from the Oracle table. If you don't do this, you will see all sorts of strange results. HTH
Matthias Klaey
www.kcc.ch Received on Fri Jan 04 2002 - 16:13:06 CST