Create Field Names in Table that are ever changing (1 Viewer)

[chip0105]

I have a table that contains data that is updated annually and keeps previous year data as well to reference - back 5 years.

So, to keep data in proper perspective, I created fields (as Current Year, Year-1, Year-2, Year-3, etc.) to store and reference data values.

I created a query to find and display the correct year information regardless of the current year. I then reference the Current Year query to show the correct year(s) to use.

However, my customer actually wants to see year values in the queries I am displaying. I know I can create a form with fields labeled with correct year values. HOWEVER, a form can only be 21 inches wide and the amount of data I need to display exceeds that length by several inches and I am trying to provide a product on a single page instead of breaking it up into several pages; if possible.

Is there a way I can programatically rename query fields from "Curr Yr Data" to "2015 Data" or "Cur Yr vs Year-1 Data" to "2015 vs 2014 Data"?

I researched for days and cannot come up with a process to do this progromatically. I don't want to manually make changes in query or table names every year. :banghead:

I thought I might be able to use the VBA ALTER TABLE function but cannot seen to add a concatenation function to the string for a new name also: "

CODE
Dim dbs As DAO.Database

Dim tdf As DAO.TableDef

Dim fld As DAO.Field

Set dbs = CurrentDb

Set tdf = dbs.TableDefs("tmptbl_NewQueryDump")

Set fld = tdf.Fields("Unplanned Turnover (Current - Year-1)")

fld.Name = "Unplanned Turnover (" & [qrySelect_MarketYear].[Market Year] & " - " & [qrySelect_MarketYear-1].[MarketYear] & ")"

dbs.Close

Set dbs = Nothing

Set fld = Nothing

Set tdf = Nothing

MsgBox "Changed"

#CODE

I keep getting a "cannot find field name" error message. It finds the original field name but cannot resolve the new field name to change the table field name as coded. Simple field name changes work but concatenated changes do not.

Any suggestions would be GREATLY appreciated.


Thanks in advance for your time and assistance.

 

[JHB]

I have a table that contains data that is updated annually and keeps previous year data as well to reference - back 5 years.

So, to keep data in proper perspective, I created fields (as Current Year, Year-1, Year-2, Year-3, etc.) to store and reference data values.
..

From a database perspective, it is wrong to store data horizontally, as it is done in Excel.
Data in a database is stored vertically.
If you wish to see the data horizontally, then use a crosstab query.

 

[Uncle Gizmo]

As JHB says your structure sounds wrong... See this thread for a possible solution:- http://www.access-programmers.co.uk/forums/showthread.php?p=671226

 

[Uncle Gizmo]

Alternatively you could look at creating aliases for your field names in your query.

 

[arnelgp]

i see you are using two querys there:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb

dim qd1 as dao.querydef
dim qd2 as dao.querydef
dim rs1 as dao.recordset
dim rs2 as dao.recordset
dim var1 as variant
dim var2 as variant

set qd1 = dbs.querydefs("qrySelect_MarketYear")
set qd2 =dbs.querydefs("[qrySelect_MarketYear-1]")
set rs1=qd1.openrecordset()
set rs2= qd2.openrecordset()

var1 = rs1("[Market Year]")
var2 = rs2("[Market Year]")

set rs1=nothing
set rs2=nothing
set qd1=nothing
set qd2=nothing

Set tdf = dbs.TableDefs("tmptbl_NewQueryDump")
tdf.Fields("["Unplanned Turnover (Current - Year-1)]").Name = "Unplanned Turnover (" & var1 & " - " & var2 & ")"

set tdf=nothing
set dbs=nothing

 

[chip0105]

Uncle Gizmo,

I appreciate the prompt response and the supplied coding. However, when I entered everything as coded, I received a coding error in the line:

tdf.Fields("["Unplanned Turnover (Current - Year-1)]").Name = "Unplanned Turnover (" & var1 & " - " & var2 & ")"

Not sure what is causing the error.

My sole purpose for doing this in Access is so the user does not have to reformat everything each and every year within an Excel spreadsheet. So, I created data tables to store yearly reporting values and they simply provide their input files. With a number of queries and a single macro I tie everything together, they can get their desired "spreadsheet" in a matter of seconds instead of 3 or 4 days of manually moving yearly data aropund and then recalculating the fields. This is why I created the current year, year-1, year-2 fields as they are working with 5 years of stored data. My process provides all the information in a matter of seconds. However, they want to see the actual year names in the fields, so I thought I would dump the data results of my select query, to a new table and then rename the year fields accordingly - based on whatever the current reporting year happens to be.

With all that said, it would be most appreciated if you (or someone) can assist with the error I get with the coded line you provided:

tdf.Fields("["Unplanned Turnover (Current - Year-1)]").Name = "Unplanned Turnover (" & var1 & " - " & var2 & ")"

 

[chip0105]

I appreciate everyone's time and effort in providing a result for me. However, I found another source for an answer and will post it for reference:

[FONT=Calibri][SIZE=3]Private Sub cmdUpdateTableNames_Click()[/SIZE][/FONT]
 
[SIZE=3][FONT=Calibri]DoCmd.SetWarnings (WarningsOff)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DoCmd.OpenQuery "qryMakeTable_CreateNewQueryTableAndUpdateWithYears"[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Dim dbs As DAO.Database[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim tDef As DAO.TableDef[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim fDef As DAO.Field[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Dim qd1 As DAO.QueryDef[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim qd2 As DAO.QueryDef[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim rs1 As DAO.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim rs2 As DAO.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim var1 As Variant[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim var2 As Variant[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Set dbs = CurrentDb()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set tDef = dbs.TableDefs("tmptbl_NewQueryDump")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set fDef = tDef.Fields("Unplanned Turnover (Actual - Prev Yr)")[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Set qd1 = dbs.QueryDefs("[qrySelect_MarketYear]")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set qd2 = dbs.QueryDefs("[qrySelect_MarketYear-1]")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rs1 = qd1.OpenRecordset()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rs2 = qd2.OpenRecordset()[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]var1 = rs1("[Market Year]")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]var2 = rs2("[Market Year]")[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]fDef.Name = "Unplanned Turnover (" & var1 & " - " & var2 & ")"[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Set fDef = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set tDef = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set dbs = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rs1 = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rs2 = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set qd1 = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set qd2 = Nothing[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]DoCmd.SetWarnings (WarningsOn)[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]MsgBox "Changed"[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri][FONT=Calibri]End Sub[/FONT][/FONT][/SIZE]
 
[#CODE]

 

[gemma-the-husky]

tables really should not be ever-changing.

if you are changing tables to deal with annual or monthly data, then your design is wrong. Tables generally need to change to reflect new system analysis. ie - you now need to manage some data that you did not previously.