[AccessD] Inner Join Query

If I remove that condition I get all 45 records but the count is all wrong. What I did was create a field in both tables that was a concatenation of the area and stat_code fields. Now it works correctly.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Thursday, November 20, 2008 2:39 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Inner Join Query

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
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com