记录集不可更新(来自两个表的查询) [英] Recordset is not updatable (query from two tables)

问题描述

我将Microsoft Access 2013与Sharepoint列表一起使用，并且我有两个表:

I am using Microsoft Access 2013 with Sharepoint lists and I have two tables:

• 学生:身份证，全名，手机，开始日期(他们在我中心开始学习的日期)以及一些无关的字段

• students: ID, Full name, Mobile, Start_Date (the date that they start studying at my centre), and some irrelevant fields

=>许多学生的开学日期不同

=> there are many students with different starting dates

• [周休假] :ID，原因，From_Date(中心暂时关闭的日期)，[周数](中心从该From_Date临时关闭的周数) )

• [Weeks Off]: ID, Reason, From_Date (the date that the centre is temporarily closed), [Number of Weeks] (the number of weeks that the centre is temporariliy closed from that From_Date)

=>在每个学生的学习时间里，他们可能有一些假"，这没有算在他们学习的总周数中.

=> in each student' study time, they may have some "weeks off" that are not counted in the total number of weeks that they have studied for.

我正在创建一个查询，以计算学生从他们的Start_Date开始学习的星期数.

I am creating a query to calculate the number of weeks that students have studied from their Start_Date.

SELECT

students.ID,
students.[Full name],
students.Mobile,
students.Start_Date,
Round((Date()-students.[Start_Date])/7,0) - 
( SELECT SUM(
    IIF( [Weeks Off].[From Date]> students.[Start_Date] and [Weeks Off].[From Date]<Date(), 
         [Weeks Off].[Number of Weeks], 0 )
   ) 
  FROM [Weeks Off]
) AS [Studied Weeks],

FROM students;

现在的问题是，即使查询成功地在所有学生的列中显示了学习周数"，记录集还是无法更新的.

The problem now is that even though the query successfully displays all students with a column showing their "Studied Weeks", the Recordset is not updatable.

如何再次使其可更新?

摘自评论:

我将其更改为:

(Round( ( Date()- students.Start_Date)/7,0) - 
 DSum("[Number of Weeks]", "[Weeks Off]", 
      "[From Date]>= students.Start_Date And [From Date]<= Date()") 
) AS [Studied Weeks] 

但是它说:Microsoft找不到名称"students.Start_Date"(您在表达式中输入的名称).所以我仍然停留在这里.

But it says: Microsoft cannot find the name students.Start_Date you entered in the expression. So I'm still stuck here.

推荐答案

请参阅:艾伦·布朗(Allen Browne):为什么读取我的查询-仅?

来自后者:

它在SELECT子句中使用First()，Sum()，Max()，Count()等.汇总记录的查询是只读的.

It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.

如果将计算结果放入一个单独的查询中并将其(在Student.ID上)连接到学生"表，则可能会起作用.

It might work if you put the calculation into a separate query and join that (on Student.ID) to the Students table.

如果将SUM计算转换为DSum()表达式，它将起作用(但可能会更慢).然后只有该列将是只读的.

It will work (but may be slower) if you convert the SUM calculation into a DSum() expression. Then only that column will be read-only.

修改

students.Start_Date是您的DSum调用中的变量，因此它必须在条件中的常量字符串之外.

students.Start_Date is a variable in your DSum call, so it must be outside the constant string in the criteria.

使用Gustav的 CSql()函数格式化日期并将其与其余日期连接.

Use Gustav's CSql() function to format the date and concatenate it with the rest.

(Round( ( Date()- students.Start_Date)/7,0) - 
 DSum("[Number of Weeks]", "[Weeks Off]", 
      "[From Date]>=" & CSql(students.Start_Date) & " And [From Date]<= Date()") 
) AS [Studied Weeks] 

应该这样做.

这篇关于记录集不可更新(来自两个表的查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！