[AccessD] Query giving different results each time

Have you tried using sub-selects...?
mark

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: 28 July 2004 17:17
To: accessd at databaseadvisors.com
Subject: Re: [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/

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004