Correlated Subquery in SELECT statement Error 1

[bajo71]

Hello,

The following code populates a field named PREV_MONTH with the values in EFFECTIVE_DATE using DateAdd to extract previous month's value. The resultset returns the records exactly as I anticipated, however, about three seconds later, an error message appers reading "At most one record can be returned by this subquery", at which point all fields turn to 'ERROR'.

Do I need to change the subquery to allow for aggregate output? Thank you.......

SELECT e1.EFFECTIVE_DATE, e1.VALUE, e1.TOTAL_TXN_AMT, (SELECT TOTAL_TXN_AMT
FROM AL_EVNT_HEADER e2
WHERE e2.EFFECTIVE_DATE=format(DateAdd("m", -1, e1.EFFECTIVE_DATE), "YYYY-MM-DD") AND e2.VALUE=e1.VALUE) AS PREV_MONTH
FROM AL_EVNT_HEADER AS e1
WHERE (((e1.ACTION)="SEGMENT"))
ORDER BY e1.VALUE;

 

[dhookom]

This is an accurate error message. There are more than 1 records matching the Effective_Date and Value. You can use an aggregate or TOP 1 to fix the issue.

Is the EFFECTIVE_DATE field in AL_EVNT_HEADER actually text?

Duane
Hook'D on Access
MS Access MVP

 

[bajo71]

Yes, the Effective_Date field is text, hence the Format function. I suppose I could've used CDate. I will try the TOP 1 argument.

Thanks