Very basic question

[Dick]

I have created a table in Acces 2007. I would like to add a field that
concatenates two other fields which I will use to link to another table. I
have read through several help windows and tutorials and have a fiarly good
conceptual understanding. I just can't figure out how to do it! After I add a
new field to my table, where do I go to enter the formulas and how do I get
the calculated results into the records?

 

[Rick Brandt]

I have created a table in Acces 2007. I would like to add a field that
concatenates two other fields which I will use to link to another
table. I have read through several help windows and tutorials and
have a fiarly good conceptual understanding. I just can't figure out
how to do it! After I add a new field to my table, where do I go to
enter the formulas and how do I get the calculated results into the
records?

You either read the wrong materials or you did not gain a good conceptual
understanding from them.

Calculations cannot be done at the table level and the result of
calculations done elsewhere should not be stored in tables. This is a
relational database 101 rule. You can (and should) do both of these in a
SELECT query and then just use the SELECT query in place of your table.

However; if you are responsible for the design of both tables why is the
join data in two fields in one of them and in one field in the other? The
most correct solution is to split it out into two fields in the second table
as it is clearly two pieces of data and logically should be stored in two
separate fields.

If you are not responsible for the design of the second table and are stuck
with that design then you can either use the SELECT query to create the
concatenated field for doing the join or you can join on an expression in
your queries. It is not well-known, but joins don't have to be based on the
"=" operator nor do they have to be only against raw fields. They can also
be done on expressions. Both of these though require that you use the SQL
view of the query designer rather than the graphical designer.

 

[Dick]

I said the question was very basic. More basic than you might think. You
suggest I "just use the SELECT query in place of the table..." Huh? Where do
I go to create the SELECT query? How do the results get into a table that
contain the desired data? I will worry about proper table design after I get
these basic mechanisms figured out.

 

[Rick Brandt]

I said the question was very basic. More basic than you might think.
You suggest I "just use the SELECT query in place of the table..."
Huh? Where do I go to create the SELECT query?

Queries tab, press "New". Really, if you don't know yet how to make a query
then you should stop now and spend a few hours in the help file. Access is
not Word or Excel. You can't just open it up and start using it by trial
and error.

How do the results
get into a table that contain the desired data?

That was my point. They DON'T go into a table at all. Data that can be
derived from fields in the table should be derived on-the-fly as needed. It
should not be redundantly stored to disk again.

 

[Dick]

As a matter of fact, I not only can but did just open it up and start using
it by trial and error. Then I went back and spent several hours reading help
files hoping to see the obvious. I do know enough to ask the question.
Perhaps I am at a disadvantage because I am a power user of Excel and
Powerpoint and I understand data analysis in ways that probably transcend
your own. After 61 years, I am still trying to grow and don't feel compelled
to ridicule the ignorance or arrogance of others.

Your last suggestion was helpful but I am not inclined to ask for more.

 

[fredg]

As a matter of fact, I not only can but did just open it up and start using
it by trial and error. Then I went back and spent several hours reading help
files hoping to see the obvious. I do know enough to ask the question.
Perhaps I am at a disadvantage because I am a power user of Excel and
Powerpoint and I understand data analysis in ways that probably transcend
your own. After 61 years, I am still trying to grow and don't feel compelled
to ridicule the ignorance or arrogance of others.

Your last suggestion was helpful but I am not inclined to ask for more.

Atta boy Dick.
You really know so much about Powerpoint and Excel that you feel you
can stumble through Access as though you were sleep walking. You may
be 61 years old, but you are acting like a 'you can't tell me what to
do' 18 year old. I'm 73, and I know that Rick gave you the correct
information. You may not have understood his reply (that's the Excel
spreadsheet mentality talking) but never-the -less it was good
information.

 

[Rick Brandt]

As a matter of fact, I not only can but did just open it up and start
using it by trial and error.

Implied in my "can't" was "and expect to produce anything useful".

Building databases is a skill apart from the knowledge of any particular
tool used to create one. One can know how to use Word, but one first must
know how to create useful documents. One can know how to use Excel, but
must first know something about the calculations and analysis that they will
be asking Exel to perform. If you use Access and know nothing or little
about building databases then your chances of producing anything useful are
small to none.

Then I went back and spent several
hours reading help files hoping to see the obvious.

A good general rule is that if you can't easily find in the help file how to
accomplish a certain task the odds are good that the task is not something
you should be doing in the first place. The lack of quality in many of
Microsoft's help file search engines detracts from this advice somewhat, but
you should at least be suspicious of what you are trying to do if you cannot
find out how to do it.

I do know enough
to ask the question.

Kudos for that. And I was happy to inform you that you are heading down the
wrong track. This is the "getting started" forum and you indicated that
your question was "very basic". Given that, it is a bit suprising that you
are incredulous about the answer you were given.

Perhaps I am at a disadvantage because I am a
power user of Excel and Powerpoint and I understand data analysis in
ways that probably transcend your own.

Exactly. Database design is not about analyzing data. It is about properly
storing it. The latter facilitates the former, but they are distinct
concepts. As one of our most prolific posters in these groups like to say
"Access is not Excel on steroids". What is appropriate to do in a
spreadsheet is almost never correct in a database.

After 61 years, I am still
trying to grow and don't feel compelled to ridicule the ignorance or
arrogance of others.

Nor do I. If you think anything I posted falls into the category of
ridicule then I apologize, but I also must respectfully disagree with that
assessment. Telling someone that asks "how do I make a query" that they
need to spend some time in the Help file is hardly ridicule.

Your last suggestion was helpful but I am not inclined to ask for
more.

Well don't give up on the asking. There are plenty of others who will
gladly respond. If a response similar to mine is enough to turn you off to
each of them though you will have no one left in fairly short order.

 

[Wayne-I-M]

You didn't say which version of access you were using. Rick's advice was
totally correct and you really should not store calculated results
ie.
If you store this
8th June 2008 - my date of birth = my age
But this will be wrong tomorrow.
So you need to re-calculate this each time you use it. You can run a
calculation in access quick than you can retrive some forms of data so it
make sence

Anyway. I have given some of the people in my office this website to look
at - they all know how to use excel but (again as Rick said) access is one of
those MS programmes that you can't just start using. Well you can - but then
after w while you'll need to go back and re-do everything you did wrong.

Have a look at this site a pick the version of access you hav a just spend
some time (more than a few hours I'm afraid) getting the basics.

http://office.microsoft.com/en-us/training/default.aspx

Good luck with your new database

 

[Dick]

Thanks for the tutorial link. I will pursue it.

I am using Access 2007. I understand the general rule that I do not want to
store calculated data. I am not seeking to store redundant calculated data,
although I can understand why people think I do. So let me describe my goal
a little more fully.

I am working with hierarchic data. The first level has a field with a unique
numeric code. The second level has another field unique numeric codes that
duplicates the first. The third level has yet a third field but it is not
unique across level two. A human consumer would recognize that level two is
an implicit category attached to level three. So, if I cconcatenate the codes
for levels two and three in a new field, I will have an identifier that is
unique across all three levels; it will be intuitive; and I will be able to
replace the original three fields with one field.. In other words, my goal
is to make my table structure simpler. If I kept all four fields, I would be
violating well recognized normalization guidelines. But I only want one
field to replace the three that I received. How can I create a new field and
populate it with the concatentated results?

OK, I did not fully describe my situation but I thought I did describe the
part of the task that eluded me. I still do not understand the mechanics of
Access. I probably don't fully understand the virtues of a properly
normalized database, but I understand more than I have been given credit for.
I am willing to accept some lecturing about the things I am assumed to not
know but I hope to get an answer to my question along the way.

If you want to go off on another tangent, lecture me about how to best
capture and represent hierachic data in a relational database. Capturing the
data is the easy part. Getting it back out is another matter. If hierarchic
data is "merely" a decomposition, then numeric data can be aggregated up the
hierarchy through simple queries and subtotals. If the hierarchy is
categorical (this may not be the proper term), then the child inherits
some/all? attributes of the parent. Failure to acknowledge the parent
results in the loss of the inherited attributes. Can this be represented in
an intuitively understandable way? If Access is not an appropriate tool for
this task (I know that Excel is not) then how can this be done?

Thanks again for the link to the tutorials. They should answer my
mechanical problems and they may help me to understand my semantic problems.

 

[John W. Vinson]

I am working with hierarchic data. The first level has a field with a unique
numeric code. The second level has another field unique numeric codes that
duplicates the first. The third level has yet a third field but it is not
unique across level two. A human consumer would recognize that level two is
an implicit category attached to level three. So, if I cconcatenate the codes
for levels two and three in a new field, I will have an identifier that is
unique across all three levels; it will be intuitive; and I will be able to
replace the original three fields with one field.. In other words, my goal
is to make my table structure simpler. If I kept all four fields, I would be
violating well recognized normalization guidelines. But I only want one
field to replace the three that I received. How can I create a new field and
populate it with the concatentated results?

One piece you may be missing is that you can link tables on *multiple fields*.
It is NOT necessary to create the redundant concatenated field in order to
provide a link between your tables; you can create a Relationship, or a
parent/child link to a subform, on up to *ten* fields.

You can concatenate the fields in a query for display purposes, without
storing that concatenated value in any table.

That said... you can use the BeforeUpdate event of a Form to concatenate
values in multiple field controls into one, and store that concatenated value
in a bound textbox. It's a bit complex but not all that hard, but has all the
disadvantages of storing data redundantly, and as I say it is not necessary in
order to accomplish what you intend.

 

[Wayne-I-M]

Hi

I think you may have missunderstood the way this forum works. It is a peer
to peer forum - mind you its not really or no one would ever ask a question
as everyone would have the same amount of skill.

In your posts you seem to not like being lectured.

If you want to go off on another tangent, lecture me about how to best
capture and represent hierachic data in a relational database.

But thats the way it is (and should be). If you ask a question such as how
to I get a person's full name into a table field. The answer will come back
that you should not be storeing both sets data in the same field (1stname and
2ndName) and that you should redesign your table
structure......etc......etc.....etc. If you don't like these sorts of answer
then it's not a good idea to post here. Yes the answer was a lecutre in the
correct method of storeing data (it should be relative to the primary field
and "most" importantly - individual). Lectures are what you ask for when you
ask a question.

Everyone who posts here is on the learning curve somewhere - some high up on
the curve with lots of experience (in access) and other just starting off and
trying to create a simple application. If you ask a question you are looking
to have someone futhure along the curve than you answer. Now, you may not
like the answer or the way it's given, but you'll find that (most of the
time) the answer is valid. Don't get upset if some tells you that what you
have worked on for ages is just wrong. Redo it and smile. You have just
moved futhure along the curve.

Rick gave you a really good answer. The way he gave it was in his style. I
give answers (and ask questions) in my style, so do you. We all do. Thats
just the way life is. If you don't like the "way" an answer is given - don't
worry, accept the advice given or not thats up to you. But if the answer is
given by an MVP then its normally a good idea to accept it, even if it means
you need to go back over something you thought was right and redo it - like
your concencated linking fields - sorry but that's the wrong way to do. OK
it "could and will" work but there are better ways to do it that will not
lead to the problems you'll have if you continue to think along these lines.

Good luck with your application