Adding a new Field to a Linked (or Imported) Table 
Author Message
 Adding a new Field to a Linked (or Imported) Table

Hi there everybody,

I posted a question a couple of weeks ago and people were nice enough to
help me. Now, I have another problem, and I can't quite seem to figure it
out.

Here is my problem:

I am running Access 97, and I have linked tables that I use to get at data.
Unfortunately, due to the naming procedures of the program that I am
importing this data from, I am having a problem. Basically, some clients it
puts an ID number (as a double), and others it puts an ID number (as a
text -- with a letter in front of an otherwise numeric ID number). But it
only adds the text identifier in ONE of the tables, not all of them. So the
ID for the same client may be "xxx" in one table, but "Gxxx" in another.

Now, what I want to do is add a field to the table that contains the ID
number without the text (since some of the IDs are the same, I need a way to
identify different clients, and would like to keep using this program's
semi-unusual naming convention, since I have to deal with it anyway). In the
newly added field, I would like to make the field contain the number in the
"ID" field, plus the letter "G". FYI, it is currently a linked dBase III
file, and I would prefer to keep it that way, though it's not entirely
necessary.

If anyone can help me, I would greatly appreciate it.

Thanks for listening,

Jason Spezza



Sun, 20 Jul 2003 01:26:05 GMT  
 Adding a new Field to a Linked (or Imported) Table
Jason,

Do you really need to add it to the actual table or can you do what you want
at the query level with a contrived field? If the query route will work put
something like the following into a column of the QBE query grid.

NewID:"G" & [OldID]

This should now create a field in the query that you can sort, filter or
link by.

--
Gary Miller
Gary Miller Computer Services
Sisters, OR


Quote:
> Hi there everybody,

> I posted a question a couple of weeks ago and people were nice enough to
> help me. Now, I have another problem, and I can't quite seem to figure it
> out.

> Here is my problem:

> I am running Access 97, and I have linked tables that I use to get at
data.
> Unfortunately, due to the naming procedures of the program that I am
> importing this data from, I am having a problem. Basically, some clients
it
> puts an ID number (as a double), and others it puts an ID number (as a
> text -- with a letter in front of an otherwise numeric ID number). But it
> only adds the text identifier in ONE of the tables, not all of them. So
the
> ID for the same client may be "xxx" in one table, but "Gxxx" in another.

> Now, what I want to do is add a field to the table that contains the ID
> number without the text (since some of the IDs are the same, I need a way
to
> identify different clients, and would like to keep using this program's
> semi-unusual naming convention, since I have to deal with it anyway). In
the
> newly added field, I would like to make the field contain the number in
the
> "ID" field, plus the letter "G". FYI, it is currently a linked dBase III
> file, and I would prefer to keep it that way, though it's not entirely
> necessary.

> If anyone can help me, I would greatly appreciate it.

> Thanks for listening,

> Jason Spezza



Sun, 20 Jul 2003 02:27:45 GMT  
 Adding a new Field to a Linked (or Imported) Table
In your query create an expression using the "right" function...

ID: Right(ClientID, 3)

"ID" is your new field name, "ClientID" is the field currently holding
the ID from the linked table and "3" being how many characters you want
to keep from ClientID.

:)
Karen



Quote:
> Hi there everybody,

> I posted a question a couple of weeks ago and people were nice enough
to
> help me. Now, I have another problem, and I can't quite seem to
figure it
> out.

> Here is my problem:

> I am running Access 97, and I have linked tables that I use to get at
data.
> Unfortunately, due to the naming procedures of the program that I am
> importing this data from, I am having a problem. Basically, some
clients it
> puts an ID number (as a double), and others it puts an ID number (as a
> text -- with a letter in front of an otherwise numeric ID number).
But it
> only adds the text identifier in ONE of the tables, not all of them.
So the
> ID for the same client may be "xxx" in one table, but "Gxxx" in
another.

> Now, what I want to do is add a field to the table that contains the
ID
> number without the text (since some of the IDs are the same, I need a
way to
> identify different clients, and would like to keep using this
program's
> semi-unusual naming convention, since I have to deal with it anyway).
In the
> newly added field, I would like to make the field contain the number
in the
> "ID" field, plus the letter "G". FYI, it is currently a linked dBase
III
> file, and I would prefer to keep it that way, though it's not entirely
> necessary.

> If anyone can help me, I would greatly appreciate it.

> Thanks for listening,

> Jason Spezza

Sent via Deja.com
http://www.deja.com/


Sun, 20 Jul 2003 02:28:07 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. How to modified existing field and add new field in existing table for Access database

2. Adding a Field to a Linked table

3. Adding a field to a linked table

4. Add field to linked table

5. Create new fields in a table based off of fields in another table

6. Create new field in existing table exactly like field in second table

7. Linked Table VS Import Table

8. Linked Table VS Import Table

9. Adding a new field to a table

10. Crystal Report cannot see new field that is added to MS Access table

11. Adding new fields to an existing table

12. Add New Field in existing Table (VB6)

 

 
Powered by phpBB® Forum Software