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 >