[AccessD] Query does not return all records

Taking out the date criteria results in all records from the dbo_StatusChanges table being returned which is correct.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Wednesday, January 12, 2011 2:24 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query does not return all records

If you take the Last() out of the SELECT and GROUP, do you get all expected
records?

Does this?

SELECT
    A.PID,
    A.Well_Number
FROM dbo_DSS_CompletionMaster AS A
    LEFT JOIN dbo_DSS_StatusChanges1 AS B ON B.PID = A.PID


Is it possible that you have duplicate (last) dates StatusDate values for a
given PID/Well Number?

On Wed, Jan 12, 2011 at 11:50 AM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:

> OK here is what I get. Apparently 5 records were added to the master table
> (dbo_DSS_CompletionMaster) today so it has 2090 records. If I use the
> criteria of <1/1/2001 I get 1037 records. If I use the criteria of >1/1/2001
> I get 1046 records. PID is a required field thus exists for all records in
> both tables. It is a unique field in the table dbo_DSS_CompletionMaster.
> There are 2083 unique PID's in the dbo_StatusChanges1 table.  The
> dbo_DSS_StatusChanges1 table has a total of 85508 records.
>
> Thanks for the assistance.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
> Sent: Wednesday, January 12, 2011 12:57 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query does not return all records
>
> If you change the HAVING line to read >#1/1/2001#, do you get 1039 records?
>
> Are any PID fields null? If so, you may have to use the following
>
> ON NZ(dbo_DSS_StatusChanges1.PID,0) = NZ(dbo_DSS_CompletionMaster.PID,0)
>
>
>
> On Wed, Jan 12, 2011 at 9:08 AM, Kaup, Chester <
> Chester_Kaup at kindermorgan.com> wrote:
>
> > The following query only returns 1046 records even though the table
> > dbo_CompletionMaster has 2085 distinct records. What am I doing wrong?
> > Thanks.
> >
> > SELECT dbo_DSS_CompletionMaster.PID,
> dbo_DSS_CompletionMaster.Well_Number,
> > Last(dbo_DSS_StatusChanges1.StatusDate) AS LastOfStatusDate
> > FROM dbo_DSS_StatusChanges1 RIGHT JOIN dbo_DSS_CompletionMaster ON
> > dbo_DSS_StatusChanges1.PID = dbo_DSS_CompletionMaster.PID
> > GROUP BY dbo_DSS_CompletionMaster.PID,
> dbo_DSS_CompletionMaster.Well_Number
> > HAVING (((Last(dbo_DSS_StatusChanges1.StatusDate))<#1/1/2001#));
> >
> >
> > 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
>
>
> --
> 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