David McAfee
davidmcafee at gmail.com
Fri Jan 14 14:32:15 CST 2011
Sorry, I was out sick yesterday.
Try this:
SELECT
B.PID,
B.Well_Number,
Last(A.StatusDate) AS LastOfStatusDate,
C.CountOfDate
FROM dbo_DSS_StatusChanges1 AS A
RIGHT JOIN dbo_DSS_CompletionMaster B ON A.PID = B.PID
INNER JOIN (SELECT PID,StatusDate, Count(StatusDate) AS CountOfDate FROM
dbo_DSS_StatusChanges1 GROUP BY PID, StatusDate HAVING Count(StatusDate) >1)
AS C ON A.PID = C.PID AND A.StatusDate = C.StatusDate
GROUP BY
B.PID,
B.Well_Number
HAVING (Last(A.StatusDate)<#1/1/2001#);
It is basically your existing query inner joined to a duplicates query.
Anything that shows up is a duplicate and would affect your numbers.
HTH
David
On Thu, Jan 13, 2011 at 8:35 AM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>