In Microsoft Access is there a way to put IIF statement criteria into a table for easier management and reference?

5 pts.
Tags:
Access 2003
Access Tables
IIF function
IIF statement
Microsoft Product ID
I have some data with a list of Product IDs and I want to assign the Product IDs a category name based the criteria below. 
<br><br>
IIF Product ID = #*C#* then category name = Cable
<br><br>
IIF Product ID = 00#* or 497* then category name = Component
<br><br>
... 
<br><br>
I can get it to work using multiple IIF statements like the one below:
<br><br>
Product Type: IIf([Product ID] Like "#*C#*","Cable",IIf([Product ID] Like "00#*" Or [Product ID] Like "497#*","Component",IIf([PIDType4]="7346M" Or [PIDType5]="CPP7346M","7346M","Regular Product")))
<br><br>
But I have a lot more criteria I would like to enter in and gets hard to manage it this way. Is there someone I can create a table with all of these criteria and just reference the table instead?
<br><br>
Thanks, Adam


Software/Hardware used:
Microsoft Access 2003

Answer Wiki

Thanks. We'll let you know when a new response is added.

My recommendation would be to create a lookup table for categories, and alter your products table to add a new column for a category Id. Then you would need to show the information joining both tables (on the category Id).

After adding the new column you would need to execute some UPDATEs on the products table to assign the appropriate category id (and modify the input form so any new product gets inserted with its corresponding category Id).

———–

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • As230149
    [...] In Microsoft Access is there a way to put IIF statement criteria … [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following