Records mimicking each other

J

Jen

OK I have a query that pulls records from a linked Oracle table. When pulling
these records they all look the same in the query even if they are different
in the table i.e.

Record 1: 5 3N 6W SESE
Record 2: 5 3N 6W SESE

When the should appear as

Record 1: 5 3N 6W SESE
Record 2: 5 3N 6W NWSE
The 5 3N 6W having been the criteria.

If I change the SQL to include Select Distinct it shows the records
correctly but I am then unable to update them.

I need to see them correctly as well as be able to update the records.

Any thoughts?

Jen - slvrrsepdl
 
J

John Vinson

OK I have a query that pulls records from a linked Oracle table. When pulling
these records they all look the same in the query even if they are different
in the table i.e.

Record 1: 5 3N 6W SESE
Record 2: 5 3N 6W SESE

When the should appear as

Record 1: 5 3N 6W SESE
Record 2: 5 3N 6W NWSE
The 5 3N 6W having been the criteria.

If I change the SQL to include Select Distinct it shows the records
correctly but I am then unable to update them.

I need to see them correctly as well as be able to update the records.

Any thoughts?
Click to expand...

It sounds like there are in fact multiple records in the Oracle table
with values 5 3N 6W SESE... and you're seeing all of those records.

If there are indeed multiple records, which record do you want to
update? Any DISTINCT query will of necessity be non-updateable, since
the one record that you see represents multiple records in the
underlying table.


John W. Vinson[MVP]
 
J

Jen

There is only the one record with SESE and one with the NWSE, they are the
only entries for 5 3N 6W. I verified this with a different program that
allows us to query the tables. I ran the query with the criteria of NWNE
instead of the 5 3N 6W and it produced the SESE. We have a separate query
that produces a report for the information as well and it produces both
entries correctly, the queries are nearly identical.

Jen
 
J

John Vinson

There is only the one record with SESE and one with the NWSE, they are the
only entries for 5 3N 6W. I verified this with a different program that
allows us to query the tables. I ran the query with the criteria of NWNE
instead of the 5 3N 6W and it produced the SESE. We have a separate query
that produces a report for the information as well and it produces both
entries correctly, the queries are nearly identical.
Click to expand...

Well, evidently there's something wrong with the query. Could you post
the SQL view of it? I obviously can't tell what's wrong without seeing
what you're doing! Perhaps there's a join to some other table?

John W. Vinson[MVP]
 
B

BradJones

John Vinson said:
Well, evidently there's something wrong with the query. Could you post
the SQL view of it? I obviously can't tell what's wrong without seeing
what you're doing! Perhaps there's a join to some other table?

John W. Vinson[MVP]
Click to expand...

I believe that the problem lies with the linked Oracle table. Either there
is no index on the table on Oracle or you did not choose the proper field(s)
to create a unique index when you linked the table in Access.

If possible, find out the index/indices on the Oracle table. Relink the
table and choose the fields that make up the index. View the table to see
whether you records are unique.

If not possible, use pass-through queries to capture the data.

Good Luck
 
J

Jen

So I created a new database, imported the queries and reports and whala it
worked! I still have no clue why it wasn't working but it is now. Thanks for
your help.
 
Top