Hi
Been trying to guess this for hours...
I want to count the number of records returned in a SELECT subquery and alter the WHERE of the main query accordingly...I know this should be possible.. So in English... (System_Record is a Boolean)...
if records returned by SELECT = 0 then set the WHERE condition " OR System_Record=TRUE"
if records returned by SELECT > 0 then set the WHERE condition " AND System_Record=FALSE"
My code so far.. don't laugh! The QBE did!
Code:
SELECT
TBL.Item_Name,
TBL.Unique_No,
TBL.ID,
(SELECT COUNT(UNIQUE_NO) FROM ADDRS_TBL AS TBL1
WHERE TBL1.ID=75666) As My_Count
FROM ADDRS_TBL AS TBL
WHERE (TBL.ID=75666)
SWITCH(My_Count=0, " OR System_Record=True", My_Count>0, " AND System_Record=False")
Yikes!! Can someone help please?