Query Confusion

  • Thread starter Thread starter Kendra
  • Start date Start date
K

Kendra

I am trying to write a very simple query:

In the first column, there is text data that represents
months. For example "January" in one entry, "February" in
the next.

I want the next column to convert this text data into a
numerical value. For example, if the first column says
January, I want the second column to say "1," and if the
next entry is "February" in the first column, I want the
second column in the second entry to show "2."

I've tried searching the manual but just can't figure out
how and in what window to do this. I am used to excel and
this is totally different. I am using access 2002.

Thanks,
Kendra
 
There are a number of different ways you can do this. However, the most
efficient way (I think) is to create a Table with just 2 Fields

* MonthName Text(10) PK
* MonthNo Byte Uniquely indexed

Fill 12 records for the MonthNames and corresponding MonthNo and add this
Table to your Query joining to the current Table using the MonthName Field.
Add the MonthNo as an output Field of your Query.

HTH
Van T. Dinh
MVP (Access)
 
Van,

One caveat to this is that if the query is part of the basis for a form,
depending on the rest of the query, the MonthNo field is editable and the value
for a month in the table could be changed by the user on the form.
 
I am trying to write a very simple query:

In the first column, there is text data that represents
months. For example "January" in one entry, "February" in
the next.

I want the next column to convert this text data into a
numerical value. For example, if the first column says
January, I want the second column to say "1," and if the
next entry is "February" in the first column, I want the
second column in the second entry to show "2."

I've tried searching the manual but just can't figure out
how and in what window to do this. I am used to excel and
this is totally different. I am using access 2002.
Click to expand...

Yes. Excel is a spreadsheet, a good one; Access is a relational
database. They are as different as hammers are from crescent wrenches!

I would suggest NOT storing these two redundant ways of displaying a
month in your table at all. You should also not use a query datasheet
for routine data entry - use a Form instead. If your table contains
the month number (which I'd recommend) you can use a Combo Box on the
form to display the month name but store the values 1 to 12 - just
create a little twelve-row table with two fields, number and text:
1 January
2 February
3 March
....
12 December

and use it as the source of the combo.

If you really, really want the calculated field in the query, you can
use

=Switch([Field1] = "January", 1, [Field1] = "February", 2, [Field1] =
"March", 3, ... <etc>)
 
Back
Top