[AccessD] Query giving different results each time

Scott Marcus marcus at tsstech.com
Tue Jul 27 14:08:40 CDT 2004



Scott Marcus
TSS Technologies, Inc.
marcus at tsstech.com
(513) 772-7000
Mark,

You WHERE clause is probably giving you some trouble because 'tblPRS_Pending_PHONE.bo_id =  A.bo_id' are the same instance therefore giving random values. Try the following....

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"));

 -----Original Message-----
From: 	accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]  On Behalf Of Mark A Matte
Sent:	Tuesday, July 27, 2004 12:11 PM
To:	accessd at databaseadvisors.com
Subject:	RE: [AccessD] Query giving different results each time


Charlotte,

The original post had the SQL...but here is what I am currently using...but 
with the same inconsistant results.  Any ideas would be greatly appreciated.

Thanks,

Mark

SELECT tblPRS_Pending_PHONE.profile_cm_seq, tblPRS_Pending_PHONE.effdt, 
tblPRS_Pending_PHONE.bo_id, tblPRS_Pending_PHONE.primary_ind, 
tblPRS_Pending_PHONE.phone
FROM tblPRS_Pending_PHONE
WHERE (((tblPRS_Pending_PHONE.effdt)=(SELECT Max(A.effdt) AS MaxOfeffdt
FROM tblPRS_Pending_PHONE A
WHERE  tblPRS_Pending_PHONE.bo_id =  A.bo_id and
tblPRS_Pending_PHONE.profile_cm_seq = A.profile_cm_seq;)) AND 
((tblPRS_Pending_PHONE.primary_ind)="y"));


>From: "Charlotte Foust" <cfoust at infostatsystems.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: Tue, 27 Jul 2004 08:56:24 -0700
>
>I missed the part of thread where you mentioned a sub-query.  Have you
>posted the SQL for this query/subquery?
>
>Charlotte Foust
>
>
>-----Original Message-----
>From: Mark A Matte [mailto:markamatte at hotmail.com]
>Sent: Tuesday, July 27, 2004 7:19 AM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Query giving different results each time
>
>
>Yes I tried the compact and repair.  It didn't seem to help.  Each time
>I
>run the query...I get different results...is there something about the
>sub-query I am missing?
>
>Thanks,
>
>Mark A. Matte
>
>
> >From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
> >Reply-To: Access Developers discussion and problem
> >solving<accessd at databaseadvisors.com>
> >To: Access Developers discussion and
> >problemsolving	<accessd at databaseadvisors.com>
> >Subject: RE: [AccessD] Query giving different results each time
> >Date: Tue, 27 Jul 2004 14:01:07 +1000
> >
> >On 27 Jul 2004 at 1:45, Mark A Matte wrote:
> >
> > > Hello All,
> > >
> > > Thanks for the feedback...but I'm still a  little confused...If I am
> >running
> > > the same SQL against the same table...back to back...why am I
> > > getting different results?
> > >
> > >
> >A corrupt index? Have you tried a compact and repair?
> >
> >--
> >Lexacorp Ltd
> >http://www.lexacorp.com.pg
> >Information Technology Consultancy, Software Development,System
> >Support.
> >
> >
> >
> >--
> >_______________________________________________
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>_________________________________________________________________
>Is your PC infected? Get a FREE online computer virus scan from
>McAfee(r)
>Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
>--
>_______________________________________________
>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

_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfee(r) 
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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