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