i have a table(patients) with more than 1500 records.this table contains patients information like diagnosis etc.I use cbo boxes in my form to choose info.(i have separate tables to keep this information e.g.table for diagnosis, table for treatment procedures etc.

of course these info are kept in the patients table as text fields.and also i have many query with criteria to select specific records(for example ) parameter value is [Enter diagnosis] and i type for example "cardiac" to get the list of cardiac patients etc.

now , the size has become large . so i want to do somthing to solve this
i designed this when i was very new to access.

how if i want to change the field type from text to number which can look up the required info from other table.

but if i do this , i think that my queries want work .

any advice on this.

i have about ten fields to be changed from text to number.

i dont know if there are any tools to do things like this.

to do it manually, you want to create a new (number) column into which you can add the new numeric refs. you can use a maketable query to populate your new table from the exisiting text data. You can then update the values of the new column by joining the existing table and the new table on the text values. You then change the names of the columns so that your number column is named as the original column.

if the text values were linked to other tables you need to update those in a similar way.

then you need to test your app - as some forms/functions etc may now not work as you are now using longs instead of text.

If the columns currently contain numeric values, you can just change the data type for the column.

PS, if you think 1500 rows is large, what would you think of 9 million rows? That's the size of the transaction file I am working with.