[AccessD] Query Help Needed

[John Bodin]

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