asked on

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.

Nitin Sontakke

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.

PortletPaul

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'

petekipe

ASKER

I'll reattach the file.
Sample.mdb

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

petekipe

ASKER

Thank you for a perfect solution Paul! I'd never heard of Switch before, but it's in my toolkit now.

PortletPaul

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

All the best.

petekipe

ASKER

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?

petekipe

ASKER

Just figured it out - I needed a HAVING clause.

PortletPaul

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