SQL Statement Containing Multiple Subqueries for a Single Result Not Working

I'm back looking for help with another query.  I've attached a sample MS Access database containing Query1.  I'm trying to do something I've never tried before, and I don't know how to make it work.  Here's the query:
SELECT IS1.receipt_no,
        IS1.transmittal_no,
        CY.campaign_year,
        EXISTS(SELECT IS2.receipt_no FROM InspectionStickers AS IS2 WHERE IS2.receipt_no = IS1.receipt_no AND IS2.fk_inspection_type = 1) AS AI,
        EXISTS(SELECT IS2.receipt_no FROM InspectionStickers AS IS2 WHERE IS2.receipt_no = IS1.receipt_no AND IS2.fk_inspection_type = 3) AS AO,
        EXISTS(SELECT IS2.receipt_no FROM InspectionStickers AS IS2 WHERE IS2.receipt_no = IS1.receipt_no AND IS2.fk_inspection_type = 2) AS SI,
        EXISTS(SELECT IS2.receipt_no FROM InspectionStickers AS IS2 WHERE IS2.receipt_no = IS1.receipt_no AND IS2.fk_inspection_type = 4) AS IM,
        IIF((SELECT IS2.receipt_no FROM InspectionStickers AS IS2 WHERE IS2.receipt_no = IS1.receipt_no AND IS2.status <> 'U') = Null, 'Used', IIF((SELECT IS2.receipt_no FROM InspectionStickers AS IS2 WHERE IS2.receipt_no = IS1.receipt_no AND IS2.status <> 'N') = Null, 'New', 'Current')) AS status 
FROM   InspectionStickers AS IS1 
INNER JOIN CampaignYear AS CY ON IS1.fk_campaign_year = CY.pk_campaign_year 
GROUP BY
        IS1.receipt_no,
        IS1.transmittal_no,
        CY.campaign_year 
ORDER BY 1 DESC

Open in new window


My problem is with the last column I'm trying to return, which I can only conceptualize as requiring nested IIF statements containing subqueries.  The error I'm getting is 'At most one record can be returned by this subquery.  My objective is to return 'New' if all rows for a given receipt_no are in status 'N', 'Used' if all rows for a given receipt_no are in status 'U', otherwise 'Current'.  Any help would be greatly appreciated.
petekipeOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
I am not an MS-Access expert.

From the error I can glean that while the subquery should be returning single record it is in this case returning more.

Hopefully, you should be able to run the query outside this query and check if that is the case and the expected result is indeed correct and unavoidable.

If such is the case, will you not be able to resort to probably count(*) and check if it is 0 or != 0. Something like that, just to be compliant.
0
PortletPaulEE Topic AdvisorCommented:
I don't see an attachment

  'New' if all rows for a given receipt_no are in status 'N'
, 'Used' if all rows for a given receipt_no are in status 'U'
, otherwise 'Current'

The hard part is "all rows"

A "generic"  way to tackle this is as follows:

select receipt_no , min(status), max(status)
from yourtable
group by receipt_no

If MIN() = MAX() and MIN = 'N' = New
If MIN() = MAX() and MIN = 'U' = Used
else 'Current'
0
petekipeOwnerAuthor Commented:
I'll reattach the file.
Sample.mdb
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PortletPaulEE Topic AdvisorCommented:
SELECT InspectionStickers.receipt_no, Min(InspectionStickers.status) AS MinStatus, Max(InspectionStickers.status) AS MaxStatus, Switch(Min([status])=Max([status]) And Min([status])='N','New',Min([status])=Max([status]) And Min([status])='U','Used',Min([status])=Max([status]) And Min([status])='C','Current',Min([status])<>Max([status]),'Current') AS ReceiptStatus
FROM InspectionStickers
GROUP BY InspectionStickers.receipt_no;

Open in new window

ms-access.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
petekipeOwnerAuthor Commented:
Thank you for a perfect solution Paul!  I'd never heard of Switch before, but it's in my toolkit now.
0
PortletPaulEE Topic AdvisorCommented:
I find switch easier than nested iifs but in truth i havent used access in many years.

All the best.
0
petekipeOwnerAuthor Commented:
I need to give the user a checkbox to view only 'New' and 'Current' rows, or all rows.  So I just tried adding a filter to the query that would add a WHERE clause to optionally not return 'Used' rows, but I'm running into trouble.  When I tried

     WHERE  MinStatus = MaxStatus And MinStatus <> = 'U'

...I got the error 'Syntax error (missing operator) in query expression 'MinStatus = MaxStatus And MinStatus <> = 'U''.

When I tried

     WHERE Min(IS1.status) = Max(IS1.status) And Min(IS1.status) = 'U'

...I got the error 'Cannot have aggregate function in WHERE clause (Min(IS1.status)=Max(IS1.status) And Min(IS1.status)<>'U')'

Any ideas?
0
petekipeOwnerAuthor Commented:
Just figured it out - I needed a HAVING clause.
0
PortletPaulEE Topic AdvisorCommented:
<<"I needed a HAVING clause."
yep!

The having clause allows assessment of aggregated values, which don't exist until after the group by is performed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.