Query Stump (1 Viewer)

[riverstar5]

Hello,

I have a table with telephone contact information however some of my contacts have a contact preference, home or cell... how do I create a query to have it show only the contact preference number that has been chosen.

Thank you in advance for your assistance...

 

[Alansidman]

Where are you looking to have this displayed. In a form or a query?

 

[riverstar5]

In a query...

 

[Alansidman]

If you do not have a field that indicates the preference, then create one and populate it as necessary. I am assuming that it will be text field that is populated with either cell or home. I am assuming that you have a flat table with the names and phone numbers in the same table. I am also assuming that that you have two fields for phone numbers, cell and home.

In a new field, create an expression, PrefPhone: IIF([TableName].[PrefField]="Cell",[tableName].[Cell],[tableName].[home])

In your query, uncheck the display for fields cell and home so that they do not display and only the preferred number displays.

Alan

 

[riverstar5]

so this is the fields that I have

First Name
Last Name
Contact Preference, has a dropdown to pick "cell or home"
Cell Number
Home Number

 

[Alansidman]

And here is your query

Select YourTableName.[First Name], YourTableName.[Last Name],PrefPhone: IIF([TableName].[Contact Preference]="Cell",[tableName].[Cell Number],[tableName].[home Number])
FROM YourTableName;

Insert the name of your table wherevever I have YourTableName. If it has spaces then surround it with Square Brackets. A good naming convention is to not have spaces in table or Field Names. With spaces you are prone to errors and omissions.

BTW: It is also not a good practice to have drop down (lookups) in your table. They should be in your forms. Read this discussion. http://access.mvps.org/access/lookupfields.htm
Alan

 

[riverstar5]

Thank you so much for all your help, I think I got it...