[AccessD] Query giving different results each time

OK Mark, but it is a bit difficult if we only get bits and pieces.
How about a "true" sample of data - to stop guessing?

As for this new information this will do:

   SELECT
     bo,
     phone
   FROM
     tblPhone
   GROUP BY
     bo,
     phone
   HAVING
     (Count(pro_cm_seq) MOD 2) = 1;

/gustav


> 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.