Oracle to Access

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I am trying to create an Access dB with tables imported from an Oracle
dB....ultimately the data extracted from the Access dB will be in
Excel and dB4 format

problems:
the linked tables produce multiple rows of the same data....so I
created an imported table for each of the linked tables....I then
created a query that deletes the data out of the impoted tables then
appends the data from the linked tables to the imported ones....the
results were better but still gleans multiple rows

How can I code an expression that will show the first row of data only
and not the additional rows of the replicated data?

Also how do I just refresh/update a linked table without having to
import ALL of the data each time ?

Is there a better way to deal with linked tables?

Thanks in advance for your advice

Ray
 
Ray

Your terminology is a little confusing. It sounds like you have both linked
and imported tables.

If you only had linked tables, you could use a query, and the UniqueValues
(i.e., DISTINCT) property to see only one copy of each row.

Out of curiosity, why does the Oracle db have duplicate rows?
 
Thanks for responding Jeff
I'm sure my terminology is confusing...I'm not trained in Access at
all...just picked up stuff on my own. And yes it does have linked
aaand imported tables....I was using the imported tables hoping I
could eliminate the duplicate rows.....The Oracle dB does not have
duplicate rows....the dup rows occur AFTER I add more than 2 tables to
the query.....I need to use several tables from the Oracle dB to get
the data I need......

Thanks
Ray
 
Jeff, I hope you don't mind my joining in here.

Ray, it sounds like you may not have defined the
relationships between your tables in your query.
Normally, when inserting multiple tables to a query, you
would then define the relationships between them to
determine the output.

To do this in the query builder, you would click on a
related field in one table and then drag to the related
field in another table (for example, they may both share
an employee id number). Sometimes, you need to relate
more than one field to uniquely identify a relationship,
such as a fund and account number. In those cases, you
would drag lines between all fields necessary to define
the relationship.

By default, when you drag these lines to define
relationships you will get an INNER JOIN. This means
that the query results will only be those records that
had matches in all tables. You can double-click the join
lines to change these to OUTER JOINS, which will display
all records from one table, and only matching records
from the other tables.

Without the joins defined between tables, Access just
displays every combination of records, which in most
cases is not desired (although there are some cases that
it can be useful).

If you were missing the joins, I believe this should take
care of your problem and you won't have to import the
data any longer, just use the linked tables.

Post back if this was off the mark, or if you have other
questions about table joins.

-Ted Allen
 
Thanks Ted...I'll try it & let you know......I do have some
relationships and will try additional ones
Ray
 
Back
Top