Martin F's questions

我有一个Engagement包含四个字段的 Access 表：

Emp_id, Year, Week, Act_id

它记录员工何时/正在从事某项活动。每个字段都是数字，每个字段都构成复合主键的一部分。该应用程序的语义是每个条目（每个Emp_id- Year- Week-Act_id组合）必须是唯一的。也就是说，虽然员工通常会有不同的年周和不同的活动，但员工有时可以在不同的年周从事相同的活动，甚至在同一年的周内从事不同的活动。不允许使用空值。一切正常。

现在我需要扩展/修改语义，以允许与任何给定员工活动相关联的未知（或者更恰当地说，未公开）年-周值。当然，尝试输入带有空 Year-Week 的行会导致“索引或主键不能包含 Null 值”。所以我需要改变桌子的设计。

我尝试的一件事是通过在“索引”窗口中关闭主键（并保持唯一键打开）将主键索引转换为非主索引。这可以正确防止 Year-Week 值为非空的重复记录 - 但它允许 Year-Week 为空的重复记录。

例如，使用上述非主唯一索引，允许以下数据：

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Same activity,
     7  2015    22      31  } different dates.
     7  2015    33      32
     7  2015    40      33  } Same dates,
     7  2015    40      34  } different activities.
     7  2016     2      36
     7                  38  } Different activities,
     7                  39  } undisclosed dates.

随后不允许进行以下任何添加：

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Both records are 
     7  2015    33      32  } duplicates of above.

到目前为止一切顺利（行为符合要求）。但是，随后允许但不应该允许以下两种添加：

Emp_id  Year  Week  Act_id
     7                  38  } Both records are
     7                  39  } duplicates of above.

这是为什么？

有什么好的方法可以解决这个问题，一方面允许 Year 和 Week 等效于 null ，另一方面限制每个Emp_id- Year- Week-Act_id组合是唯一的？

我可以想到另外两个（未尝试过的）解决方案：

1. 选择一个等效于空的数值，例如零或 -1，并以某种方式向用户解释这一点。
2. 将 Year-Week 字段类型从 Number 转换为 Text 并简单地使用 ""（空字符串）作为 null。

根据您的经验，在这种情况下有什么好的解决方案？

我知道复合主键中的可为空列有什么问题？和多列主键中的 NULL 值，它解释了某些事情，但不提供解决方案。

假设我有两个表要外连接

Tab1:
ID  Nam
1   Joe
2   Moe
3   Flo

Tab2:
ID  Val     y   w
1   stuff   66  33
2   duff    67  44

并且其中一个结果列是派生的——即，它是另外两个列的函数

SELECT Tab1.*, Tab2.Val, Tab2.y, Tab2.w, week(Tab2.y,Tab2.w) AS week
FROM Tab1 LEFT JOIN Tab2 ON Tab1.ID = Tab2.ID

正如预期的那样，在没有连接的情况下，空值出现在实际列下，但#Error出现在派生列下：

ID  Nam Val     y   w   week
1   Joe stuff   66  33  66w33
2   Moe duff    67  44  67w44
3   Flo                 #Error

首先，我认为我可以#Error通过有用的处理空字符串函数来防止，Nz()但Nz(week(Tab2.y,Tab2.w))什么也没做。接下来，我认为这是我必须在我的week()函数中处理的事情——即处理案例IsNull(y)——但我发现它甚至不会被“空案例”调用。

如何防止#Error值显示？

我有一个 Access 2010 表Assignment

key Empl    Number
key Year    Number
key Week    Number
key Proj    Number

它在给定的工作周内将员工分配给零个或多个项目，因此，所有四列构成了复合键。我有另一个表，Import具有相同的结构，包含用于Assignment根据不同选项更新的数据。

选项 1 保留所有现有分配，添加全新的导入，并忽略任何已存在的导入。

我可以使用简单的追加查询来管理它：

INSERT INTO Assignment 
SELECT *
FROM Import 

到目前为止，一切都很好。

选项 2 如上所述，除了分配和导入之间存在员工-年-周匹配的情况外，将现有分配替换为导入。

实现这一目标的一种方法是首先删除所有具有员工-年-周匹配项的记录，然后附加导入（如上所述）。结果应该是按原样替换完全匹配，用新项目替换部分（员工-年-周）匹配，并添加全新的分配。

我可以通过这个查询隔离匹配项，称为Existing：

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

如何删除Assignment也在其中的记录Existing？

我试过这个的变化

DELETE 
FROM Assignment
WHERE IN (Existing)

所有导致错误消息。

顺便说一下，如果有这样的动物，我似乎可以使用MINUS(or DIFFERENCE) 查询：

Assignment
MINUS
Existing

该方法的一个小变化是首先删除具有 employee-year-week match和 project mismatch的记录，然后附加导入，如选项 1 所示。（删除和添加较少，但结果相同。）

我可以通过这个查询隔离那些匹配项，称为Existing_exp：

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

但我仍然需要完成如何从中删除记录的Assignment任务Existing_exp！

在SQL 聚合函数 (Office 2007)GROUP BY中，Access SQL中只有这些聚合函数：

Count(), Sum(), Avg(), 
First(), Last(), Min(), Max(), 
StDev(), StDevP(), Var(), VarP()

在PostgreSQL 聚合函数中，PostgreSQL中还有（除其他外）这些聚合函数：

array_agg (expression)
  -- input values, including nulls, concatenated into an array

string_agg (expression, delimiter)
  -- input values concatenated into a string, separated by delimiter

如何GROUP BY在 Access 中获得等效的数组或字符串聚合函数？是否可以构建Access SQL 聚合函数？如果不清楚，如果我这个数据

ID col
-----
1  A
1  B
1  C
2  A
3  A
3  B

如何获得以下聚合？

ID cols
----------
1  A, B, C
2  A
3  A, B

我必须求助于VBA吗？还有其他想法吗？

我使用的是 Access 2007 - 2010，但如果新版本有所不同，请告诉我。

如何将 Access 2007 数据库的整个设计或规范（而非数据内容）另存为文本？

我遇到了“输入参数值”错误，即查询弹出窗口显示了过时的查询名称——尽管已打开名称自动更正选项并运行了压缩和修复数据库。

我读到名称自动更正有问题。我还读到可以将对象另存为文本（在早期版本的 Access 中），并且认为这将是一种在任何地方更改名称的有价值的方法——查询和表单以及表。

_{请不要试图说服我不要更改名称；这只会让我更加坚定地这样做 ;-)}

我在 MS Excel 中有一个我称之为稀疏“交互矩阵”的数据，我希望将其转换为 Access 中的“三元关系”（表格）。

电子表格数据采用这种模式：

Name   Act-1  Act-2  Act-3  ... Act-n
name-1         week
name-2                week
name-3  week                     week
  :                         week
name-m         week   week

有n作为 Excel 标题的活动，m每个数据行第一列中的名称，以及在某人（姓名）进行活动的任何地方输入的零个或多个特定周。如果n是小的和固定的，我可以将关系保持为矩阵，但n可以变得非常大并且矩阵将非常稀疏。

因此，所需的关系表结构（我称之为分配）是这样的：

Assignment: Name, Activity, Week

Excel 或 Access 中是否有一个简单的工具来促进这种数据转换？

伪代码算法将是

for each Name (row)
    for each Activity (column)
        if Week, add Assignment: Name, Activity, Week

虽然我在 VBA 中编写代码（几年前），但我从未将它与 Excel 结合使用。我倾向于使用 Python 和 CSV 文件。也许有一个很好的 SQL 技术（即使不是特定于 Access 的）？或者它与“旋转”有关（我从来不明白那是什么）？

对于这种转换，您有什么想要分享的经验吗？