×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

If Statement
2

If Statement

If Statement

(OP)
I'm new to query building in Access. I have a unique identifier such as an employee id and the result in question is fruits. If an employee has more than one fruit, i want to see the word "multiple". If the employee has only one fruit, i want to see that fruit. How do i write a formula to show this. I'm assuming an IIF statement would work, but i'm not sure how to write it. Can someone help?

fruit emp id name
apples 12345 John Smith
oranges 12345 John Smith
lemons 12345 John Smith
oranges 54321 Joseph Smith
apples 98763 Mary Jones
lemons 98763 Mary Jones

RE: If Statement

Hi timidjewel,
Welcome to Tek-Tips. A couple suggestions for posting here:
Use TGML (those little icons above the post box), The Pre tag works great for displaying records.
fruit	 emp id	  name       
apples	 12345	  John Smith
oranges	 12345	  John Smith
lemons	 12345	  John Smith
oranges	 54321	  Joseph Smith
apples   98763	  Mary Jones
lemons	 98763	  Mary Jones 

Provide a display of your expected results. I assume you only want one record per employee.

Provide actual significant table and field names with data types.

Try this:

CODE --> sql

SELECT DISTINCT IIf(DCount("Fruit","YourTableName","[emp ID] =" & [emp ID})>1,"multiple",Fruit), [emp id], [Name]
FROM YourTableName 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: If Statement

If this is your TableX:

fruit   emp_id emp_name
apples  12345  John Smith
oranges 12345  John Smith
lemons  12345  John Smith
oranges 54321  Joseph Smith
apples  98763  Mary Jones
lemons  98763  Mary Jones
 
How would the outcome of your query look like?

fruit    emp_id emp_name
multiple 12345  John Smith
oranges  54321  Joseph Smith
multiple 98763  Mary Jones
 
???


---- Andy

There is a great need for a sarcasm font.

RE: If Statement

(OP)
Thank you for the tips! I apologize, but i am not familiar with TGML and couldn't get it to look as good as your reply. But you are correct in that I only want one record per employee (TaxID). This is a great start so I am including my current sql since i'm joining to another table. I was trying to be discreet with the data due to the sensitivity of the data I am working with, but this may be easier.

SELECT dbo_CMC_PRPR_PROV.PRPR_NPI, ProviderList.TaxID
FROM ProviderList INNER JOIN dbo_CMC_PRPR_PROV ON (ProviderList.[Mailing Name] = dbo_CMC_PRPR_PROV.PRPR_NAME) AND (ProviderList.TaxID = dbo_CMC_PRPR_PROV.MCTN_ID)
GROUP BY dbo_CMC_PRPR_PROV.PRPR_NPI, ProviderList.TaxID;

So my example in the original thread for Fruits is actually PRPR_NPI and the emp id is actually TaxID. This probably changes your solution so my apologies for the confusion.

Current Result
PRPR_NPI TaxID
12 12345
14 12345
52 12345
83 54321
18 98763
23 98763

Desired Result
PRPR_NPI TaxID
multiple 12345
83 54321
multiple 98763

RE: If Statement

I would try:


CODE --> SQL

SELECT IIf(DCount("*","dbo_CMC_PRPR_PROV","TaxID = " & TaxID)>1,"Multiple", PRPR_NPI) As PRPR_NPICalc, ProviderList.TaxID
FROM ProviderList INNER JOIN
 dbo_CMC_PRPR_PROV ON (ProviderList.[Mailing Name] = dbo_CMC_PRPR_PROV.PRPR_NAME) AND (ProviderList.TaxID = dbo_CMC_PRPR_PROV.MCTN_ID)
GROUP BY IIf(DCount("*","dbo_CMC_PRPR_PROV","TaxID = " & TaxID)>1,"Multiple", PRPR_NPI), ProviderList.TaxID; 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: If Statement

(OP)
Andy, you are correct

Current Result
PRPR_NPI     TaxID
12                12345
14                12345
52                12345
83                54321
18                98763
23                98763

Desired Result
PRPR_NPI     TaxID
multiple            12345
83                    54321
multiple            98763

RE: If Statement

I am sure Duane's solution will work for you.

But - as far as "not familiar with TGML and couldn't get it to look as good as your reply" - you can try to use them. Highlight the text in your post or reply, and click on "those little icons above the post box". Click on Preview to see how your post will look like before clicking on "Submit Post" button.


---- Andy

There is a great need for a sarcasm font.

RE: If Statement

(OP)
Thank you both for the quick reply.

Duane, when i enter that SQL into my query, I get the below error

The expression you entered as a query parameter produced this error: 'Microsoft Access cannot find the name 'TaxID' you entered in the expression'

RE: If Statement

(OP)
Thanks Andy! I did practice and preview and I think I am getting the hang of it. Thank you for your help!

RE: If Statement

(OP)
I was able to get the query to run by removing the & symbol.

SELECT IIf(DCount("*","dbo_CMC_PRPR_PROV","TaxID = TaxID")>1,"Multiple",[PRPR_NPI]) AS PRPR_NPICalc, ProviderList.TaxID
FROM ProviderList INNER JOIN dbo_CMC_PRPR_PROV ON (ProviderList.TaxID = dbo_CMC_PRPR_PROV.MCTN_ID) AND (ProviderList.[Mailing Name] = dbo_CMC_PRPR_PROV.PRPR_NAME)
GROUP BY IIf(DCount("*","dbo_CMC_PRPR_PROV","TaxID = TaxID")>1,"Multiple",[PRPR_NPI]), ProviderList.TaxID;

However, this did not work. I double-checked a tax id that i know has multiple NPI's and it is showing each NPI instead of the word "multiple".

RE: If Statement

You need to filter the DCount() which you removed.

Try:

CODE --> SQL

SELECT IIf(DCount("*","dbo_CMC_PRPR_PROV","TaxID = " & MCTN_ID)>1,"Multiple",[PRPR_NPI]) AS PRPR_NPICalc, ProviderList.TaxID
FROM ProviderList INNER JOIN dbo_CMC_PRPR_PROV ON (ProviderList.TaxID = dbo_CMC_PRPR_PROV.MCTN_ID) AND (ProviderList.[Mailing Name] = dbo_CMC_PRPR_PROV.PRPR_NAME)
GROUP BY IIf(DCount("*","dbo_CMC_PRPR_PROV","TaxID = " & MCTN_ID)>1,"Multiple",[PRPR_NPI]), ProviderList.TaxID; 

If this doesn't work then it might be an issue with field data types which you didn't provide as suggested.

You can do some testing by opening the debug window (press Ctrl+G) and entering a known good TaxID (assuming the field is numeric:

CODE --> debug

? DCount("*","dbo_CMC_PRPR_PROV","TaxID =12345") 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close