CODEWITHSUNDEEP
sqlms-access-2013
farmpapa
farmpapa

Reputation: 31

Update column with autonumber

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.

Upvotes: 0

Views: 161

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

Related Questions