[AccessD] Query giving different results each time

Just the bo and phone would be fine...I pulled in the date just to verify I 
was getting the correct record.


>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 12:12:26 -0400
>
>Gustav,
>
>That works unless you want more of the fields in the query. If he wants 
>only the phone number...yes.
>
>Maybe I'm being dense(it won't be the first time...or the last).
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
>Sent: Wednesday, July 28, 2004 11:57 AM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Query giving different results each time
>
>
>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
>
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Don’t just search. Find. Check out the new MSN Search! 
http://search.msn.click-url.com/go/onm00200636ave/direct/01/