SQL Statement Containing Multiple Subqueries for a Single Result Not Working

petekipe
petekipe used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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'
petekipeOwner

Author

Commented:
I'll reattach the file.
Sample.mdb
Success in ‘20 With a Profitable Pricing Strategy

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

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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
petekipeOwner

Author

Commented:
Thank you for a perfect solution Paul!  I'd never heard of Switch before, but it's in my toolkit now.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I find switch easier than nested iifs but in truth i havent used access in many years.

All the best.
petekipeOwner

Author

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?
petekipeOwner

Author

Commented:
Just figured it out - I needed a HAVING clause.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial