Aliases or synonyms fields

Hi

I'm using Access in XP Office Pro w SP 3 on Xp Pro w SP3.

On other db's I used I've been able to create alias or synonym fields for a
given data field. I know I can do this is a query, but can I create an alias
field on the table definition?

For example, in my query I have FullName: LastName & ", " & FirstName. I
would like to put FullName in my table definition so that I don't have to
repeat the definition each time I want to use it. Can this be done in Access?

--
Dennis

On Sun, 15 Mar 2009 18:16:01 -0700, Dennis
wrote:

For example, in my query I have FullName: LastName & ", " & FirstName. I
would like to put FullName in my table definition so that I don't have to
repeat the definition each time I want to use it. Can this be done in Access?

It cannot... and should not.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.
--

John W. Vinson [MVP]

John,

I think you misunderstood. In other db, the aliases is just like a
calculated field defintion just as it is in Query. Except instead of storing
your calculated field definition in a query, you store the definition in the
data base definision. That way the same definition is automatically
available to all queries. There is no overhead until you reference the
calculated field in a query.

Given the above, your statement " it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption" is incorrect. Just as the query
calculated field does not waste disk space nor does it risk data corruptions.
It might waste time, but then that is the price of a calculated field.

So, I guess I don't understand your response. But I now understand that
this capability is not available in Access.

Thank you for your time. I appreciate the clarification.

--
Dennis

Dennis,

That capability IS available via. many Access tools, the main one being
called a query. Whether it is to concentate the data for viewing at the time
of viewing (good idea) or to load it into such a field in a table (usually a
bad idea) or to serve as a master data source for other uses or queries

This is a matter of naming conventions in Access, which (in this case)
follow accepted DB practices. If it stores data, it's called a table, if it
maniupulates data (even just for viewing) it's called a query or something
else other than a table.









In this area, Access follows the fundamentals of DB design where tables are
a repository for data and other objects (such as queries) are the
manipulators of data. So asking for a "capability" to do that in a table is
like asking for the "capability" steering a car using the gas tank.

If (as as I don't think you are) you mean simply giving existing fields a
different "label" that shows in the views, you can also do that using the
"Caption" property in the fields.




"Dennis" wrote:

John,

I think you misunderstood. In other db, the aliases is just like a
calculated field defintion just as it is in Query. Except instead of storing
your calculated field definition in a query, you store the definition in the
data base definision. That way the same definition is automatically
available to all queries. There is no overhead until you reference the
calculated field in a query.

Given the above, your statement " it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption" is incorrect. Just as the query
calculated field does not waste disk space nor does it risk data corruptions.
It might waste time, but then that is the price of a calculated field.

So, I guess I don't understand your response. But I now understand that
this capability is not available in Access.

Thank you for your time. I appreciate the clarification.

--
Dennis

Dennis,

To add to Joh's already good (and correct) advice. I would create a query
whose sole purpose it to pull out FullName and maybe Initials and then use
that in any query that you want the FullName. (Of course, I am assuming
that your tables are set up to store the individuals ID.) This way even if
someone want to be seen as Gina and not Regina once you make the
'correction' in the FirstName field you don't have to then go make make the
'correction' in the FullName field, making twice the work.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Dennis" wrote in message
...
John,

I think you misunderstood. In other db, the aliases is just like a
calculated field defintion just as it is in Query. Except instead of
storing
your calculated field definition in a query, you store the definition in
the
data base definision. That way the same definition is automatically
available to all queries. There is no overhead until you reference the
calculated field in a query.

Given the above, your statement " it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption" is incorrect. Just as the query
calculated field does not waste disk space nor does it risk data
corruptions.
It might waste time, but then that is the price of a calculated field.

So, I guess I don't understand your response. But I now understand that
this capability is not available in Access.

Thank you for your time. I appreciate the clarification.

--
Dennis

Gina,

Please let me restate my questions. Can I create a query field definition
once and reference (vs copy) that defintion in another query. Or do I have
to copy it / re-enter it in the other queries?

My goal here is to create and debug a query defintion once and re-use that
debugged definition multiple times.

FullName is just one example. CityStZip, FirstLast, LastFirst, and foreign
key look up names, and calculated fields are some other examples I can think
of right of the top of my head.


--
Dennis

Dennis,

If I understand you correctly the answer is yes. If I understand you
correcly, you just want to create A query and reuse the same said query.

The only objection you are getting is because you stated you wanted to store
the results, ie: FullName in a table. That is what is frowned upon for all
of the prior mentioned reasons. I frequently use a query for Associate
Names, City, State and Zip, Area Codes, to name a few... Butt I don't store
the FullName in a field in the table. For me that would be a problem
because users have a choice of FirstName LastName - Nickname LastName -
Company Name - DBA - ShortName (for lookups only). They can change any one
of the fields because of typos or change of Company Name or getting married
and I would have to be updating two to three fields.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Dennis" wrote in message
...
Gina,

Please let me restate my questions. Can I create a query field
definition
once and reference (vs copy) that defintion in another query. Or do I
have
to copy it / re-enter it in the other queries?

My goal here is to create and debug a query defintion once and re-use that
debugged definition multiple times.

FullName is just one example. CityStZip, FirstLast, LastFirst, and
foreign
key look up names, and calculated fields are some other examples I can
think
of right of the top of my head.


--
Dennis

On Mon, 16 Mar 2009 07:31:02 -0700, Dennis
wrote:

Please let me restate my questions. Can I create a query field definition
once and reference (vs copy) that defintion in another query.

Yes. A Query can be based on another Query.

--

John W. Vinson [MVP]

All,

All, I appreceiate your patience with me. Like I said, I've been using
relational db for over 20 years on large to mid-range machines and I am just
learning Access. So I know what I want to do, I just don't know how to do it
in Access. I greatly appreciate all of the input.


John,

thanks you very much. I see how you can do that now. How big a
performance hit till I take if I base a query on another query. I'm looking
at this from not only an Access back end, but what would happen if I upgraded
my db engine.

My plan is to define a general query will all of my actual fields along with
all of my defined fields. This query would have no sort or selection (where)
criteria. Then all subsequent queries would be based on my general query and
those queries would have the sort and select criteria. Is this a bad
approach in Access? Either from a programming approach or a db engine
efficiency standpoint. I'm trying to learn the best and most efficient ways
to utilize Access while still producing easily maintainable and re-useable
code.

--
Dennis


"

On Mon, 16 Mar 2009 10:20:03 -0700, Dennis wrote:

All,

All, I appreceiate your patience with me. Like I said, I've been using
relational db for over 20 years on large to mid-range machines and I am just
learning Access. So I know what I want to do, I just don't know how to do it
in Access. I greatly appreciate all of the input.


John,

thanks you very much. I see how you can do that now. How big a
performance hit till I take if I base a query on another query. I'm looking
at this from not only an Access back end, but what would happen if I upgraded
my db engine.

My plan is to define a general query will all of my actual fields along with
all of my defined fields. This query would have no sort or selection (where)
criteria. Then all subsequent queries would be based on my general query and
those queries would have the sort and select criteria. Is this a bad
approach in Access? Either from a programming approach or a db engine
efficiency standpoint. I'm trying to learn the best and most efficient ways
to utilize Access while still producing easily maintainable and re-useable
code.

You can consider a saved Access query without parameters to be a view. All your computed fields are
in the view. All your forms and reports can use the view as their record source.

You can query the view as if it were a real table. If you use SQL Server as your data store,
Access can link to the view and the view will appear in Access as a table. If the view is
updateable in SQL Server, it will usually be so in Access, provided you tell Access what the key is.

I have an Access program that links to 50+ SQL Server views. My program links to no tables
directly.

I do not think there is any good reason to create a saved query that has no columns which are not in
the table. Saved queries are usually for joining tables and making computed columns. If there is
no join in the query, and no computed column, then why have the query? Maybe someone else knows of
a reason.