Renaming Fields in SQL

[techgirl10]

I have a query that I run monthly that contains a rolling 18 months of data. Therefore, the oldest month will drop off and a new month will be added so the months and years will change. I want to rename the fields a generic name such as Month1, Month2, etc. Can I do this in SQL?

This is my SQL text:

SELECT Rate2006.Rating, Rate2006.[07/2006], Rate2006.[08/2006], Rate2006.[09/2006], Rate2006.[10/2006], Rate2006.[11/2006], Rate2006.[12/2006], Rate2007.[01/2007], Rate2007.[02/2007], Rate2007.[03/2007], Rate2007.[04/2007], Rate2007.[05/2007], Rate2007.[06/2007], Rate2007.[07/2007], Rate2007.[08/2007], Rate2007.[09/2007], Rate2007.[10/2007], Rate2007.[11/2007], Rate2007.[12/2007] INTO tblMakeCaseOverallRate
FROM Rate2006 INNER JOIN Rate2007 ON Rate2006.Rating = Rate2007.Rating
ORDER BY Rate2006.Rating;

Any ideas would be greatly appreciated. Thanks!

 

[Remou]

Is using SQL to rename a requirement? It is easy enough to do this with DAO:

Set db = CurrentDb
Set fld = db.TableDefs("Table1").Fields("Field1")
fld.Name = "NewName"

If you must use SQL, it might be easier to simply append to an existing table that is already set up with generic names.

 

[genomon]

Have you tried aliases? Rate2006.[07/2006] AS Month1, etc..?

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[dhookom]

Why create an un-normalized table like this when you should be able to keep normalized data and display it with a crosstab query? It looks like you are storing data in both table and field names. This is generally consider poor practice. Your situation may justify this.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[techgirl10]

I tried aliases and appending but as I roll to a different month and drop off a month, the data remains the same for the previous month/year selection.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886