[AccessD] Query Help Needed

[Bill Benson]

Beats me what the queryplanner does, it might jettify the whole thing into
the same lump of query code anyway, Yeah, I like options and this was the
first simple case of using Not Exists I have every had the pleasure of
trying out. I almost want to thank you for the challenge  <vbg>

On Mon, Mar 16, 2015 at 1:06 PM, John Bodin <jbodin at sbor.com> wrote:

> That is what supposed to happen, you are right.  Again, I didn't test it
> out very much but did get it to run.  There were dates completely missing.
> The Exists/not Exists statements are something I forgot about and haven't
> used very much apparently.  Good to know of another option to IN().
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Bill Benson
> Sent: Monday, March 16, 2015 12:54 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query Help Needed
>
> I don't understand what could be different between our data sets, but I
> tried out my query on this data set and there was no omitted results so far
> as I can see:
>
> ID CallNo TechID AssignDate
> 1    1            1       02-Jan-15
> 2    1            2       03-Jan-15
> 3    2            1       15-Jan-15
> 4    3            1       20-Jan-15
> 5    4            2       20-Jan-15
> 6    4            1       22-Jan-15
>
>
> Query:
>
> Select CallNo, AssignDate,TechID
>  From Table1 as A
>  Where not exists
>  (Select AssignDate,TechID from
>  Table1 as B where A.CallNo = B.CallNo and A.AssignDate > B.AssignDate)
>
> Result (which I believe to be accurate - if not, let me know)
>
> CallNo AssignDate TechID
> 1           1/2/2015         1
> 2           1/15/2015       1
> 3           1/20/2015       1
> 4           1/20/2015       2
>
> On Sun, Mar 15, 2015 at 9:27 PM, John Bodin <jbodin at sbor.com> wrote:
>
> > Hi Bill,
> >
> > I tried this and although it fires off and returns records, it seems
> > to exclude entire sequences of calls altogether.  I didn't dig too
> > deeply into this as Don's suggestion seems to have worked.  Thanks for
> > the reply and code.
> >
> > John
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> > Of Bill Benson
> > Sent: Sunday, March 15, 2015 7:29 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Query Help Needed
> >
> > I am wondering if something like this might work?
> >
> > Select CallNo, AssignDate,TechID
> > From tblCallsTechs as A
> > Where not exists
> > (Select AssignDate,TechID from
> > tblCallsTechs as B where A.CallNo = B.CallNo and A.AssignDate >
> > B.CallDate)
> > --
> > 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
> >
> --
> 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
>