[AccessD] Query giving different results each time

Hello All,

Thanks for all of the feedback...some of the more recent suggestions where 
actually where my post began...I was trying to use a sub query to get to the 
data I wanted...my problem wasn't that I couldn't get the data that I 
wanted...I would run the same sql againts a small, local table in an A97 
db(no BE or links just 1 db)...and each time I ran the sql...I would get a 
different number of records back.

My question was why...I tried all of the single and multiple queries 
suggested...but I could already get to the data with multiple queries...and 
the single queries suggested did not provide the correct data.  Here is the 
sql that would give different counts each time:

SELECT B.profile_cm_seq, B.effdt, B.bo_id, B.primary_ind, B.phone
FROM tblPRS_Pending_PHONE AS B
WHERE (((B.effdt)=(SELECT Max(A.effdt) AS MaxOfeffdt
FROM tblPRS_Pending_PHONE A
WHERE  B.bo_id =  A.bo_id;)) AND ((B.primary_ind)="y"));

I was dealing with less than 3000 records locally...so I didn't worry about 
creating indexes on this temp table.  I have since indexed the bo_id and 
effdt fields.  Now...the above sql runs everytime with the same number of 
rows returned.

I cannot send the table because it is customer info...but below is a 
'modified' sample of the data I have to work with...but...on a recordset 
this size...the above sql gave me exactly what I expected.

Does anyone know why adding the indexes would return different results?

Thanks,

Mark

bo_id	profile_cm_seq            primary_ind	effdt	phone
10	84562	                              Y	14-Jul-04	555/481-5549
10	2588	                              Y	22-Nov-03	555/363-2290
10	2588	                              N	22-Jan-04	555/363-2290
10	4444444	                              N	22-Nov-03	555/796-3360
10	4444444	                              N	22-Jan-04	555/796-3360
10	4444444	                              N	14-Jul-04	555/796-3360
10	65542	                              Y	22-Jan-04	555/363-4490
10             84562                                     N                
22-Jan-04  555/781-5549




>From: "Scott Marcus" <marcus at tsstech.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Query giving different results each time
>Date: Wed, 28 Jul 2004 13:46:53 -0400
>
>Mark,
>
>Is this any closer(I'm just taking stabs now)? Save both queries and run 
>the second one.
>
>qry1:
>SELECT tblPRS_Pending_PHONE.bo_id,
>        Max(tblPRS_Pending_PHONE.effdt) AS MaxOfeffdt
>FROM tblPRS_Pending_PHONE
>WHERE tblPRS_Pending_PHONE.Primary=True
>GROUP BY tblPRS_Pending_PHONE.bo_id;
>
>qry2:
>SELECT tblPRS_Pending_PHONE.bo_id,
>        Last(tblPRS_Pending_PHONE.Phone) AS Phone_Number
>FROM tblTest INNER JOIN Query4 ON (tblPRS_Pending_PHONE.effdt = 
>qry1.MaxOfeffdt)
>              AND (tblPRS_Pending_PHONE.bo_id = qry1.bo_id)
>WHERE tblPRS_Pending_PHONE.Primary=True
>GROUP BY tblPRS_Pending_PHONE.bo_id;
>
>Until I have a copy of the data, I cannot be sure this is 100% correct.
>
>Scott
>
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Overwhelmed by debt? Find out how to ‘Dig Yourself Out of Debt’ from MSN 
Money. http://special.msn.com/money/0407debt.armx