dynamic field names (sort of)

[mountainclimber]

tbl_FldNames:

pid_FldNames	DisplayName
1	a
2	x
3	r

tbl_Values:

1	2	3
100	a	car
3	b	truck
5	z	car

Is the above the best way to keep field names "dynamic" or "changeable"? I.e. keep the field names static and create a separate table where they can be changed for display? If so how would you create a query that resulted in something like this:

a	x	r
100	a	car
3	b	truck
5	z	car

[Micron]

Umm, what you're asking to do is bizzare. Dynamic table field names violate everything about database integrity. This does not mean you cannot assign an alias to a query field name to show what you want, but to do so with tables makes no sense to me. Only if they were to be temporary tables (i.e. deleted after each use) and there was no other way to do this with a query with aliased fields.

[ranman256]

you should not be doing it that way.
you should always have constant field names. If this is a pivot table, then you must use the raw data sheet ,not the pivot.

[mountainclimber]

Umm, what you're asking to do is bizzare. Dynamic table field names violate everything about database integrity. This does not mean you cannot assign an alias to a query field name to show what you want, but to do so with tables makes no sense to me. Only if they were to be temporary tables (i.e. deleted after each use) and there was no other way to do this with a query with aliased fields.

Well, of course the field names aren't really dynamic in my example. Yes, I was thinking of a temp table that would be deleted. Still how would you do it? How would aliases work if the query where the aliases live would need to be changed each time a name was changed?

[mountainclimber]

you should not be doing it that way.
you should always have constant field names. If this is a pivot table, then you must use the raw data sheet ,not the pivot.

The field names would be constant and in fact they are in my example. The 3rd table is just output, not a permanent table in my database. No pivot tables here.

[June7]

Can you tell us why you need to dynamically change displayed name? What criteria determines what the display name should be? How many different display names could there be? Why would this need to be done in query? Users should not work directly with tables and queries. This dynamic display could probably be done on form and report.

[mountainclimber]

Can you tell us why you need to dynamically change displayed name?

Because names that the user wants to see changes over time. Just user preference I guess. The tables I gave are hypothetical but the situation is not hypothetical. It really happens to our current system which I manage. I am converting a system that lives and runs in Excel exclusively to live and run in Access exclusively (as possible).

What criteria determines what the display name should be?

Just user preference. They change the names so they can understand them better. I am in investment finance and the proprietary calculations that we create have a lot of inputs so the names, if fully descriptive, can get quite unwieldy if they are not abbreviated. These abbreviated names get changed until they are optimized (most intuitive to the user). We have descriptions, but the calculated values are used in dashboards (among other things) for quick absorption. In other words, the user doesn't have time to read each long name or description etc.

How many different display names could there be?

There is a long name and a short name. The long name has a rigid naming convention that serves a good purpose for us that would be pretty detailed to explain and beyond my question. The short name is more of dashboard or presentation name for external use (aka presentations to clients that are simple etc.), but both are viewed by the user (internal employees) on a regular basis.

Why would this need to be done in query?

It does not. I guess... I just thought there was a query and/or table behind each report.

Users should not work directly with tables and queries. This dynamic display could probably be done on form and report.

They are not working with the tables directly. Only I do that. They would be working with a report or form. Sorry, I figured that was beyond mentioning. If you have seen my posts on your forum, I am obviously learning Access as I go. I have reviewed reports in Access online a bit to get an idea, but I have not used them yet. I have used forms for 15 years in Excel.

[June7]

So define dynamic in this context. Say you have 10 users - does each user want to see a different display name for the same field? Or by dynamic do you mean just modify the display name all users see? The latter is simply a form/report design edit done when needed. How often would that be?

[mountainclimber]

So define dynamic in this context. Say you have 10 users - does each user want to see a different display name for the same field? Or by dynamic do you mean just modify the display name all users see? The latter is simply a form/report design edit done when needed. How often would that be?

I get what you are driving at now. Lets say it should handle up to 10 users or so. They all agree on the names and the changes they make so they can talk to each other (and me) and we all know what we are talking about. I mean just modify the display name so all can see the same thing, but be able to change it when one wants to. It would be best if the newly changed names were viewable immediately to everyone after changes were made or perhaps more preferably if the other users were notified that there was a change made to a name they are currently using.

Off topic: Are you C. Pearson, or is signature just marketing for him? Either way, I am a huge fan. I go to his site when 15 years of Excel experience is not enough.

[June7]

I am not C. Pearson. Just like the article.

Is the db split design? Multi-user db should be split with each user running their own copy of frontend on their local computer. Design edits would not be available until new version is distributed.

[mountainclimber]

I am not C. Pearson. Just like the article.

Is the db split design? Multi-user db should be split with each user running their own copy of frontend on their local computer. Design edits would not be available until new version is distributed.

That is all on the todo list. Sounds like I am on the right track. Sounds like I do it all in the report/form. Thanks! I won't worry about it right now then.

[Micron]

So here is what I would consider doing if there aren't too many controls whose names you would want to change, and you're willing to do the up front work assuming the need is great enough for you. First, as mentioned in this thread, interaction between the user and database should be with forms and reports, not queries and tables. So with that in mind, you could create a table to hold each control label caption and read it on form opening. When you change the value in the table, your label will assume the new name. One advantage would be that when you effect a change, you would not have to deploy a new front end each time. However, if the changes would be infrequent, you might as well just agree on the changes, make them in design view, then redeploy.

If you go the table route, you will probably need fields for form name (to easier identify which form contains it), label name and label caption (the text you see) and maybe a notes field. You would retrieve them in code during form's open event, likely using a function that, for each control, if type=acLabel then lookup the caption in the table where the table label name = the form label name. If each label does not have a table entry, you will have to trap this (and probably other) errors.

[mountainclimber]

So here is what I would consider doing if there aren't too many controls whose names you would want to change, and you're willing to do the up front work assuming the need is great enough for you. First, as mentioned in this thread, interaction between the user and database should be with forms and reports, not queries and tables. So with that in mind, you could create a table to hold each control label caption and read it on form opening. When you change the value in the table, your label will assume the new name. One advantage would be that when you effect a change, you would not have to deploy a new front end each time. However, if the changes would be infrequent, you might as well just agree on the changes, make them in design view, then redeploy.

If you go the table route, you will probably need fields for form name (to easier identify which form contains it), label name and label caption (the text you see) and maybe a notes field. You would retrieve them in code during form's open event, likely using a function that, for each control, if type=acLabel then lookup the caption in the table where the table label name = the form label name. If each label does not have a table entry, you will have to trap this (and probably other) errors.

That is more or less how all of my Excel forms work and what I planned on doing for this one (in Access). Thank you for confirming.