[AccessD] Query not working as expected.

Yes there are only 40 records that meet the criteria. I was thinking the RIGHT JOIN would force all 142 records to be returned. If I am understanding correctly the criteria supersedes the LEFT JOIN. Basically I was wanting to return all 142 records and have a switch date if one existed and null if it did not. Seems like a project for sub query or just divide it into several queries. Thanks.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, February 13, 2012 12:56 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Query not working as expected.

Are there only 40 records which satisfy the criteria?  

Sometime when I have a problem with summation queries I unclick the
summation and run it to see what records are actually passing my criteria
tests and are being included in the query.

HTH

Rocky Smolin
Beach Access Software
858-259-4334
www.bchacc.com
www.e-z-mrp.com
Skype: rocky.smolin
 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Monday, February 13, 2012 10:49 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Query not working as expected.

I have the following query. The table tbl Injection Profiles to Run has 142
records. The query is only returning 40 records. If I remove the criteria
all 142 records are returned. Can I not use a criteria this way? Here is the
SQL. Thanks.

SELECT [tbl Injection Profiles to Run].WellNumber, [tbl Injection Profiles
to Run].[Test Date],
Min(Switches.Switch_Date) AS MinOfSwitch_Date FROM Switches RIGHT JOIN [tbl
Injection Profiles to Run] ON Switches.Well = [tbl Injection Profiles to
Run].WellNumber GROUP BY [tbl Injection Profiles to Run].WellNumber, [tbl
Injection Profiles to Run].[Test Date] HAVING
(((Min(Switches.Switch_Date))>[tbl Injection Profiles to Run]![Test
Date]+365));

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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com