combine an outside table and a new table

[hawley]

I have a table that I got from Oracle. I did the "Get External Data". What I want to do is add and take away some fields and make a new table. I want to update the new table with info from Oracle when it changes. Any ideas?

Here is an example of what I am talking about.
I want the vendor_id and vendor_name to always be the same on both tables so that if the Oracle table is updated with new vendors then add them to the new table. But I never want to be able to change the Oracle table.

Table from Oracle - New Table -
Vendor_Id Vendor_Id
Vendor_Name Vendor_Name
Vendor_Status Ad_Send_Nbr

If you need more info or need to explain more let me know.

Thanks-

 

[JeremyNYC]

Link to the Oracle table, instead of importing it. If you want to add fields, make a table in your database that has the PK of the Oracle table and the new fields. You can add data for those fields whenever needed here, you'll just have to grab the PK from the linked table and put it in the new table, too. If you want to rename fields, just make a query to do that. That same query can be used to join (LEFT JOIN) the Oracle table with the new table, so that all of the data are in one place.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[hawley]

I am still having trouble with this. I linked the Oracle table. When I create a new table and added the fields vendor_id and vendor_name from the Oracle table what I get is a dropdown box. I don't want that. I actually want the data to display the same way it does in Oracle and then add a new field named ad_send_nbr so I can put in new info about the vendor. Any ideas or suggestions.

Thanks-

 

[JeremyNYC]

Where are you doing this that you get a combo box? Vendor name is in the old table, so you should not add that to the new table. The display of the date is a trivial--that can be changed on any form using the Format function.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[hawley]

I am creating a new table and on the data type I pick "Lookup Wizard". I then choose the Oracle table I want. I pick vendor_id and vendor_name from it. Then I add a new field called ad_send_nbr. When I look at the datasheet view that is where the vendor_id and vendor_name are dropdown boxes. How do I just get the data from Oracle to appear in those two fields?

Thanks-

 

[JeremyNYC]

First off, kill the wizards. They do too much, and often do it incorrectly. You are using a lookup wizard. What that does is set up your table to display data using combo boxes. End of story. Don't use the wizard. Don't add the name field to the new table. Just the ID and ad_send_nbr. Then put the new table and the old table together in a query, linked on the ID field.

Check out the Fundamentals article on my website. It will help.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[hawley]

Jeremy - Thanks for the article. It is very hepful. Although I do still have some questions.

I understand what you are saying when you say link the old and new table together. But how am I to do that when the new table does not have any vendor_id's? Do I have to copy the column vendor_id from the Oracle table to the new one? Or is there a way to link those columns together? What I want to do is have the vendor_id the excat same on the new table as it is in the Oracle table. The reason I want this is because if we add a new vendor I don't want to have to go in the new table and manual update the vendor id's. Is this even possible?

Example-
Oracle New Table
vnd_id vnd_nm vnd_id ad_snd_nbr
1 Journal 5 123
2 Newspaper 1
3 Paper 2 125
4 Magazine 3 126
5 Another One 4 124
Vendor Id
Same as Oracle

Thnaks-

 

[JeremyNYC]

Hawley,

Where are the ad_snd_nbr values coming from?

Jeremy
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[hawley]

The user will enter the ad_snd_nbr.
I think what I want to do is a one way replication.
Does that sound right?

Thanks-

 

[JeremyNYC]

Well, if the user is to enter these data by hand, I would say don't bother creating records until the data are entered. If you base a bound form on an OUTER JOIN query, Access will add the records to the new table when you the user adds data to the appropriate control.

Here's the query (just pick the appropriate fields from the Oracle table, to make it as fast as possible:
SELECT OracleTable.*, NewTable.ad_snd_nbr
FROM OracleTable LEFT JOIN NewTable
ON OracleTable.vnd_id = NewTable.vnd_id

Then include all the controls on the form. When you put something in the control that holds ad_snd_nbr, access will create a record in NewTable and populate vnd_id with the value from that field in OracleTable.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[JeremyNYC]

And no, I would not recommend using replication for this.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.