make table query data type conversion

[ISTodd]

Hi,

I have a Make Table query that takes data from StudentTable. I need to change the datatype from text to integer in field [gender] where M=1 F=2 and change the data type from text to integer with no decimal points. I hope this is clear enough.. I am very new to Access and any help would be greatly appreciated.

Todd

 

[AlexCuse]

I think you want the Val function? Take a look in the help files.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[lespaul]

I'm more interested in why you want to do this.....


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ISTodd]

Hi - we have a student management database that has certain fields that need to be sent elsewhere (for testing and reporting information)- the data layouts require fields to be in a text file and set up differently. In the past we have always exported to excel and made hand corrections then saved as a text file (took lots of time). I was able to import the excel file into access and by using a make-table query we were able to change the field names, and make some easy changes (like the example of M=1 for [gender]) but the data type needs to be changed on this and a couple other fields. I am able to do this on the new table by hand, but thought that perhaps there was a function I could add to the Make Table query that would do that for me...

Thanks for any help you can offer - Todd

 

[lespaul]

create a query that contains all the information you need directly from the student management database in the format you need and export the query results. If you provide some sample data and expected results I'm sure someone can help you get the query with the correct results.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

What is the actual SQL code of the make table query and which changes are applied manually ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ISTodd]

Hi...
SELECT student_info.id_number AS student_code, student_info.sch_code AS school_code, student_info.f_name AS first_name, student_info.l_name AS last_name, Left([m_name],1) AS middle_initial, IIf([ethnic]="1","5",IIf([ethnic]="2","4",IIf([ethnic]="3","2",IIf([ethnic]
="4","3",IIf([ethnic]="5","1",[ethnic]))))) AS race_code, IIf([sex]="f","1",IIf([sex]="M","2","")) AS gender_code, IIf([gradelvl]="KA","0",IIf([gradelvl]="Kf","0",IIf([gradelvl]="K5f","0",
IIf([gradelvl]="Kp","0",IIf([gradelvl]="6-1","6",IIf([gradelvl]="6-2","6",
IIf([gradelvl]="7-1","7",IIf([gradelvl]="7-2","7",IIf([gradelvl]="8-1","8",
IIf([gradelvl]="8-2","8",[gradelvl])))))))))) AS grade_code, ([birth_date]) AS birthdate INTO [Student Information Data File]
FROM student_info;

Grade_code from text to integer

Gender_code from text to integer

Race_code from text to integer

I made this in query builder so please explain what you did.


(thanks)

Todd

 

[PHV]

Grade_code from text to integer
IIf([gradelvl]="KA",0,IIf([gradelvl]="Kf",0,IIf([gradelvl]="K5f",0,IIf(
[gradelvl]="Kp",0,IIf([gradelvl]="6-1",6,IIf([gradelvl]="6-2",6,IIf(
[gradelvl]="7-1",7,IIf([gradelvl]="7-2",7,IIf([gradelvl]="8-1",8,IIf(
[gradelvl]="8-2",8,Val([gradelvl]))))))))))) AS grade_code

And so on ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ISTodd]

I get a synax error in FROM clause

Ss I said my sql is not great.. could you perhaps tell me what to do with this?

 

[PHV]

How did you manage to change the FROM clause ?
What is your actual SQL code ?
Which sense did you make with my previous post ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886