Create New Table/Column

[ainese]

Hi,

I have a table "Customer Plans" with 100s of rows of data with 3 columns
'Name', 'Company', 'Plan Type'

I want to create a new column or table "Customer Details" with the the column name 'Customer' under the following criteria:

If the data in 'Customer Plans.Company' Column meets a certain criteria as in = "Corp&Gov" or "Head Office" or is "blank", I want it to populate the new column/table "Customer Details.Company" with the corresponding data in the 'Name' column

e.g.
if
Name = Anna B
Company = Corp&Gov
then
Customer = Anna B

I want to populate 'Customer' with Anna B for that record and so on down the list of data in 'Company' column.

If however the 'Company' column doesn't meet the criteria, I want to populate the new column/table 'Customer' with the current data in 'Company'

if
Name = Anna B
Company = Dealer
then
Customer = Dealer


I can do this in excel no bother but I have no experience with this in Access...


Any advise would be greatly appreciated.

Thanks,
Aine

 

[Rob Parker]

Hi Aine,

Creating a new column or table is definitely NOT the way to do this - you
should do this in a query (which you can consider as a "virtual table").
You should do this because, if the other data changes, the calculated value
in your table will become wrong.

Use an IIf statement in a new field in your query (name the field
"Customer"). If you've only got a few criteria you can put them in a value
list in the iif statement, as in the sample below. If there's lots, then
you should have a separate table for them (which you could also use as a
lookup table for Company in your Customer Plans table), with a flag (Yes/No
field) to indicate whether to use this as Customer; the query would be
rather different for this approach.

Here's the SQL for the simple approach (using the three criteria you listed;
you can add more if needed):

SELECT [Name], [Company], IIf([Company] In ("Corp&Gov", "Head Office",
""),[Name],[Company])
AS [Customer]
FROM [Customer Plans];

You can cut/paste this into the query designer in Access if you select SQL
in the Views box.

A couple of other points which will make your life with Access easier:

1. Don't use reserved words as names of tables, fields, anything at all, in
your database. "Name" is one such term that you have - it should be
something like PersonName. This one is bound to trip you up sometime.

2. Don't use spaces in names; either use an underscore instead, or use
CamelCase to make your names more readable. This is less of a problem than
point 1, but it does free you from the need to surround such table/field
names with square brackets.

HTH,

Rob