[AccessD] Query giving different results each time

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