1

This post is related in several aspects to the following:

Selecting all columns that start with XXX using a wildcard?

I am currently using Access 2010. I would like to add new columns to my table, based off values of the other columns.

Current table (Table #1):

Row | PlaceID | FoodItem1_10 | FoodItem1_02 | FoodItem2_10 | FoodItem2_02

001   Park           Y              N               Y           N 
002   Library        Y              N               Y           N
003   Museum         Y              N               Y           N

Where:

Item1_10....ItemN_10 is a field where a value of 'Y' (for Yes) is assigned if, at a particular location, they sell that food item only 10 months of the year. Otherwise, the value is 'N' for No.

Item1_02....ItemN_02 is a field where a value of 'Y' is assigned if, at a particular location, they sell that food item only 02 months of the year. Otherwise, the value is 'N' for No.

I want to add columns to Table #1, and have it look as follows:

Desired new table (Table #2):

Row | PlaceID | FoodItem1_10 | FoodItem1_02 | FoodItem2_10 | FoodItem2_02 | AnyItems_10months | AnyItems_02months

001   Park           Y              N               Y           N                  Y                   N
002   Library        Y              Y               Y           N                  Y                   Y
003   Museum         Y              N               Y           N                  Y                   N

Where:

AnyItems_10months is a field that captures whether or not a particular place sells any items for a 10 month period. This field takes the values 'Y' for when, in any column, the particular place has a value of 'Y' for columns Item1_10 ..... ItemN_10.

AnyItems_02months is a field that captures whether or not a particular place sells any items for a 02 month period. This field takes the values 'Y' for when, in any column, the particular place has a value of 'Y' for columns Item1_02 ..... ItemN_02.

What I have been trying:

Since my columns follow a particular naming pattern, I thought it would be best to use a wildcard to generate my two new columns as such:

Expression_Builder

Obstacle

-Access does not accept my expression.

Community
  • 1
  • 1
ealfons1
  • 353
  • 1
  • 6
  • 24
  • If the numbered field names reflect your actual table structure rather than just being used for expository purposes, you should consider normalising this in my view. I'd also get rid of the textual yes/no field and replace it either with an actual Boolean (Yes/No) field or (if Access/JET's issues with Boolean fields and nulls bite) an Integer field where 0 = False and any other number = True (if you do that you can still show 'N' and 'Y' in the UI). – Chris Rolliston May 13 '14 at 06:08

1 Answers1

0

Why don't you just hard-code it into a query? You're not going to be able to make a field like that in a table without reading the .Fields property of the table. It would get really messy. If you're always going to do it the same way, doing it in a query is going to be the easiest way.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117