[AccessD] Query does not return all records

After taking out the date criteria, how many have a null  
Last(dbo_DSS_StatusChanges1.StatusDate)?

-- 
Stuart

On 12 Jan 2011 at 16:40, Kaup, Chester 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
>