Renaming field names of Linked Table in query

[deeds]

I have a linked table from our system...it's field names are "codes" (hard to
understand). So, I created another table with those code names and a field
with the corresponding clean name. Now, how can I create a query that
somehow shows those "clean" field names for the linked table? Thanks in
advance...

 

[deeds]

Here is more of an example of my need: In a query design I bring in a field
from a linked table fieldname: GLACT...I want that to show GL Account. I
have a table that has both GLACT and GL Account titles available
(dictionary). So, in the query I could manually rename GLACT by typing in GL
Account: GLACT in the FIELD row. It would be slick if I could replace the
manual process with a lookup of sorts. So in the query it would look
like.... Lookup(GLACT, 2, False): GLACT Sorry for the crude example but if I
could use a lookup to rename the GLACT name would be great. Any ideas?
Thanks

 

[Jeff Boyce]

I might not be getting the full picture...

You can highlight any of your linked tables and rename them.

But why do you care what the tablenames are? You really don't want to be
giving the users direct access to the tables themselves, so it probably
shouldn't matter if the tablenames, as linked, are somewhat cryptic.

Access forms (and reports) are used to display data. And if your Access
database is reasonably-well-normalized, you'd use your forms and reports to
insulate the users from the complexities of the relational table structure.

You've asked "how" ... I'm wondering "why"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[deeds]

Thanks Jeff...sorry for the confusion... Let's say you have a linked table
from a "system". This table has cryptic names for it's fields. I want to
essentially duplicate that table...but replace the cryptic field names with
clear names. Now, I do have a dictionary that has the clear name for each
cryptic field name. I would somehow like to automate a tool to run a query,
bring back the entire table but have the clear field names. Does that help?
Thanks again

 

[Jeff Boyce]

So, it isn't the table name that you are trying to clarify, it is the field
names?

This works in Access 2007, but "your mileage may vary" if you are using an
earlier version:

Open the Access database to the Database window (or Navigation Pane).

Right-click on the linked tablename and select "Design View" ... you'll be
warned that you can't mess with linked table definitions. Ignore the
warning and click <Yes>.

Highlight the field you wish to change the name of. The lower portion of
the window contains properties for that field.

Find the "Caption" property and change the cryptic name of the field to
something friendlier.

When you've done that to all the fields you care to change, close the
window. You'll be prompted to save your changes. Answer "Yes".

If you open the table now, the "alias" you gave the field will be displayed.

Now, again, ?!why?! Your users should never be exposed to the raw
tables/fields. Your forms can display whatever labels you want.

Why does it matter what fieldnames are showing if you already know the
'translations'?

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[deeds]

Thanks Jeff...that works! The reason is soley for my use. Each of the
linked tables has 40-50 cryptic names...when i write queries I don't want to
refer to the friendly names on another table...I want them displayed so that
I can see what I am doing. I wish there were some way to cross reference
this friendly name table and have those names appear when I run a query. It
will take me a long time to go in and rename each cryptic field name. Any
ideas of using the cross reference table I already have? Thanks again!

 

[deeds]

Sorry..one more thing...I just tried the naming the "caption" property. That
will display the new name...however, when I go to create a query and bring
that table in I can't see the friendly name in that table...I still see the
cryptic name. Anyways, I wish there were some way to use my cross reference
table to use the friendly name in queries etc...

 

[Jeff Boyce]

While I'm not familiar with using that kind of data
dictionary/cross-reference, I bet you can find folks who've done something
similar.

Try re-posting in the .modulesdaovba newsgroup...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Amoroso]

Have you considered using a make table query? You can take the field names and change them in the query. In the query you set the name you want to display and the table and field you are taking it from New name:

![field] You can also right click and use the build function