Updating Field Names in Tables using data definition query?

[jaceyk]

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

[Stewart Ross]

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:

Preferred Name: Old Name

In the underlying SQL itself aliasing is done using the As operator:

SELECT [Old name] AS [Preferred Name], [Another hard to read one] AS [Easily Read Name], ... , 
FROM [Your table]
WHERE somecondition holds
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