[AccessD] Query giving different results each time

Gustav,
This does not give the results I need.  I believe it is because this does 
not take into consideration a phone number being made primary...a second 
time...There may be a number of rows for the same pro_cm_seq...and the last 
one is now set to primary.

Sorry for the confusion...

Thanks,

Mark

P.S...This is something the company bought...It's not my monster...just 
something I was handed...lol


>From: Gustav Brock <gustav at cactus.dk>
>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 17:57:27 +0200
>
>Hi Mark and Scott
>
>Maybe I'm dense but - given your example data - this gives you exactly
>what you are asking for:
>
>   SELECT
>     bo,
>     phone
>   FROM
>     tblPhone
>   GROUP BY
>     bo,
>     phone
>   HAVING Count(pro_cm_seq)=1;
>
>/gustav
>
>
> > 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.
>
>--
>_______________________________________________
>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/