cross reference query

[maffonso]

Hi guys,
I have built a cross reference query (columns is year).
I would like to change the caption and adjust others things.
The best way would be to wrap the query in a form, but soon
2007 will begin And I will have to maintain the form.
I worry mostly about the caption of the cross query.
How can I change the caption of a cross query without use a form.

Thank you all

Maffonso

[Rich P]

Hi Maffonso,

To edit the query field caption (I hope that is what you are asking
about) is type something like this for each field that you wnat a custom
caption for - in the "Field" box in query design - say your table has
fields named fld1, fld2, fld3

in the query design you do this to change the caption for each field

customCaption1: fld1 customCaption2: fld2 ustomeCaption3: fld3

you are modifying the text in the field box by adding your customCaption
followed by a semicolon : followed by the field name.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[maffonso]

Hi Rich,
I mean caption that caption in the blue bar at the top of the window.
By default the caption in the blue bar is the name of the
query. For example: qryMyQuery:cros s reference.
My probem is that if I use a form, each new year I will have to
create a new column. For example: when 2007 comes I will
have to create a 2007 column.

Thank you
Maffonso

[Rich P]

>>
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 programmaticall y 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 ***