[AccessD] Query giving different results each time

Gustav,

This doen't give you the Phone Number so a second query is needed.

I have however found a solution in one query...

SELECT tblTest.bo_id,
       tblTest.Primary,
       Last(tblTest.pro_cm_seq) AS SEQ,
       Last(tblTest.Phone) AS PHONE_NUMBER,
       Last(tblTest.effdt) AS EFFECTIVE_DATE
FROM tblTest
GROUP BY tblTest.bo_id, tblTest.Primary
HAVING (((tblTest.Primary)=True))
ORDER BY tblTest.bo_id,
         Last(tblTest.effdt) DESC;

-----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:13 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query giving different results each time


Hi Mark

I vote for qry1.
It is probably the fastest and it could even be reduced to:

SELECT tblTest.bo_id,
       tblTest.pro_cm_seq,
FROM tblTest
GROUP BY tblTest.bo_id,
         tblTest.pro_cm_seq
HAVING (((Count(tblTest.bo_id))=1));

/gustav


> I have two queries that working together may give you the result you want. Just run qry2. They work as predicted on my machine. It is an alternate method that assumes matching pairs for outdated
> records.

> Here they are...

> qry1:
> SELECT tblTest.bo_id,
>        tblTest.pro_cm_seq,
>        Count(tblTest.bo_id) AS CountOfbo_id
> FROM tblTest
> GROUP BY tblTest.bo_id,
>          tblTest.pro_cm_seq
> HAVING (((Count(tblTest.bo_id))=1));

> qry2:
> SELECT tblTest.bo_id,
>        tblTest.pro_cm_seq,
>        tblTest.Primary,
>        tblTest.effdt,
>        tblTest.Phone
> FROM tblTest INNER JOIN qry1 ON tblTest.pro_cm_seq = qry1.pro_cm_seq;

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark A Matte
> Sent: Wednesday, July 28, 2004 10:35 AM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Query giving different results each time


> I'll do my best...

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

> If I could not get the right number of rows in my output...I would just keep 
> tweaking the sql...my concern is why the same sql keeps giving me different 
> results.

> Thanks,

> Mark

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