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?
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
 
PortletPaulfreelancerCommented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
PortletPaulfreelancerCommented:
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 ConnectWise

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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
<<"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.