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