0

I need to figure out when each person will complete a task based on a work calendar that won't include sequential dates. I know the data in two tables T1

Name DaysRemaining  Complete
Joe      3
Mary     2

and T2

Date        Count
6/1/2018
6/8/2018
6/10/2018
6/15/2018

Now if Joe has 3 days remaining I would like to count 3 records forward from today in T2 and return the date to the Complete column. If today is 6/1/2018 I would want the Update query to return 6/10/2018 to the Complete column for Joe.

My thought is that I could daily update T2.count with a query that began today and would then autoincrement. Following that I could join the T1 and T2 on DaysRemaining and Count. I can do that but haven't found a working solution for updating t2.count with autoincrement. Any better ideas? I am using a linked sharepoint table so creating a new field each time would not be an option.

farmpapa
  • 31
  • 1
  • 8

1 Answers1

1

I think this will work:

select t1.*, t2.date
from t1, t2  -- ms access doesn't support cross join
where t1.daysremaining = (select count(*)
                          from t2 as tt2
                          where tt2.date <= t2.date and tt2.date > now()
                         );

This is an expensive query and one that is easier to express and more efficient in almost any other database.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I missed the part about the cross join. Yeh that would make sense and I guess I don't understand why it is not enabled in access, but that is the system I have to work with...so I guess back to trying my original autonumber solution right? – farmpapa May 08 '18 at 19:07
  • curious if I could make a Cartesian product work [link](https://stackoverflow.com/questions/1231676/how-to-use-cross-join-in-access) – farmpapa May 08 '18 at 21:18
  • @farmpapa . . . I don't understand your comments at all. The query should work in MS Access. – Gordon Linoff May 09 '18 at 02:36