用自动编号更新列 [英] Update column with autonumber

问题描述

我需要弄清楚每个人何时将基于不包含连续日期的工作日历完成任务.我知道两个表T1中的数据

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

和T2

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

现在，如果Joe剩余3天，我想在T2中从今天算起3条记录，并将日期返回到Complete(完成)列.如果今天是6/1/2018，我希望Update查询返回6/10/2018到Joe的Complete列.

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.

我的想法是，我可以每天从今天开始的查询中更新T2.count，然后自动进行递增.之后，我可以在DaysRemaining和Count上加入T1和T2.我可以这样做，但是还没有找到使用自动增量更新t2.count的有效解决方案.还有更好的主意吗?我正在使用链接的共享点表，因此每次创建一个新字段都将是一种选择.

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.

推荐答案

我认为这会起作用:

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.

这篇关于用自动编号更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！