Create Field Names based on values in another field

  • Thread starter Thread starter Dane Cooper
  • Start date Start date
D

Dane Cooper

I have a table with many fields, some of which are years as field names.
One of the field names is "Base Year". I want the some of the field names
to change based on the value in "Base Year". For example: If "Base Year"
is 2005, I want Field1 to be named "2005", Field2 to be named "2006", etc.

Any help would be appreciated.

Dane
 
That is not proper database design. A field name should not contain data.
and you certainly would not have different field names for every record.

Without knowing more about your data, you should most likely have a second
table where one record per year is created.
 
Dane, you do *not* want these fields.

Whenever you see fields repeating like 2005, 2006, and so on, it *always*
means that you need a related table where these values can be stored as
records, not as fields in one monster table.

The related table would have fields such as:
ID Relates to the primary key of your
existing table.
WotYear Number (holds the value 2005 or whatever)
WotValue Whatever you are trying to store.
Now instead of storing the value in a column named 2005, you will store the
year in the 2nd field, and the value in the 3rd. For 2006, you will create
another record, with 2006 in the WotYear column, and the value in the
WotValue column.

Use field names that suit you, but that is how relational databases are
designed.
 
I have a table with many fields, some of which are years as field names.
One of the field names is "Base Year". I want the some of the field names
to change based on the value in "Base Year". For example: If "Base Year"
is 2005, I want Field1 to be named "2005", Field2 to be named "2006", etc.

Any help would be appreciated.

Dane
Click to expand...

Rick and Allen are absolutely correct with regard to table design: any
table with fields named 2005 and 2006 is ipso facto INCORRECTLY
DESIGNED.

If you want to take data from a normalized table and *display* it with
one column for each year, you can use a Crosstab query using the year
as a Column Header... but storing this in a table is neither necessary
nor prudent.

John W. Vinson[MVP]
 
Allen:

Actually, I have seen a Microsoft Access database application do exactly
what I have described. When I opened it up to find out how it was done, I
saw that the author had used a lot of VBA language to label fields in a
subform with the proper year, yet the subform was in datasheet format. The
underlying table still had its original field names (e.g., Yr1, Yr2, etc.).
So I know it can be done AND in such a manner as to keep the principles of a
relational database in tact. I just don't know how.

Dane
 
John:

Actually, I have seen a Microsoft Access database application do exactly
what I have described. When I opened it up to find out how it was done, I
saw that the author had used a lot of VBA language to label fields in a
subform with the proper year, yet the subform was in datasheet format. The
underlying table still had its original field names (e.g., Yr1, Yr2, etc.).
So I know it can be done AND in such a manner as to keep the principles of a
relational database in tact. I just don't know how.

Dane
 
The fact that is can be done does not mean it should be done.

I am not surprised there was a lot of code to try to make up for a bad data
design. We very often see people wasting inordinate amounts of time writing
unnecessary code, trying to make up for the fact that they did it wrong in
the first place.

(That's assuming that the example you looked at was stored as you describe,
rather than merely interfaced as you describe.)
 
John:

Actually, I have seen a Microsoft Access database application do exactly
what I have described. When I opened it up to find out how it was done, I
saw that the author had used a lot of VBA language to label fields in a
subform with the proper year, yet the subform was in datasheet format. The
underlying table still had its original field names (e.g., Yr1, Yr2, etc.).
So I know it can be done AND in such a manner as to keep the principles of a
relational database in tact. I just don't know how.
Click to expand...

I guess the question is: *what do you want to accomplish*?

If you want to store the data in a denormalized table, that's a Bad
Idea - but sometimes you have to do bad things like this just as a
temporary expedient.

If you want to produce a Report or a Form with captions for the year,
you can do so (with some labor) without munging up your table
structure.

Could you explain the real-life need? What do you want to see?
And what data are you starting with?

John W. Vinson[MVP]
 
Back
Top