[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





>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 09:35:47 -0400
>
>Lets try a different route. Please explain what you have and what result 
>you want.
>
>Scott Marcus
>
>-----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 9:27 AM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Query giving different results each time
>
>
>Scott,
>
>Thanks for the feedback...but the results of this sql are also
>inconsistant...I ran it 3 times...and got 3 different results...the 
>reslults
>are typically close...for the last 3 tries...it returned 2365, 2369 and
>2375...without changing anything...just re-running the sql???
>
>I fell very confused...lol
>
>Any ideas?
>
>Thanks,
>
>Mark
>
>
> >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 09:12:00 -0400
> >
> >Mark,
> >
> >Do you try the following to see if it fixes your problem?
> >
> >SELECT B.profile_cm_seq,
> >        B.effdt,
> >        B.bo_id,
> >        B.primary_ind,
> >        B.phone
> >FROM tblPRS_Pending_PHONE B
> >WHERE (B.effdt=(SELECT Max(A.effdt)
> >                 FROM tblPRS_Pending_PHONE A
> >                 WHERE B.bo_id = A.bo_id
> >                       AND
> >                       B.profile_cm_seq = A.profile_cm_seq))
> >       AND
> >       B.primary_ind="y";
> >
> >
> >Scott Marcus
> >
> >--
> >_______________________________________________
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>_________________________________________________________________
>Discover the best of the best at MSN Luxury Living. http://lexus.msn.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

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/