Change a field name in a query

[W. Faidley]

Can the field name in a query be setup as a variable such
that in the query datasheet view, the resulting field
name could change based on input to the query?
This would be like having the Caption for the field name
be a variable.
Is there any established code function that would
accomplish this?
Thanks in advance.

 

[Graham Mandeno]

Not without editing the query's SQL statement. But why would you want to do
this? A query is for assembling data. You should use a form or a report
for displaying the data, and it is easy to do what you require in either of
those.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Jeff Boyce]

To reinforce Graham's comment, I usually suspect that someone's committed
spreadsheet on Access when they have a table that uses fieldnames as a
variable. Good relational database design and normalization avoids
"repeating" fields, instead designing a "narrow but deep" table structure.

Perhaps if you provided a bit more info about what the fieldname are, and
what kind of data the fields contain, the 'group readers could offer better
suggestions.

Good luck

Jeff Boyce
<Access MVP>

 

[W. Faidley]

Thanks for your reply!
More info, I have a situation where there are multible
databases where the table designs are the same. The table
field names are the same in each database and have
generic names such as F1, F2, etc. In each database there
is a reference table that defines the field name for F1,
F2, etc. These names may vary between databases.
I would like to setup a generic guery that I could copy
to each database that would extract the data with the
correct field names in the datasheet view based on the
particular reference table associated with each database.

 

[Jeff Boyce]

I'll expand on my earlier supposition. If you have "multiple databases
where the table designs are the same", I wonder about what kind of data is
being stored, both within tables, and between databases. There may be an
opportunity for further normalization, which, if so, could help in your
efforts.

If it isn't proprietary, what kind of data and table and database structure
are you working with? I ask because answers to questions about machine
tolerances may not be appropriate for someone using a financials database
(except in the most general design considerations).

Good luck

Jeff Boyce
<Access MVP>

 

[W Faidley]

Thanks again for your help. The databases have similar
tables with primarily text fields. However, the field
name and associated contents may very between databases
as follows:

Generic
Field name db1_field name db2_fieldname db3_ field name
F1 Doc Type Name Description
F3 Name Doc Type Description
F3 Name Description Doc Type

For each database there is a reference table that defines
the particular field name for its data table
I was hoping that, through code, the caption for each
table could be defined based on the reference table
field names in each database.

Hope this helps.

 

[Jeff Boyce]

I've been out of town -- sorry for the delay.

I'm still having difficulty understanding both what your data is, and what
you want to do. Graham's original response holds your key -- to make a
query "flexible", you need to be working with the SQL statement that
represents the query, not the design grid representation of the query.

I am still unclear about the data you are storing in multiple similar tables
and fields.

Good luck

Jeff Boyce
<Access MVP>