If Statement 2

[timidjewel]

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

 

[dhookom]

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.
[pre]
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[/pre]

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:

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

 

[Andrzejek]

If this is your [tt]TableX[/tt]:

[pre]
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
[/pre]
How would the outcome of your query look like?

[pre]
fruit emp_id emp_name
multiple 12345 John Smith
oranges 54321 Joseph Smith
multiple 98763 Mary Jones
[/pre]
???


---- Andy

There is a great need for a sarcasm font.

 

[timidjewel]

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

 

[dhookom]

I would try:

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

 

[timidjewel]

Andy, you are correct

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

Desired Result
PRPR_NPI[tab] TaxID
multiple[tab][tab][tab]12345
83[tab][tab][tab][tab][tab]54321
multiple[tab][tab][tab]98763

 

[Andrzejek]

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.

 

[timidjewel]

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'

 

[timidjewel]

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

 

[timidjewel]

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".

 

[dhookom]

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

Try:

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:

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

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