[AccessD] Query giving different results each time

Scott Marcus marcus at tsstech.com
Wed Jul 28 10:05:07 CDT 2004


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





>From: "Scott Marcus" <marcus at tsstech.com>
>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 09:35:47 -0400
>
>Lets try a different route. Please explain what you have and what result 
>you want.
>
>Scott Marcus
>
>-----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 9:27 AM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Query giving different results each time
>
>
>Scott,
>
>Thanks for the feedback...but the results of this sql are also
>inconsistant...I ran it 3 times...and got 3 different results...the 
>reslults
>are typically close...for the last 3 tries...it returned 2365, 2369 and
>2375...without changing anything...just re-running the sql???
>
>I fell very confused...lol
>
>Any ideas?
>
>Thanks,
>
>Mark
>
>
> >From: "Scott Marcus" <marcus at tsstech.com>
> >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 09:12:00 -0400
> >
> >Mark,
> >
> >Do you try the following to see if it fixes your problem?
> >
> >SELECT B.profile_cm_seq,
> >        B.effdt,
> >        B.bo_id,
> >        B.primary_ind,
> >        B.phone
> >FROM tblPRS_Pending_PHONE B
> >WHERE (B.effdt=(SELECT Max(A.effdt)
> >                 FROM tblPRS_Pending_PHONE A
> >                 WHERE B.bo_id = A.bo_id
> >                       AND
> >                       B.profile_cm_seq = A.profile_cm_seq))
> >       AND
> >       B.primary_ind="y";
> >
> >
> >Scott Marcus
> >
> >--
> >_______________________________________________
> >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
>
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

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





More information about the AccessD mailing list