Change Text to Integer Using a Query/Data Definiation Query

A

AJ

Access 2002

I have a table located in a MS database, this table cannot be modified. I
need to create a new table and change the field properties from text to
integer. I would like to do this someway other than using the design view.

Suggestions or how do you ALTER mutiple columns at once using the Data
Definition Query?

ALTER TABLE SampleB ALTER COLUMN Effect_PL_QualQuestResp INTEGER
 
M

Micah Chaney

Why is the one table not updateable? Is it linked? Is this something you're
going to have to do more than once, or you just need to modify some data one
time only? Is this unupdateable table going to be populated with more
records? Answer these, and then I can help you.
 
A

AJ

Hi Micah,

The table is located on a server and is automatically popluted when survyes
are submitted online.

I need to create another table using the information from the one located on
the server to create calculations. In the very end, produce reports on a
montly basis.
 
M

Micah Chaney

OK Here's what you do:

Create a Query based on that Table.
From the File Menu | Query | Make Table.
Bring all the fields down to the Detail area and Run the Query.

Then go to this new table, and erase all the data, keep the field names just
erase the data.
In the new table, change whatever fields you want from Integer to Text, keep
in mind going the other way could cause problems if there's invalid data in
those fields (ie if there's text in a date field, or text in a number field).

You may want to create a primary key field or a group of primary key fields
so that you can avoid duplicating records. What you'll end up doing is
appending records to this table, over and over again. You don't want to get
a whole lot of duplicate records, because Access will start trippin' if it
gets to big.

Going back to your Query, File Menu | Query | Append. You're going to want
to Append data from the original table to the new table. The reason I have
you doing this twice, is you can save this Query, and utilize it over and
over again, without having to repeat that first step.
Save this Query.

The next time you run this query, it may display an error saying a certain
number of fields could not be appended because of duplicate values, primary
key violations or somethin' like that. This is OK. The query will append
only the new records. What you may want to do is, create a macro, that will
Set Warnings Off and then run this query and then Set Warnings On.

Let me know if this helps.
 
Top