[AccessD] Query giving different results each time

Hi Mark and Patti

If you need all info, this single query will do, no subquery:

  SELECT
    bo,
    pro_cm_seq,
    Max(effdt) AS lasteffdt,
    phone
  FROM
    tblPhone
  GROUP BY
    bo,
    pro_cm_seq,
    phone
  HAVING
    (Count(pro_cm_seq) Mod 2)=1;

/gustav


> 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


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