I have a link table that is attached to an existing table which has no unique ID for each record. I do not have ownership of the table that I am linking to and need each record to have a unique ID. Is it possible to add an autonumber field to the table when it links?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Is it possible to add an autonumber field to a linked table
- Thread starter foundryqa
- Start date
I guess you've tried this and it didn't work?
Presumably the target table does have a key even if not in the form of a single field. What is stopping you from using that?
My concern is if you create a new id field, nothing in your, or their, database relates to it so you are going to have to go through a lot of processing to match it to meaningful data. That meaningful data will come from the key of the target table. So why not use that directly?
Remember if you could add a new column, it will change every time you refresh the target database. Seems dodgy to me. mike.stephens@bnpparibas.com
Presumably the target table does have a key even if not in the form of a single field. What is stopping you from using that?
My concern is if you create a new id field, nothing in your, or their, database relates to it so you are going to have to go through a lot of processing to match it to meaningful data. That meaningful data will come from the key of the target table. So why not use that directly?
Remember if you could add a new column, it will change every time you refresh the target database. Seems dodgy to me. mike.stephens@bnpparibas.com
Hi foundryqa,
Here's a couple of options seeing as it's possible you're pulling in data from a flat table with no unique record id:
Rather than linking, you might consider to import the table and using either a saved import specification which would add a unique ID, or a group of queries and some code to fish through this incoming data, compare it to perhaps some existing, then append unique records to another table already defined in your Db.
As above, what's the objective? If your unique ID is there to relate 'one' to many elsewhere in your Db, then you may have to look at another field or fields to define the record as unique. This is where queries or code can come in rather handy.
Perhaps you could paste in a couple of 'typical' records that you're linking to, here, to study where the 'uniqueness' is?
Ideas?
Gord
gord@ghubbell.com
Here's a couple of options seeing as it's possible you're pulling in data from a flat table with no unique record id:
Rather than linking, you might consider to import the table and using either a saved import specification which would add a unique ID, or a group of queries and some code to fish through this incoming data, compare it to perhaps some existing, then append unique records to another table already defined in your Db.
As above, what's the objective? If your unique ID is there to relate 'one' to many elsewhere in your Db, then you may have to look at another field or fields to define the record as unique. This is where queries or code can come in rather handy.
Perhaps you could paste in a couple of 'typical' records that you're linking to, here, to study where the 'uniqueness' is?
Ideas?
Gord
gord@ghubbell.com
foundryqa
The above questions are valid...
I've had to temporarily add an index/ID field to tables to use Dlookup for comparing a field value from one record to the next.
The ALTER TABLE works for this
This adds a field that simply numbers the records.
When I'm done...
removes the column.
HTH John
Use what you have,
Learn what you can,
Create what you need.
The above questions are valid...
I've had to temporarily add an index/ID field to tables to use Dlookup for comparing a field value from one record to the next.
The ALTER TABLE works for this
Code:
ALTER TABLE TableName ADD COLUMN FieldName COUNTER;When I'm done...
Code:
ALTER TABLE TableName DROP COLUMN FieldName;removes the column.
HTH John
Use what you have,
Learn what you can,
Create what you need.
- Status
Similar threads
