Cleaning up an old table with existing data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, all,

Running Access 2003. I have a table with nearly 2000 data records and many
fields that are now obsolete.

I would like to be able to remove the obsolete fields.

I don't want to lose or damage the remaining data.

I've considered creating a new table with only the fields I want and then
moving the data to the new table.

The easiest way for me to do this would be to make a copy of the existing
table and edit it, but there doesn't seem to be a way to do that. Does
anyone know if this is possible to do?

What I am thinking is that it would be really easy if I could
1) copy the old table into one with a new name
2) remove all the data records from the new table
3) modify the field definitions, and then
4) move the data that I want to save (using query) into the new table.
Of course, I would want the new table to have the original table name.

Is this possible to do? What are the dangers, if any? What things do I
need to think about that might catch me up later?

Thanks in advance,
Nedda
 
Presumably this is going to be a one-time requirement only, so you don't
need to know how to do it programmatically.

Select the table in which you're interested in the database window, and
select Edit | Copy from the menu. Select Edit | Paste from the menu, and
you'll be presented with a dialog that asks you for the name to use for the
copy, and whether or not you want to copy the structure only, the structure
and data, or append the data to an existing table. You want the "Structure
Only" option.

Make your design changes to the new table, then create an Append To query to
take the data from the old table and write it to the new table.

On the other hand, if all you're concerned about is removing some fields
from the table, you can do that easily. Open the table in Design view,
select the field(s) you want to delete, then choose Edit | Delete Rows from
the menu.
 
Hi Nedda,
I would be tempted to make a query with the existing table as the source and
in the data columns of the query just put the fields that you require - then
change the query type to "make table" and run - this will make the new table
without touching the data in the old table.
If your new tabe is OK then name it and save it - keep your original table
for future rerference.
Good luck,
Frank
 
Hi, Douglas,

Yes, this is a one-time project. However, I do intend to redsign the fields
that currently contain phone numbers, for each phone number splitting the 10
digit numbers into 3 fields, one for area code, one for each of the other
parts (can't think of what to call them right now). I know I will have to
take these apart, and perhaps I can do that in the original table using a
query and then just move those pieces into the new table.

I am not a programmer, but I think there are functions I can use to split up
the pieces and load only the segments I want in each case, right?

Thanks so much for your help.
Nedda
 
Hi, Hilly,

Thanks for your idea. I didn't know there is a query type called "make
table". I will certainly consider this option, especially as it means I
don't have to define each new field in all it's details.

Nedda
 
Take a look at the Left, Mid, Right and InStr functions for how to extract
specific parts of a text field. Depending on the data, the Split function
might also be of use.
 
Thanks for your suggestion, Douglas. However, I've used the "double"
datatype and stored these as a number.

Do I write the number into a text field first and then use functions to
split it up, or are there functions that will allow me to take about a number
in the "double" datatype?

Your knowledge in this area is greatly appreciated.

Thanks in advance.
Nedda
 
HomePhone is a field with datatype "double". It stores numbers. So a phone
number is stored as a 10 digit numeric, 8606515771 instead of 860-651-5771.

I want to store the phone number in 3 separate pieces without the hyphens.
How do I take the number apart?

Or, I want to store the number with hyphens, in which case I have to take it
apart, insert the hyphen and then put in the next set of digits, the next
hyphen, etc.

Thanks,
Nedda
 
Hopefully you're also convert the component parts of the phone number as
text, not numbers. While phone numbers and certain IDs (Social Security
Numbers, Social Insurance Numbers, etc.) may look like numbers, they should
always be stored as text, not numbers, since formatting is important
(leading zeroes, for example).

You can convert your number to a string using the Format function:

Format([HomePhone], "###-###-####")

You can then use standard text manipulation functions, so that the area code
would be Left(Format([HomePhone], "###-###-####", 3), the exchange would be
Mid(Format([HomePhone], "###-###-####"), 5, 3) and the remainder of the
number would be Right(Format([HomePhone], "###-###-####"), 4). You could
also use the Split function:

Split(Format([HomePhone], "###-###-####"), "-")(0)
Split(Format([HomePhone], "###-###-####"), "-")(1)
Split(Format([HomePhone], "###-###-####"), "-")(2)

respectively.
 
Thanks, Douglas. I'll give this a try and let you know how it worked out.
Nedda

Douglas J. Steele said:
Hopefully you're also convert the component parts of the phone number as
text, not numbers. While phone numbers and certain IDs (Social Security
Numbers, Social Insurance Numbers, etc.) may look like numbers, they should
always be stored as text, not numbers, since formatting is important
(leading zeroes, for example).

You can convert your number to a string using the Format function:

Format([HomePhone], "###-###-####")

You can then use standard text manipulation functions, so that the area code
would be Left(Format([HomePhone], "###-###-####", 3), the exchange would be
Mid(Format([HomePhone], "###-###-####"), 5, 3) and the remainder of the
number would be Right(Format([HomePhone], "###-###-####"), 4). You could
also use the Split function:

Split(Format([HomePhone], "###-###-####"), "-")(0)
Split(Format([HomePhone], "###-###-####"), "-")(1)
Split(Format([HomePhone], "###-###-####"), "-")(2)

respectively.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Nedda said:
HomePhone is a field with datatype "double". It stores numbers. So a
phone
number is stored as a 10 digit numeric, 8606515771 instead of
860-651-5771.

I want to store the phone number in 3 separate pieces without the hyphens.
How do I take the number apart?

Or, I want to store the number with hyphens, in which case I have to take
it
apart, insert the hyphen and then put in the next set of digits, the next
hyphen, etc.

Thanks,
Nedda
Click to expand...
Click to expand...
 
Back
Top