compare data in a column and update if required (1 Viewer)

ainese

New member
Local time
Tomorrow, 03:04
Joined
Mar 1, 2013
Messages
1
Hi there,

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 'Customer Plans.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 or help would be hugely appreciated.

Thanks in advance,
Aine :banghead:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:04
Joined
Feb 19, 2002
Messages
42,970
Welcome aboard:)
Storing data multiple times violates standard database design principles - Read about normalization and first, second, and third normal form to begin your edudation.

The "calculated" column should be created in a query. That way it will always reflect the current value.

Select [Name], [Company], [Plan Type], IIf(IsNull([Company]) Or [Company] = "Corp&Gov" Or [Company] = "Head Office"), [Name], [Company]) As Customer
From [Customer Plans];

PS - "Name" is the name of a VBA property and can cause problems when used in VBA. It is best to avoid using VBA and SQL reserved words in your column names to avoid issues. You should also avoid using special characters and embedded spaces. Column names should start with a letter and include only lower and upper case letters, numbers, and the underscore. Also, if you think you might ever want to upgrade to a RDBMS, you should limit the size to 26 characters.
 

Users who are viewing this thread

Top Bottom