[AccessD] Query does not return all records

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