[AccessD] Query giving different results each time

O'Connor, Patricia (OTDA) Patricia.O'Connor at dfa.state.ny.us
Wed Jul 28 10:28:45 CDT 2004


Be Careful using LAST it will not always give you what you expect. It
will give you the last one it encounters which may not be the MAX date
!!!

See help files and knowledge base

Patti 

> -----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:23 AM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Query giving different results each time
> 
> Gustav,
> 
> This doen't give you the Phone Number so a second query is needed.
> 
> I have however found a solution in one query...
> 
> 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 Gustav Brock
> Sent: Wednesday, July 28, 2004 11:13 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query giving different results each time
> 
> 
> Hi Mark
> 
> I vote for qry1.
> It is probably the fastest and it could even be reduced to:
> 
> SELECT tblTest.bo_id,
>        tblTest.pro_cm_seq,
> FROM tblTest
> GROUP BY tblTest.bo_id,
>          tblTest.pro_cm_seq
> HAVING (((Count(tblTest.bo_id))=1));
> 
> /gustav
> 
> 
> > 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
> 
> --
> _______________________________________________
> 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
> 
> 



More information about the AccessD mailing list