By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,422 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,422 IT Pros & Developers. It's quick & easy.

Updating Field Names in Tables using data definition query?

P: 1
Is it even remotely possible to update field names to the correct field name for the same table using a data definition query? We have a utility that spits out data in an Access database for use in reporting (can't really go into too much detail...it's a legal application). Anyway, I always have to customize the field names every single time a production is to go out. The original field names are always the same, and the field names I want to change them to are always the same on a case by case basis. I would love to figure out how to customize the query so that it automatically updates the field names...this would cut down a lot of time spent on changing each field name individually.

And yes, I am inexperienced in SQL but I can understand the logic when reading the statements (I just can't spit out the lingo).

TIA,
Jacey
Jul 18 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
In your circumstances I would devise queries which alias the fields concerned, giving them the names you want them to have, then use these for export/reporting/further processing.

Place a table on the Access Query Editor grid then add some fields to the grid. You can use alternate names for the fields (known as aliasing) by providing the new name for the field where the existing name shows on the grid, like this:
Expand|Select|Wrap|Line Numbers
  1. Preferred Name: Old Name
In the underlying SQL itself aliasing is done using the As operator:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Old name] AS [Preferred Name], [Another hard to read one] AS [Easily Read Name], ... , 
  2. FROM [Your table]
  3. WHERE somecondition holds
  4. ORDER BY some field
As for replacing field names automatically - wouldn't recommend that you interfere with table definitions, although it is technically possible. Use an aliased query instead.

There are other approaches, such as using mapping tables to map one field to another, but these would require a lot of thought about what you want to do and how you want to do it.

I think you can achieve a lot simply by renaming the fields in suitable queries that you can set up for specific purposes.

-Stewart
Jul 19 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.