Selecting from a table and inserting into another table's column of a different type using query in ms access.

Question

I have some txt files that contain tables with a mix of different records on them which have diferent types of values and definitons for columns. I was thinking of importing it into a table and running a query to separate the different record types since a identifier to this is listed in the first column. Is there a way to change the value type of a column in a query? since it will be a pain to treat all of them as text. If you have any other suggestions on how to solve this please let me know as well.

Here is an example of tables for 2 record types provided by the website where I got the data from

create table dbo.PUBACC_A2
(
      Record_Type               char(2)              null,
      unique_system_identifier  numeric(9,0)         not null,
      ULS_File_Number           char(14)             null,
      EBF_Number                varchar(30)          null,
      spectrum_manager_leasing  char(1)              null,
      defacto_transfer_leasing  char(1)              null,
      new_spectrum_leasing      char(1)              null,
      spectrum_subleasing       char(1)              null,
      xfer_control_lessee       char(1)              null,
      revision_spectrum_lease   char(1)              null,
      assignment_spectrum_lease char(1)              null,
      pfr_status        char(1)          null

)

go
create table dbo.PUBACC_AC
(
      record_type               char(2)              null,
      unique_system_identifier  numeric(9,0)         not null,
      uls_file_number           char(14)             null,
      ebf_number                varchar(30)          null,
      call_sign                 char(10)             null,
      aircraft_count            int                  null,
      type_of_carrier           char(1)              null,
      portable_indicator        char(1)              null,
      fleet_indicator           char(1)              null,
      n_number                  char(10)             null
)

Answer 1

If you have a column defined as text, because it has both alphas and numbers, you'll only be able to query it as if it were text. Once you've separated out the different "types" of data into their own tables, you should be able to change the schema definition. Please comment here if I'm misunderstanding what you're trying to do.

Answer 2

Yes, you can do what you want. In ms access you can use any VBA functions and with some

IIF(FirstColumn="value1", CDate(SecondColumn), NULL) as DateValue,
IIF(FirstColumn="value2", CDec(SecondColumn), NULL) as DecimalValue,
IIF(FirstColumn="value3", CStr(SecondColumn), NULL) as StringValue

You can use all/any of the above in your SELECT.

EDIT:

From your comments it seems that you want to split them into different tables - importing as text should not be a problem in that case.

a) After you import and get it in the initial table, create the proper table manually setting you can INSERT into the proper table.

b) You could even do a make table query, but it might be faster to create it manually. If you do a make table query you have to be sure that you have casted the data into proper type in your select.

EDIT2: As you updated the question showing the structure it becomes obvious that my suggestion above will not help directly.

If this is one time process you can follow HLGEM's solution. Here are some more details.

1) Import into a table with two columns - RecordType char(2), Rest memo

2) Now you can split the data (make two queries that select based on RecordType) and re-export the data (to be able to use access' import wizard)

3) Now you have two text files with proper structure which can be easily imported

Answer 3

I did this in my last job. You start with a staging table that has one column or two coulmns if your identifier is always the same length. Then using the record identifier, you move the data to another set of staging tables, one for each type of record you have. This will be in columns for the data and can have the correct data types. Then you do any data cleaning you need to do. Then you insert into the real production table.