Using criteria in select query for recoding of variables

P

Peter

I am a very new user. I want to code the values of a new variable based on
the values of an old variable. For example 1 and 2 in old variable become A
in new variable; 3 becomes B in the same new variable; and the value 4 and 5
in the old variable becomes C in the new variable.
 
M

Michel Walsh

Hi,


Not sure I understand fully the problem. Is it a kind of "lookup" ? if so,
have a table that list the translations to be preformed:


Old ToBe ' fields name
1 A
2 A
3 B
4 C
5 C



and then, either a join, either a DLookup would do:



myNewValue = DLookup("ToBe", "tableNameHere", "Old=" & myOldValue )



Hoping it may help,
Vanderghast, Access MVP
 
P

Peter

--
Peter


Michel Walsh said:
Hi,


Not sure I understand fully the problem. Is it a kind of "lookup" ? if so,
have a table that list the translations to be preformed:


Old ToBe ' fields name
1 A
2 A
3 B
4 C
5 C



and then, either a join, either a DLookup would do:



myNewValue = DLookup("ToBe", "tableNameHere", "Old=" & myOldValue )



Hoping it may help,
Vanderghast, Access MVP
Click to expand...

Hey thanks Michel, I reallly appreciate your time.

You interpreted my question correctly and your suggestion will be very
useful. I will try it as a means of learning about lookup applications.

My purpose also is to learn about using criteria and expressions in the
query grid. So, could you please think about another solution that is based
on using the criteria line to achieve the same outcome. I think if you
created something for me in sql that Ii could just cut and paste and adapt
the code for running on my table. It will be my first time using a sql
statement! (I have got the gist of doing the pasting in from other
conversations i have looked at in this discussion group.)

Could you pls make the code as clear as possible and using the professional
layout conventions so i can see how it should be written right from the start
of my learning about sql.

Imagine the table just has the field "old" as you have conjectured and with
the values you have put in. Say there is no other lookup table. The new
field would be the one you suggested, "feildsname". This would be creasted
in an expression. And then the values of the "fieldsname" variable would end
up as per your "Tobe" field above. In other words your Tobe field is exactly
how i want the calculated field to contain.

And lastly, could you recommend a good clear book for idiots to show me how
to write sql code.

Thanks very much. Looking forward to hearing back.
 
M

Michel Walsh

Hi,


The greatest book is probably the query designer, at least, for start.

Bring the two tables in a new query of the query designer, the one having
the original values, and the one having the Translations (with fields Old ad
ToBe).

In the upper part, select the field to be translated, and drag and drop it
over the field 'Old'. That creates a join between the two tables. An inner
join, in fact. An inner join can be seen as some kind of lookup, or
translator, when used to reach another field, here ToBe. So, in the grid,
now, drag fields you need, including the field ToBe, from the table of
translations.

That's all.

You can take a look at the generated SQL.


Sure, that does fine for simple SQL statements, but for more, you can take a
look at Queries for Mere Mortals:
http://www.amazon.com/gp/product/product-description/0201433362/102-2353860-8771305?redirect=true

For really advanced stuff, I suggest Joe Celko books
(http://www.amazon.com/gp/product/1558605762/102-2353860-8771305?v=glance&n=283155
and
http://www.amazon.com/gp/product/1558604537/102-2353860-8771305?v=glance&n=283155
and more specialized,
http://www.amazon.com/gp/product/1558609202/102-2353860-8771305?v=glance&n=283155).
You can also hang in newsgroups like this one and the one about MS SQL
Server, as example, if not to see something about how, something to see
about WHAT can be done in SQL


As for the convention, I personally adopted the one from Joe Celko: key
words in all caps (but I often use As instead of AS ), table name with
plurals, field name with singular: skills, or Skills is a table, skill,
or Skill, is a field, and if a table is a junction table, it is often made
of the two field it holds: SkillsWorkers is a junction table with (main)
fields Skill and Worker, so that one row in SkillsWorkers is about one skill
that has one worker, use has many rows as required to specify all the
relevant skills of a given worker.


Hoping it may help,
Vanderghast, Access MVP
 
P

Peter

Thanks again Michel,
your suggestions about how to use the query grid are great. and youve gone
to a lot of trouble to dig out useful literature on sql language. i am very
thankful.

There is just one remaining thing i need to nag you with. In my reply i
asked you about another way to skin this cat so i could learn about maybe if
/ then statments. so rather than the lookup table option my question tried to
find a way without the lookup table. i have reproduced this bit of my
question as follows:
Imagine the table just has the field "old" as you have conjectured and
with
the values you have put in. Say there is no other lookup table. The new
field would be the one you suggested, "feildsname". This would be
creasted
in an expression. And then the values of the "fieldsname" variable would
end
up as per your "Tobe" field above. In other words your Tobe field is
exactly
how i want the calculated field to contain.
Click to expand...
pls just give me a lead about the query to write to get "fieldname" to
contain different values depending on the different values of the only other
variable in the table, "old".

i know from my experience with ms excel, sas and spss that with computing
there is always two ways to skin a cat. youve shown me the lookup table
option, which i will use as a separate learning curve. And i will now i am
asking you to show me please another type of solution that tries to cope with
the creation of a new field and its contents being created by if / then
statements (?). i am really interested in this style of access programming
because i can see that for the work ahead of me i need to get this firmly in
my head as a foundation, since it will involve decision making to output
different results.

michel i hope not being too demadning but you will be doing me a very big
favour if i can just get past this issue and learn how to do it and similar
applications fundamental to my work with access in the next little while.

many thanks for your patience. you and your colleagues are doing a fine job.
 
Top