[AccessD] Query not working as expected.

Chester, try this:

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


or this:


SELECT
    A.WellNumber,
    A.[Test Date],
    Min(B.Switch_Date) AS MinOfSwitch_Date
FROM [tbl Injection Profiles to Run] AS A
LEFT JOIN Switches AS B
    ON B.Well = A.WellNumber AND B.Switch_Date>(A.[Test Date]+365)
GROUP BY
    A.WellNumber,
    A.[Test Date]



You might have to select Well number and Min(SwitchDate)
in the 2nd select above, then join that as a subquery back to
tbl A to get the test date.
On Mon, Feb 13, 2012 at 10:56 AM, Rocky Smolin <rockysmolin at bchacc.com>wrote:

> 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
>