[AccessD] SubQuery help

Hi Chester

Try with:

WHERE ConfigMaster.WellName IN

(SELECT DISTINCT [tbl Statuses During Prior Month].Well_Number

However, WellName and Well_Number probably don't match. Just my guess from the field names.

/gustav


>>> Chester_Kaup at kindermorgan.com 18-05-2010 20:48 >>>
Not knowing much about subqueries I tried to construct this one. The subquery runs fine on its own and returns 379 records. When I run the query below I get a message this type subquery can return only one record. I don't understand.

SELECT DISTINCT CDate(Month([tbl Statuses During Prior Month]![RecordDate]) & "/" & Year([tbl Statuses During Prior Month]![recordDate])) AS TestDate, GroupMaster.Name, Count(ConfigMaster.WellName) AS [Active Well Count]
FROM [tbl Statuses During Prior Month] 
INNER JOIN 
(ConfigMaster INNER JOIN GroupMaster ON ConfigMaster.PID = GroupMaster.PID) ON [tbl Statuses During Prior Month].PID = ConfigMaster.ChildPID
WHERE ConfigMaster.WellName =

(SELECT DISTINCT [tbl Statuses During Prior Month].Well_Number
FROM [tbl Statuses During Prior Month]
WHERE ((([tbl Statuses During Prior Month].Status) 
In ("FH","FL","FM","RH","RL","RM","SH","SL","SM","PH","PL","PM"))))

GROUP BY CDate(Month([tbl Statuses During Prior Month]![RecordDate]) & "/" & Year([tbl Statuses During Prior Month]![recordDate])), 
GroupMaster.Name, 
Year([tbl Statuses During Prior Month]![RecordDate]), 
Month([tbl Statuses During Prior Month]![RecordDate])
HAVING (((GroupMaster.Name) Like "Manifold*"))
ORDER BY GroupMaster.Name;

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799