[AccessD] Inner Join Query

David McAfee davidmcafee at gmail.com
Thu Nov 20 14:38:55 CST 2008


Well, you do have the added join condition of "AND ([tbl
StatusCodesActiveNew].Area=[
>
> qry Well Status 1 Month Prior with Area].Area)"


If you remove that do you get your remaining records?

David McAfee


On Thu, Nov 20, 2008 at 11:11 AM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:

> The following query works correctly returning the 15 records in the tbl
> StatusCodesActive table.
>
> SELECT [tbl StatusCodesActive].Stat_Code,
> DateSerial(Year(Date()),Month(Date()),1)-1 AS StatusDate1,
> [tbl StatusCodesActive].Stat_Desc,
> Count([qry Well Status 1 Month Prior].Status) AS CountOfStatus
> FROM [tbl StatusCodesActive]
> LEFT JOIN [qry Well Status 1 Month Prior] ON [tbl
> StatusCodesActive].Stat_Code = [qry Well Status 1 Month Prior].Status
> GROUP BY [tbl StatusCodesActive].Stat_Code,
> DateSerial(Year(Date()),Month(Date()),1)-1,
> [tbl StatusCodesActive].Stat_Desc, [tbl StatusCodesActive].SortOrder
> ORDER BY [tbl StatusCodesActive].SortOrder;
>
> The following query does not work correctly. Instead of returning 45
> records it returns only 28 records. Stat_Code and Area together create a
> unique record in the table tbl StatusCodesActiveNew table. Am I trying to do
> something here that will not work? Thanks.
>
> SELECT [tbl StatusCodesActiveNew].Stat_Code,
> [tbl StatusCodesActiveNew].Stat_Desc,
> DateSerial(Year(Date()),Month(Date()),1)-1 AS StatusDate1,
> Count([qry Well Status 1 Month Prior with Area].Status) AS CountOfStatus,
> [qry Well Status 1 Month Prior with Area].Area
> FROM [tbl StatusCodesActiveNew]
> LEFT JOIN [qry Well Status 1 Month Prior with Area]
> ON ([tbl StatusCodesActiveNew].Stat_Code=[qry Well Status 1 Month Prior
> with Area].Status)
> AND ([tbl StatusCodesActiveNew].Area=[qry Well Status 1 Month Prior with
> Area].Area)
> GROUP BY [tbl StatusCodesActiveNew].Stat_Code,
> [tbl StatusCodesActiveNew].Stat_Desc,
> DateSerial(Year(Date()),Month(Date()),1)-1,
> [qry Well Status 1 Month Prior with Area].Area;
>
> 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
>



More information about the AccessD mailing list