[AccessD] Query does not return all records

The suggested query returns no records.

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

try this:

SELECT PID,StatusDate FROM dbo_DSS_StatusChanges1 GROUP BY PID, StatusDate
HAVING Count(StatusDate) >1 AND (StatusDate <#1/1/2001#)

It's either a duplicate (last) date or a null date as Stuart mentioned.

D

On Wed, Jan 12, 2011 at 2:40 PM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:

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