>>
I will have to
create a new column. For example: when 2007 comes I will
have to create a 2007 column.
<<
The statement above suggests incorrect usage of an RDBMS - relational
database system. Even though Access is not as sophisticated as Sql
Server - it is still a relational database system, and Jet Sql operates
under the same rules as Transaction Sql (sql server Tsql). That means
that adding a new column to a query that is already in production would
be introducting redundancy into your system. That means that you are
denormalizing your database - which means that you will have the
problems that you are posting about in the first place.
The solution to your original post then, is to use a form because in a
form you can manipulate the caption in a form. But a problem that I see
from this newsgroup is that a lot of people who use Access but are not
looking to become professional programmers are lulled into thinking that
Access can do anything. And this crowd is not aware of the concept of
database normalization - which means no redundant columns - a confusing
and complex topic - which requires programming.
Well, for a desktop database system, Access is a very powerful system
and can do a lot of things that most desktop applications cannot do.
The caveat is that you have to write programming code (VBA) to make
Access do these things. So instead of adding a new column to your query
keep the query you have and programmatically pull the data that you need
by using parameters. If you need to pull data for 2006 for now, you
pass in a parameter for the year 2006. Then when you need to get data
for 2007, you can use the same parameter to retrieve data for 2007.
If you are not familiar with using parameters in queries
programatically, you can ask about it in this newsgroup.
Rich
*** Sent via Developersdex
http://www.developersdex.com ***