MS Access 2016 - Compare dates within same table

I have two tables, users and jobs. User ID and job ID are both autonumber and primary keys for their tables.

User table:

UID | Uname | Uphone | etc...
1   | Billy | 911    |
2   | Alan  | 119    | 
3   | Maria | 191    |

Jobs Table:

JID | UID   | Jtitle  | Date   | etc...
1   | 1     | PenTest | 12Aug
2   | 1     | Consult | 15Nov
3   | 2     | VulScan | 05Sep
4   | 2     | PenTest | 15Sep
5   | 1     | PenTest | 30Dec
6   | 2     | PenTest | 13Oct
7   | 3     | VulScan | 10Nov

What I need to do is take the jobs for each UID and measure the number of days between the two dates most recent and next occurring in the future. For example today is 23 October so I would need to know the number of days between 12Aug and 15Nov for Billy (UID 1). Additionally, if a job is not scheduled in the future then I'd ideally like the "future" cell to be empty for Alan (UID 2).

In my head it would look something similar to the below table.

UID  | Past  | Future | Difference  |
1    | 12Aug | 15Nov  |  95
2    | 13Oct |        |  
3    |       | 10Nov  |

Almost all users will have at least one job in the past, but no guarantee of jobs in the future. It's possible that a user would be created and their job date hasn't come yet, as with Maria (UID 3), but that would be a limited scenario (a user would be less than thirty days from hire to job most likely).

I've been trying to figure this out a couple of days now and gotten nowhere. Maybe my code was incorrect, but I couldn't get two left joins to work out and then there's the issue of calculating the difference.

Solution

Here is one solution (for sqlserver, msaccess sql is further down) -- what should happen when date equals today?

-- for testing, declare table and insert some date into it
declare @Jobs table (JID int, UID int, Jtitle varchar(50), aDate datetime);
insert into @Jobs values (1, 1, 'PenTest', '12Aug2019');
insert into @Jobs values (2, 1, 'Consult', '15Nov2019');
insert into @Jobs values (3, 2, 'VulScan', '05Sep2019');
insert into @Jobs values (4, 2, 'PenTest', '15Sep2019');
insert into @Jobs values (5, 1, 'PenTest', '30Dec2019');
insert into @Jobs values (6, 2, 'PenTest', '13Oct2019');
insert into @Jobs values (7, 3, 'VulScan', '10Nov2019');

-- FULL Join the max(Past) and the min(Future) and convert the dates.
Select 
     COALESCE(a.UID, b.UID) as UID, 
     Left(Convert(varchar, Past, 106), 6) as Past,
     LEFT(Convert(varchar, Future, 106), 6) as Future,
     DATEDIFF(day, Past, Future) as Difference  
FROM
    (Select UID, Max(aDate) as Past from @Jobs where aDate <= GETDATE() Group By UID)a
FULL JOIN
    (Select UID, Min(aDate) as Future from @Jobs where aDate > GETDATE() Group By UID) b
ON a.UID = B.UID

Results are --

UID Past    Future  Difference
1   12 Aug  15 Nov  95
2   13 Oct  NULL    NULL
3   NULL    10 Nov  NULL

SQL for MSACCESS uses UNION of Left and Right joins to get same results--

Select 
     NZ(a.UID, b.UID) as UID, 
     format( aPast, "ddMMM") as Past,
     format( aFuture, "ddMMM") as Future,
     DATEDIFF("d", aPast, aFuture) as Difference  
FROM
    (Select UID, Max(aDate) as aPast from Jobs where aDate <= DATE() Group By UID)a
LEFT JOIN
    (Select UID, Min(aDate) as aFuture from Jobs where aDate > DATE() Group By UID) b
ON a.UID = B.UID

UNION

Select 
     NZ(a.UID, b.UID) as UID, 
     format( aPast, "ddMMM") as Past,
     format( aFuture, "ddMMM") as Future,
     DATEDIFF("d", aPast, aFuture) as Difference  
FROM
    (Select UID, Max(aDate) as aPast from Jobs where aDate <= DATE() Group By UID)a
RIGHT JOIN
    (Select UID, Min(aDate) as aFuture from Jobs where aDate > DATE() Group By UID) b
ON a.UID = B.UID

To get the Uname also, just wrap the above as a subselect and join it with User. I had to convert to Integer (Cint) the UID-s to make it work.

SELECT  u.Uname, z.*  

FROM (
Select 
     NZ(a.UID, b.UID) as UID, 
     format( aPast, "ddMMM") as Past,
     format( aFuture, "ddMMM") as Future,
     DATEDIFF("d", aPast, aFuture) as Difference  
FROM
    (Select UID, Max(aDate) as aPast from Jobs where aDate <= DATE() Group By UID)a
LEFT JOIN
    (Select UID, Min(aDate) as aFuture from Jobs where aDate > DATE() Group By UID) b
ON a.UID = B.UID

UNION

Select 
     NZ(a.UID, b.UID) as UID, 
     format( aPast, "ddMMM") as Past,
     format( aFuture, "ddMMM") as Future,
     DATEDIFF("d", aPast, aFuture) as Difference  
FROM
    (Select UID, Max(aDate) as aPast from Jobs where aDate <= DATE() Group By UID)a
RIGHT JOIN
    (Select UID, Min(aDate) as aFuture from Jobs where aDate > DATE() Group By UID) b
ON a.UID = B.UID
)  as z
Left Join [User] as u
On cint(z.UID) = cint(u.UID)

I live between Oakland and Berkeley, and we have many local brew pubs.