Adding to table design

[Eric G]

Can someone please tell me how I can add a new field to an existing
back-end table and then populate it with data that I'd like to import
from a .txt file?

So the table currently has LastName, FirstName and HomeForm.
I'd like to bring in a fourth field's worth of data. How do I align
the records properly?

Also, I'll be adding about 50 or so names to the end of a list of
approx. 650 names. What's the best way to accomplish both tasks?

Thanks! Eric

 

[John Vinson]

Can someone please tell me how I can add a new field to an existing
back-end table and then populate it with data that I'd like to import
from a .txt file?

You will need to open the backend database directly and change the
table design (add the new field).

So the table currently has LastName, FirstName and HomeForm.
I'd like to bring in a fourth field's worth of data. How do I align
the records properly?

Given that I don't know what your data is like, I have no idea; what
information exists in the text file and in the table which would let
you do so? Note that a Table HAS NO ORDER, so if you're trying to
match "the first record" with "the first record" and so on, you're in
trouble. You must - no option - have a unique field (or set of up to
ten fields) that you can use as a link between the text file and the
table. If you have such a field (and note that names are NOT unique,
you may well have two people named Jim Smith!) you can create an
Update query.

Also, I'll be adding about 50 or so names to the end of a list of
approx. 650 names. What's the best way to accomplish both tasks?

An Append query is the ticket here.

 

[Eric G]

HI John,

You will need to open the backend database directly and change the
table design (add the new field).

OK I got this part. It was very easy to do.

An Append query is the ticket here.

OK, this was easy too.

Given that I don't know what your data is like, I have no idea; what
information exists in the text file and in the table which would let
you do so? Note that a Table HAS NO ORDER, so if you're trying to
match "the first record" with "the first record" and so on, you're in
trouble. You must - no option - have a unique field (or set of up to
ten fields) that you can use as a link between the text file and the
table. If you have such a field (and note that names are NOT unique,
you may well have two people named Jim Smith!) you can create an
Update query.

Here's where I can really use some help.
My original table had 679 first/last names plus a third homeform
field.
Now that my table has been updated (I have appended around 30 names to
the table) I have 710 records.

The table has its index field called StudentID (an autonumber field).
I have left a good number of names in this list of students who have
been "deleted" from the office register. They aren't doing any harm in
this table so I'm leaving them for now (probably around 50 or so
names).

So the big question, how do I get data from one field of a .txt file
that has the latest student register into my updated student table?
When I appended the 30 records above, I made sure to bring in this new
field with the other three.
But all the other 670 odd records don't have this new field's data
yet.

The only way I can make sense of it would be to somehow sort the names
alphabetically and append the field's data if it could appended
alphabetically. This is because there is no key field to relate the
two tables of data. I hope you can understand my problem.

Of course there's live data on the server tables. I can't just throw
in a fresh table of names with the new fields and their data.. It
would screw up all the other tables/data.

I'd appreciate your help here.

 

[John Vinson]

So the big question, how do I get data from one field of a .txt file
that has the latest student register into my updated student table?
When I appended the 30 records above, I made sure to bring in this new
field with the other three.
But all the other 670 odd records don't have this new field's data
yet.

The only way I can make sense of it would be to somehow sort the names
alphabetically and append the field's data if it could appended
alphabetically. This is because there is no key field to relate the
two tables of data. I hope you can understand my problem.

If - and it's a VERY BIG IF, for that many people! - you have no
duplicate names (i.e. if there are no two people in these 670 who
happen to have the same name, like Jim Smith and Jim Smith), you can
create a unique Index on the combination of first name and last name.
You can then create an Update query joining the two tables on these
names to update the field.

If there are two records in your text file for Jim Smith (different
people), and two records in your table for Jim Smith, how would the
computer - or you yourself - know which is which? That's why a unique
ID is absolutely essential!

 

[Eric G]

Thanks John for the tip. Eric

If - and it's a VERY BIG IF, for that many people! - you have no
duplicate names (i.e. if there are no two people in these 670 who
happen to have the same name, like Jim Smith and Jim Smith), you can
create a unique Index on the combination of first name and last name.
You can then create an Update query joining the two tables on these
names to update the field.

If there are two records in your text file for Jim Smith (different
people), and two records in your table for Jim Smith, how would the
computer - or you yourself - know which is which? That's why a unique
ID is absolutely essential!