[AccessD] Query not working as expected.

Susan Harkins ssharkins at gmail.com
Mon Feb 13 13:07:30 CST 2012


HAVING is a funny one too -- don't know that this is an issue here, but 
worth mentioning. It does appear that you're using it correctly, against a 
group, so probably doesn't add much to your discovery process. -- Susan H.

11: The difference between WHERE and HAVING
The WHERE and HAVING clauses perform similar functions but they aren't 
interchangeable. WHERE limits the data returned by the SELECT clause; 
therefore, a GROUP BY is inconsequential. The engine compares data and 
eliminates records that don't satisfy the WHERE clause before it groups the 
records. On the other hand, the HAVING clause eliminates data that doesn't 
satisfy the grouping criteria.

If you have trouble remembering which clause to use, remember that the WHERE 
clause is positioned before the GROUP BY clause and the engine applies the 
WHERE clause beforegrouping the records.


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.

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 



More information about the AccessD mailing list