Using find and replace in different fields

[lostinseganet]

Hello everyone. I am trying to use access to see a word in one field and
write a number in another field. Like if agree =3 , disagree= 1. I want
access to see agree in a field and write 3 in another field. How can this be
done?

 

[Dirk Goldgar]

In

Hello everyone. I am trying to use access to see a word in one field
and write a number in another field. Like if agree =3 , disagree= 1.
I want access to see agree in a field and write 3 in another field.
How can this be done?

Are you talking about doing this for an entire table? Depending on the
circumstances, it sounds to me like you could most easily accomplish
this with an update query. The SQL for such a query would be along the
lines of

UPDATE [YourTableName]
SET [disagree] = 1
WHERE [agree] = 3

If this is a one-time job, write the query, run it, and you're done. If
it's something that has to be done repeatedly in an automated way, then
you can use VBA code to build and execute an SQL statement similar to
the above on the fly.

Note, though, that if you have one field whose value *always* depends on
another field in some deterministic way, then the dependent field should
probably not exist at all. Calculated values should seldom be stored,
but instead should be calculated on the fly.

 

[Steve Schapel]

Lostinseganet,

In most circumstances, this would be regarded as an invalid procedure in
a database. In most circumstances. you would use a calculated field in
a query to return this value, using syntax something like this:
YourResult: IIf([YourField] Like "*disagree*",1,IIf([YourField] Like
"*agree*",3,0))
Or if your requirements are more complex, there are other ways to do it.

Storing the result in a field in your table can be done using an Update
Query, but it creates redundancy, and is therefore not useful.

 

[lostinseganet]

Yes I plan to use this multiple times.
agree =3 , disagree= 1

For example if I had

Field 1 Field 2
Agree _____
disagree _____
agree _____
agree _____

I want access to realize that agree equals 3, and disagree equals 1
I want the result to look like

Field 1 Field 2
Agree 3
disagree 1
agree 3
agree 3


So in the end i only input the words strongly disagre, agree, disagree,
neutral, strongly agree (hate it, hate you...blah blah) (>^_^)>
and get whole numbers 1,2,3,4, or 5

 

[lostinseganet]

Yes what I am trying to do is a bit more complex than what I wrote. An
example of what I meant is in the reply right above yours. I am trying to
make a program in access to read a field of words then with a press of a
button or a few write numbers in another field. If it become redundant...well
i can press a few buttons.

 

[Steve Schapel]

Lostinseganet,

Do you mean the words you are trying to assess are the *entire* contents
of the text field, or they could be (as I had initially assumed)
contained *within* the data in the text field? This will make a
difference to how this should be approached.

What Dirk and I have both been trying to say is: "write numbers in
another field" is not a suitable thing to do. So far, you have shown no
good reason why your circumstances shouild be an exception to the rule.
I understand what you say you want to do, and you have explained this
in your reply to Dirk, but what I'm saying is: forget it. Sorry, I
don't want to seem harsh, but it will help you to understand a fairly
fundamental principle of database design.

If you have a reference table, like this:
Field 1 Field 2
Strongly Disagree 1
Disagree 2
Neutral 3
Agree 4
Strongly Agree 5

... then you would make a query, including this table and your main data
table, with the tables joined on the text response fields. And then you
can retrieve the corresponding number value within the query.

Having said that, I wouldn't do it like that either. I would normally
expect to see it the other way around. I would prefer to have the data
entered into the main table as the number. Access provides a wonderful
type of control called an Option Group which can be used to really make
a very nice way to do the entry and display of data in fields like this
on your forms. And then you would be using the lookup table like I
mentioned ion order to retrieve the text value corresponding to the
number in your main data.

 

[lostinseganet]

I do not feel insuted. I came here for an answer. I knew it could be
something I did not want to hear. I am trying to "assess are the *entire*
contents
of the text field" Hmmm....would excel be a better choice? Do you know of
any good books for teaching access 2003? Could I make what I want in excel
then place the spreadsheet in access. Also is there any documentation online
that describes why this would be so hard? Perhaps something online can give
me more info on the why that I am missing.

 

[Steve Schapel]

Lostinseganet,

See comments "inline"...

Hmmm....would excel be a better choice?

No. Excel is not a database program. You have a data management
application here, so you need to use a database, i.e Access is the
correct choice of tools

Do you know of
any good books for teaching access 2003?

I really like "Access 2003 Inside Out" by John Viescas. It is very
comprehensive.

Could I make what I want in excel
then place the spreadsheet in access.

Theoretically you could, but this would not be an appropriate approach.

Also is there any documentation online
that describes why this would be so hard?

This is not hard. It is *very easy*. What you were originally
suggesting was quite easy (but wrong). What I am suggesting is to make
it easier for you. What is hard, apparently, is for you to get your
head around the concept of data relations. Which is understandable, if
you don't have much database experience.

Perhaps something online can give
me more info on the why that I am missing.

I'm not sure what I could point you to here. But here's the basic
concept. You have data that says agree, disagree, etc. You want to
show a numerical value based on the text value. So, how does a computer
application "know" which number corresponds with which text? It can't,
unless you "tell it" somehow. How do you "tell it"? Well, there are
various ways. In this case, it is appropriate to list these values in a
table. So, like I said, you make a table with 2 fields, 5 records
(using your example), and this becomes your "master reference" of which
number corresponds with each text value. You *must* have this data
stored somewhere in your database. Understand? Otherwise, you can't
get what you want to happen, because the computer does not have the
necessary information. Ok, so now, there's a principle that data gets
stored only one time. So you have the numbers that correspond with the
text stored in your reference table here - that's once, and once is
enough. If you have the text values, then, in another table (presumably
your operational data, just guessing but maybe it's survey responses or
something), it is not valid to re-store the same data in there. You
have the information already. All you need is a simple query, that
includes your main table plus your reference table, and the number
values corresponding with the text values in the main table, are shown
by the *query*. Not by trying to programmatically enter the numbers
into an extra field in the main table - that's hard. By the query -
that's easy.

Hope that clarifies to some degree.

 

[lostinseganet]

How about using a number of if, then, else statements? I am reading a book on
access 2002, and you said it is possible I just do not know how yet. The
format that I have my data is in words if I change them into numbers by hand
it defeats the purpose of using a computer. Also do you know the ISBN# of the
book you recommended?

 

[lostinseganet]

Will boolean language work in access 2003? I have a book about MS access 2002
which is looking promising.

In

Hello everyone. I am trying to use access to see a word in one field
and write a number in another field. Like if agree =3 , disagree= 1.
I want access to see agree in a field and write 3 in another field.
How can this be done?

Are you talking about doing this for an entire table? Depending on the
circumstances, it sounds to me like you could most easily accomplish
this with an update query. The SQL for such a query would be along the
lines of

UPDATE [YourTableName]
SET [disagree] = 1
WHERE [agree] = 3

If this is a one-time job, write the query, run it, and you're done. If
it's something that has to be done repeatedly in an automated way, then
you can use VBA code to build and execute an SQL statement similar to
the above on the fly.

Note, though, that if you have one field whose value *always* depends on
another field in some deterministic way, then the dependent field should
probably not exist at all. Calculated values should seldom be stored,
but instead should be calculated on the fly.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[lostinseganet]

The book you recommended is for intermediate to advanced people using access.
Do you know of any others?

 

[Dirk Goldgar]

In

Will boolean language work in access 2003? I have a book about MS
access 2002 which is looking promising.

I don't understand what you're asking. Could you give an example of the
sort of "boolean language" you want to use, and in what context you want
to use it?

There's not much practical difference between Access 2002 and 2003.

 

[lostinseganet]

Oh just a combination of if, then, else, statements. I also have a book on
visual basic that I am trying to learn from.

 

[Steve Schapel]

Lostinseganet,

The best I can do here, I think, is ask that you re-read my earlier
response. Did you look at it yet? Do you have any questions about the
explanation I provided?

 

[Dirk Goldgar]

In

Oh just a combination of if, then, else, statements. I also have a
book on visual basic that I am trying to learn from.

If/Then/Else statements work in VBA code, but not in queries. Queries
can use the IIf and Switch functions to do a certain amount of logic
processing. That said, it's not clear that you should be thinking along
those lines. Have you understood all the advice in this thread? I
think Steve Schapel understood better than I did what it is you want to
do, and has been giving you good advice.

 

[lostinseganet]

Lostinseganet,

The best I can do here, I think, is ask that you re-read my earlier
response. Did you look at it yet? Do you have any questions about the
explanation I provided?

I guess making this part happen is the hardest to understand.
Ok, so now, there's a principle that data gets

stored only one time. So you have the numbers that correspond with the
text stored in your reference table here - that's once, and once is
enough. If you have the text values, then, in another table (presumably
your operational data, just guessing but maybe it's survey responses or
something), it is not valid to re-store the same data in there. You
have the information already. All you need is a simple query, that
includes your main table plus your reference table, and the number
values corresponding with the text values in the main table, are shown
by the *query*. Not by trying to programmatically enter the numbers
into an extra field in the main table - that's hard. By the query -
that's easy.

Do the text valuses and the number values have to be on the same page just
different fields?

 

[Steve Schapel]

Lostinseganet,

Do the text valuses and the number values have to be on the same page just
different fields?

I am not sure what you are referring to as a "page".

I am suggesting you make a new table.
In that table, make 2 fields.
Make one of the fields a Number data type.
Make the other field a Text data type.
Into this table, enter 5 records.
In the number field, enter numbers 1-5, one in each of the 5 records.
In the text field, enter the text ('agree' etc) that corresponds with
each of the 5 numbers.

When you have done that:
1. Let us know you have done it.
2. Provide a little context to help us understand what you are working
with. I.e. what is the nature of the other data in your main table,
where is the existing agree/disagree/etc data, what is the name of your
table(s) and relevant fields?