[AccessD] SubQuery help

In the way you are using your subquery, it can only return one record.  That applies to using a subquery as a field, or as a Like or Equal to in the WHERE portion of the root query.  Think of it this way, can you have a query like this:

Select SomeField FROM SomeTable WHERE SomeOtherField=1,2,3,4,5

Won't work.  

So do to what I think you are doing, change the = to In, which CAN be multiple values.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Tuesday, May 18, 2010 1:49 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] SubQuery help

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

 
No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.