Scott Marcus
marcus at tsstech.com
Wed Jul 28 10:30:53 CDT 2004
You can shorten it to...
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
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Scott Marcus
Sent: Wednesday, July 28, 2004 11:26 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Query giving different results each time
<<This does not filter the correct records. I only need 1 primary number for
each bo_id...
It sure does work if you run qry2. qry1 is needed for qry2 only and is not intended to be the solution. qry2 is the solution.
Anyway, I have found a query that gives the desired result in one query instead of two and no sub queries are needed...
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 Mark A Matte
Sent: Wednesday, July 28, 2004 11:20 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Query giving different results each time
Scott,
This does not filter the correct records. I only need 1 primary number for
each bo_id...it gave me primary and non primary...and sometimes more than 1
number for the bo_id...I can create multiple tables/queries to get to the
data I need...but I'm still curious as to why/what access is interpretting
different each time it executes this sql.
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 11:05:07 -0400
>
>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
>
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
Don't just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/
--
_______________________________________________
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