[AccessD] Query giving different results each time

Mark,

You want to select the record where the PRIMARY_IND = Y for EACH BO ?
Correct?


SELECT B.profile_cm_seq,
        B.effdt,
        B.bo_id,
        B.primary_ind,
        B.phone
FROM tblPRS_Pending_PHONE B 
WHERE (B.bo_id,B.effdt) 
		IN(SELECT a.bo_id, Max(a.effdt)
               FROM tblPRS_Pending_PHONE a
               WHERE a.primary_ind="Y"
		   GROUP BY a.bo_id)
AND B.primary_ind="Y" 

----
If the backend is Access 97 you may have to split this into 2 queries

HTH
Patti

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Mark A Matte
> Sent: Wednesday, July 28, 2004 10:35 AM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Query giving different results each time
> 
> I'll do my best...
> 
> The table in question uses the following fields:
> bo_id...unique number string for each customer 
> profile_cm_seq...unique number string for each phone number 
> primary_ind...yes/no filed indicating primary Effdt...date 
> row is active Phone...phone number
> 
> Each time a phone number is added...or changed...multiple 
> rows are added.  
> If you have the following row
> bo   pro_cm_seq   Primary   effdt                 phone
> 10       86547          Y        5/21/2004       888/555/1212
> 
> and you add a new PRIMARY number336/555/2222 today...the 
> following rows will be added:
> bo   pro_cm_seq   Primary   effdt                 phone
> 10       86547          N        7/28/2004       888/555/1212
> 10       11235          Y        7/28/2004       336/555/2222
> 
> 
> So now...I need the most current PRIMARY Phone number...but I 
> have 2 records 
> that show as PRIMARY with 2 different numbers... I can't just 
> do a max on 
> the date because the phone numbers are different.
> 
> I even went as far as islolating some of the records that 
> showed in one pull 
> but not the next...if I run the sql for just that bo_id...it 
> pulls correctly 
> everytime.
> 
> The table only has 2967 records in it.  All fields mentioned 
> are TEXT except 
> for the effdt...which is a date field.
> 
> If I could not get the right number of rows in my output...I 
> would just keep 
> tweaking the sql...my concern is why the same sql keeps 
> giving me different 
> results.
> 
> Thanks,
> 
> Mark
>