Aliases or synonyms fields

[Dennis]

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?

 

[John W. Vinson]

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.

 

[Dennis]

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.

 

[Fred]

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.

 

[Gina Whipp]

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]

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.

 

[Gina Whipp]

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

 

[John W. Vinson]

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.

 

[Dennis]

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.

 

[Michael Gramelspacher]

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.

 

[David W. Fenton]

I've been using
relational db for over 20 years on large to mid-range machines and
I am just learning Access.

Whatever db you were using seems to have taught you non-standard
terminology. Access is closer to the standard than the terms in
which you worded your original post.

 

[Fred]

Dennis,

Long story short, what you now describe sounds fine.

Although the given reason (getting the expression reliably repeated in the
multiple places that need it) seems like something that could be more easily
handled via other means, such as copy and-paste the expression.

I have a few application where I base queries on a query. The typical use
is when I have a particular condition / criteria where many queries would
have that as one of their conditions.

Example: A membership database which lists both current and past members.
And about 10 queries which search for a subset of current members. Rather
then put "current" as a condition into all 10 queries, I make one query which
selects for current members, and use it as the record source for the 10
queries.

 

[Keith Wilby]

All,

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?

I've never had a problem with such an apporach.

Keith.
www.keithwilby.co.uk

 

[Dennis]

All,

I appreciate all of the comments and the helps. As I said, I'm still
learning the Access terminology so I apologize if I did not use the correct
terms.

But yes, what I'm trying to accomplish is to create a logical view of the
table with all of my computed fields in that view.

It is true that I could copy and paste my computed field into other queries,
but it I want to change one of those computed field, I would have to change
all of the places to where I copied it. If I leave it in one place and have
everything reference it, I only have to make one change.

Once again, thanks for all of the advice and assitance. It is greatly
appreciated.

Dennis