Update field names using a query

[ktfrubel]

I have to do a daily import from our accounting software. I have queries
created that update a lot of the information that I need. However, I was
wondering if there was a way to do a Make table query or an update query that
I can rename the field names instead of me mannualy doing it each day. After
I clean the data, I have to export it into our leads database and the field
names have to match.
There are about 15 fields in this import that have to be renamed every day,
I was just wondering if I could write some kind of query that could update
the field names for me.

Thanks for any input

 

[smvpegb]

i think that Make a table query is the best option for you.

 

[Maurice]

Why not create an importspecification where you can name the fields the way
you'd like.

Choose -> External Data -> Import

Go through the wizard but don't enter finish at the end
Set all the properties of the import fields and then click the [advanced]
button (lower left)

In the dialog that pops up rename all the fields to the fields you'd like
them to call. Click save importspec.

In code you can use this importspec as follows:

DoCmd.TransferText acExportDelim, [name of your importspec], [tablename to
import in], [FileName and path], True

hth

 

[ktfrubel]

Thanks for the suggestion, but the import comes from Fox Pro systems. I have
to import several tables: company, People, pxfr, and TMS tables from the ODBC
side of things. Then I have several queries that concatinate certain
information into desired fields. Therefore renaming the fields at time of
import cannot be done. After I have run my update and make tables, I would
like to run one last make table (if possible) with the corrected field names.

Why not create an importspecification where you can name the fields the way
you'd like.

Choose -> External Data -> Import

Go through the wizard but don't enter finish at the end
Set all the properties of the import fields and then click the [advanced]
button (lower left)

In the dialog that pops up rename all the fields to the fields you'd like
them to call. Click save importspec.

In code you can use this importspec as follows:

DoCmd.TransferText acExportDelim, [name of your importspec], [tablename to
import in], [FileName and path], True

hth
--
Maurice Ausum

I have to do a daily import from our accounting software. I have queries
created that update a lot of the information that I need. However, I was
wondering if there was a way to do a Make table query or an update query that
I can rename the field names instead of me mannualy doing it each day. After
I clean the data, I have to export it into our leads database and the field
names have to match.
There are about 15 fields in this import that have to be renamed every day,
I was just wondering if I could write some kind of query that could update
the field names for me.

Thanks for any input

 

[John W. Vinson]

Thanks for the suggestion, but the import comes from Fox Pro systems. I have
to import several tables: company, People, pxfr, and TMS tables from the ODBC
side of things. Then I have several queries that concatinate certain
information into desired fields. Therefore renaming the fields at time of
import cannot be done. After I have run my update and make tables, I would
like to run one last make table (if possible) with the corrected field names.

Do the "desired fields" keep changing their names? If so that is suspicious:
fieldnames should be stable.

Could you use an alias for the fieldname in the MakeTable query? What are some
examples of these fieldnames?

John W. Vinson [MVP]

 

[ktfrubel]

Maybe it would be easier to explain the process:
I import out of IBIS to a generic database I use for cleaning the data
before moving it to my Leads database.
I import 4 tables. Company, People, Pxrf and TMS.
I run a few make table queries to concatinate fields.
When all the the queries are left I have about 20 fileds left.
CoPhone, PPhone, Name, Name2,Caddress1, Caddress2 and so on.
What I would like to do is to write a query that will create a new table
that the field names would automatically change
CoPhone = Phone
Name = company
Name2 = company2
Caddress1= Address1
Caddress2 = Address2 and so on.
The way the fields come into the database are how they are exported from
IBIS.
I have to change them so that I can (after all cleaning is done) append them
to my leads database.
does this help?

 

[John W. Vinson]

Maybe it would be easier to explain the process:
I import out of IBIS to a generic database I use for cleaning the data
before moving it to my Leads database.
I import 4 tables. Company, People, Pxrf and TMS.
I run a few make table queries to concatinate fields.
When all the the queries are left I have about 20 fileds left.
CoPhone, PPhone, Name, Name2,Caddress1, Caddress2 and so on.
What I would like to do is to write a query that will create a new table
that the field names would automatically change
CoPhone = Phone
Name = company
Name2 = company2
Caddress1= Address1
Caddress2 = Address2 and so on.
The way the fields come into the database are how they are exported from
IBIS.
I have to change them so that I can (after all cleaning is done) append them
to my leads database.
does this help?

It is NOT necessary to change the fieldnames in a table in order to run either
a MakeTable or an Append query. You can use an Append query appending the
CoPhone field in your cleanup table to the Phone field in your Leads table;
there's no requirement that the names be the same!

I do have to put my Normalization Police hat on and chide you for having
repeated fields. If you have a [Name] (bad choice of fieldname by the way,
it's a reserved word) and Name2 field, someday you'll need a Name3 field or a
Name4 field and then... what? Redesign your table, all your queries, all your
forms, all your reports? Ouch! If you have a one (lead) to many (names or
addresses) field, you need two tables in a one to many relationship.

John W. Vinson [MVP]