I have a table with imported text fields that need to be changed to number fields. I used the CInt function in an update query to change "Male" into 0. Question is, in the table design view, the field still reads as type text. I will need to do calculations on these numbers, can you tell me the best way to do this?
CInt and table
- Thread starter belsha
- Start date
Maybe I am going about this the wrong way. I have a table that has data mostly in text format. I do a make table query from that data and create a table in a new database that still has that data as text. I run an update query to change, for instance, "Male" to 0 and then use the CInt function. Then I have to go into the table and change the field format to Integer? This is going to be done every month so there must be an easier way of doing all these steps?
Why would you need to run a make table query every month?
That doesn't make sense...
Simply see to it you've got your table in your "new" database up and ready having datatype numeric for your gender column.
Do not run an UPDATE query, simply use an INSERT statement.
And decode Male into 0.
RV
That doesn't make sense...
Simply see to it you've got your table in your "new" database up and ready having datatype numeric for your gender column.
Do not run an UPDATE query, simply use an INSERT statement.
And decode Male into 0.
RV
Ken,
That would be OK if the information was already in 0,1 format and I just needed to change it to numeric then append it to a table where the field would be numeric, but I have to convert the Male to 0 before I append it, and NOT do that to the table it's coming from, just in the query.
RV - these are huge tables we are sending off to somewhere else in chunks, so we just want to do temporary tables for that month or quarter, then destroy them. There is another database where that segment of data and much more permanantly resides.
That would be OK if the information was already in 0,1 format and I just needed to change it to numeric then append it to a table where the field would be numeric, but I have to convert the Male to 0 before I append it, and NOT do that to the table it's coming from, just in the query.
RV - these are huge tables we are sending off to somewhere else in chunks, so we just want to do temporary tables for that month or quarter, then destroy them. There is another database where that segment of data and much more permanantly resides.
KenHigg
Registered User
- Local time
- Today, 11:34
- Joined
- Jun 9, 2004
- Messages
- 13,310
This is kind of getting out of hand...
Let's see if I have this correct:
1. You have a table you import every month.
2. The table has a text fld named 'Gender'.
3. You need to count how many of each gender you have.
Is this correct? If this is the only issue you have with the way the table is set up, I see no reason to convert a fld...
a. Am I assuming correctly that in a report you list these records and then at the bottom you need to say something like:
Total Males: 125
Total Females: 136
???
Ken
Let's see if I have this correct:
1. You have a table you import every month.
2. The table has a text fld named 'Gender'.
3. You need to count how many of each gender you have.
Is this correct? If this is the only issue you have with the way the table is set up, I see no reason to convert a fld...
a. Am I assuming correctly that in a report you list these records and then at the bottom you need to say something like:
Total Males: 125
Total Females: 136
???
Ken
No. I have a table with many fields that are text. Through a make table query I am taking the fields I need and exporting them into a new database. This new database will be created each month, the information sent to someone else, then destroyed. The someone else needs the information for many fields in numeric form, such as Male=0 for their purposes. They cannot use it in text form. SO,
In DB 1, I have a field called Gender that has "Male" or "Female"
In DB 2 a make table query creates a table with the Gender field, which then shows "Male" or "Female" from DB 1.
What I need is in DB2 Gender to be integer, 1 or 2.
Need the most efficient way to do that. I do not want to change the data in the DB1 table to 0,1 though, I want that to stay "Male" or "Female"
In DB 1, I have a field called Gender that has "Male" or "Female"
In DB 2 a make table query creates a table with the Gender field, which then shows "Male" or "Female" from DB 1.
What I need is in DB2 Gender to be integer, 1 or 2.
Need the most efficient way to do that. I do not want to change the data in the DB1 table to 0,1 though, I want that to stay "Male" or "Female"
Referring to my former reply.
If you really DO need to change the datatype of your Gender column, you can't do this within your make table query, simple at that.
So the option is manually changing the datatype or using an ALTER TABLE statement to change it.
RV
If you really DO need to change the datatype of your Gender column, you can't do this within your make table query, simple at that.
So the option is manually changing the datatype or using an ALTER TABLE statement to change it.
RV
