如何避免加入 MS-ACCESS(房间访问管理)中的重复

Question

这是用于房间访问管理，我从可能外部设备导出了csv文件，这是原始数据：

2019.05.31  20:20:28    p+  462 SALES MGR, ROVING GUARD
2019.05.31  20:23:35    p-  462 SALES MGR, ROVING GUARD
2019.05.31  20:24:05    p+  461 SALES MGR, ROVING GUARD
2019.05.31  20:24:13    p-  461 SALES MGR, ROVING GUARD
2019.05.31  20:24:40    p+  460 SALES MGR, ROVING GUARD
2019.05.31  20:24:51    p-  460 SALES MGR, ROVING GUARD
2019.05.31  20:25:31    p+  447 SALES MGR, ROVING GUARD
2019.05.31  20:25:36    p-  447 SALES MGR, ROVING GUARD
2019.05.31  20:26:14    p+  459 SALES MGR, ROVING GUARD
2019.05.31  20:26:42    p+  458 SALES MGR, ROVING GUARD
2019.05.31  20:26:46    p-  458 SALES MGR, ROVING GUARD
2019.05.31  20:27:49    p+  443 SALES MGR, ROVING GUARD
2019.05.31  20:28:01    p-  443 SALES MGR, ROVING GUARD
2019.05.31  20:28:16    p+  443 SALES MGR, ROVING GUARD
2019.05.31  20:28:21    p-  443 SALES MGR, ROVING GUARD
2019.05.31  20:29:05    p+  445 SALES MGR, ROVING GUARD
2019.05.31  20:29:09    p-  445 SALES MGR, ROVING GUARD

注意：P+代表进入房间，而P-代表出口房间

下载后，我将 csv 文件导入我的应用程序，然后将 P+ 和 P- 分开，最后将其记录到数据库中。这是数据库结构：

数据库结构 P+ 表格 P- 表 结果 如我们所见，与原始数据相比，重复且不正确的记录，请帮助我解决此问题或建议另一种方法。谢谢。

顺便说一句，这是我用来获取结果的查询：

SELECT tblActivityPP.DateLog AS DateIN,
tblActivityPN.DateLog AS DateOUT, 
tblActivityPP.TimeLog AS TimeIN, 
tblActivityPN.TimeLog AS TimeOUT, 
tblActivityPP.Room,  
tblActivityPN.Room AS RoomOut, 
tblActivityPP.Position, 
tblActivityPP.Account, 
tblActivityPN.ID, 
tblActivityPP.ID
FROM tblActivityPP 
LEFT JOIN 
tblActivityPN ON 
(tblActivityPP.Position = tblActivityPN.Position) 
AND 
(tblActivityPP.Room = tblActivityPN.Room) 
AND 
(tblActivityPP.[Account] = tblActivityPN.[Account])

Answer 1

对于不同的结果，您需要 DISTINCT 子句

SELECT DISTINCT 
tblActivityPP.DateLog AS DateIN,
tblActivityPN.DateLog AS DateOUT, 
tblActivityPP.TimeLog AS TimeIN, 
tblActivityPN.TimeLog AS TimeOUT, 
tblActivityPP.Room,  
tblActivityPN.Room AS RoomOut, 
tblActivityPP.Position, 
tblActivityPP.Account, 
tblActivityPN.ID, 
tblActivityPP.ID
FROM tblActivityPP 
LEFT JOIN 
tblActivityPN ON 
(tblActivityPP.Position = tblActivityPN.Position) 
AND 
(tblActivityPP.Room = tblActivityPN.Room) 
AND 
(tblActivityPP.[Account] = tblActivityPN.[Account])

Answer 2

数据是正确的，但443室被输入/离开了三次。

| 2019.05.31 | 20:27:49 | p+ | 443 | SALES MGR | ROVING GUARD |
| 2019.05.31 | 20:28:01 | p- | 443 | SALES MGR | ROVING GUARD |
| 2019.05.31 | 20:28:16 | p+ | 443 | SALES MGR | ROVING GUARD |
| 2019.05.31 | 20:28:21 | p- | 443 | SALES MGR | ROVING GUARD |
...
| 2019.05.31 | 20:30    | p+ | 443 | SALES MGR | ROVING GUARD |  (this data is from image P+)
| 2019.05.31 | 20:30    | p- | 443 | SALES MGR | ROVING GUARD |  (this data is from image P-)

这会导致房间 443 的 9(tblActivityPP 中的 3 行 * tblActivityPN 中的 3 行)行，如果您在房间上加入 tblActivityPP 和 tblActivityPN。正确的结果(tblActivityPP 中的每一行都连接到 tblActivityPN 中的每一行)，但不是您想要的

您想知道进入后何时离开房间。这是输入后的第一个离开，将数据限制为一行。

不要创建 2 个表(具有相同字段名的表通常应该是一个带有附加字段的表，其中存储了表名描述的属性)，只需将原始数据导入一个表(例如ÀctivityLog)(但只有一个日期和时间字段(datetime))，创建一个表Accounts并将 PK(PrimaryKey) 作为 FK(外键)存储在表中， 跳过Position(应存储在Account表中)并为进入/离开房间创建表Activity并存储PK(作为FK)而不是P +/P-。`

例如表 ÀctivityLog：

| Timestamp           | ActivityID | Room | AccountID |
|---------------------|:----------:|:----:|:---------:|
| 2019.05.31 20:20:28 |      1     |  462 |     1     |
| 2019.05.31 20:23:35 |      2     |  462 |     1     |
| 2019.05.31 20:24:05 |      1     |  461 |     1     |
| 2019.05.31 20:24:13 |      2     |  461 |     1     |
| 2019.05.31 20:24:40 |      1     |  460 |     1     |
| 2019.05.31 20:24:51 |      2     |  460 |     1     |
| 2019.05.31 20:25:31 |      1     |  447 |     1     |
| 2019.05.31 20:25:36 |      2     |  447 |     1     |
| 2019.05.31 20:26:14 |      1     |  459 |     1     |
| 2019.05.31 20:26:42 |      1     |  458 |     1     |
| 2019.05.31 20:26:46 |      2     |  458 |     1     |
| 2019.05.31 20:27:49 |      1     |  443 |     1     |
| 2019.05.31 20:28:01 |      2     |  443 |     1     |
| 2019.05.31 20:28:16 |      1     |  443 |     1     |
| 2019.05.31 20:28:21 |      2     |  443 |     1     |
| 2019.05.31 20:29:05 |      1     |  445 |     1     |
| 2019.05.31 20:29:09 |      2     |  445 |     1     |

所有字段都应编制索引。

表Activities：

| ID | ActivityName | ActivityDescription |
|----|:------------:|---------------------|
| 1  |      P+      | Room entered        |
| 2  |      P-      | Room left           |

表Accounts：

| ID | AccountName  | PositionID                                       |
|----|--------------|--------------------------------------------------|
| 1  | ROVING GUARD | 3 (FK for Position SALES MGR in table Positions) |

所有ID字段都是主键和自动增量。

然后通过过滤ActivityIDActivityLog来创建 2 个查询作为tblActivityPP/tblActivityPN的替代品。

qry进入活动：

SELECT ActivityLog.ID
,Format(ActivityLog.Timestamp, "mm/dd/yyyy") AS DateLog
,Format(ActivityLog.Timestamp, "HH:nn") AS TimeLog
,Activites.ActivityName
,ActivityLog.Room
,Positions.PositionName AS Position
,Accounts.AccountName AS Account
FROM ActivitiesLog
INNER JOIN Activities ON ActivityLog.ActivityID = Activities.ID
INNER JOIN Accounts ON ActivityLog.AccountID = Accounts.ID
INNER JOIN Positions ON Accounts.PositionID = Positions.ID
WHERE ActivityLog.ActivityID = 1;

qryLeave活动：

SELECT ActivityLog.ID
,Format(ActivityLog.Timestamp, "mm/dd/yyyy") AS DateLog
,Format(ActivityLog.Timestamp, "HH:nn") AS TimeLog
,Activites.ActivityName
,ActivityLog.Room
,Positions.PositionName AS Position
,Accounts.AccountName AS Account
FROM ActivitiesLog
INNER JOIN Activities ON ActivityLog.ActivityID = Activities.ID
INNER JOIN Accounts ON ActivityLog.AccountID = Accounts.ID
INNER JOIN Positions ON Accounts.PositionID = Positions.ID
WHERE ActivityLog.ActivityID = 2;

您的结果查询：

SELECT ActivityLog.ID
,Format(ActivityLog.Timestamp, "mm/dd/yyyy") AS DateIN
,Format(ActivityLog.Timestamp, "HH:nn") AS TimeIn,
,(SELECT Min(Timestamp) FROM ActivityLog AS ALog WHERE ALog.AccountID = ActivityLog.AccountID AND ALOG.Timestamp > ActivityLog.Timestamp AND ALog.ActivityID = 2) as TimestampOut
,Format(TimestampOut, "mm/dd/yyyy") AS DateOut
,Format(TimestampOut, "HH:nn") AS TimeOut
,Activites.ActivityName
,ActivityLog.Room
,Positions.PositionName AS Position
,Accounts.AccountName AS Account
FROM ActivitiesLog 
INNER JOIN Activities ON ActivityLog.ActivityID = Activities.ID
INNER JOIN Accounts ON ActivityLog.AccountID = Accounts.ID
INNER JOIN Positions ON Accounts.PositionID = Positions.ID
WHERE ActivityLog.ActivityID = 1;

这将获取所有房间输入的事件，并(子)为该帐户选择下一个房间左侧事件。

所有SQL 代码都未经测试，并且缺少括号、别名等，但应显示通用方法。