访问查询以查找谁没有在一定日期范围内进行测试 [英] Access query to find who didn't test over a range of dates

问题描述

我希望有人可以帮助我解决Access中可能无法进行的查询.

I am hoping someone can help me out with a query that may not be possible in Access.

我有一张包含所有员工的表格和一个查询，该查询显示了经过18个月左右的测试并且成长的每个人.
我试图弄清楚如何在访问中进行查询，该查询将显示哪些员工未在日期范围内的各个日期进行测试.例如，如果我创建一个查询日期为10/1/17 – 10/14/17的查询，那么我希望得到的结果是吉姆没有在10/1和10/5上进行测试，而乔没有对10/1和10/5进行测试. 10/12.

I have a table with all employees and a query that shows everyone that has tested over an 18 month or so period and growing.
I am trying to figure out how I can do a query in access that will show what employees have not tested on individual dates within a range of dates. For example, if I create a query of the dates 10/1/17 – 10/14/17 then the result I am hoping for is that Jim did not test on 10/1 and 10/5 and Joe didn't test on 10/12.

它对于单个日期非常有用，因为它仅将单个日期查询的结果与employee表进行比较，并且两个都不在其中的人都不会进行测试，而只会对该日期进行测试.

It works great for a single date as it compares only the results from a single date query against the employee table and whoever is not in both did not test, but only for that date.

这是单日未测试"查询的SQL视图:

This is the SQL view of the single day "did not test" query:

SELECT ESD_EMP_BADGE.LAST_NAME, ESD_EMP_BADGE.FIRST_NAME, ESD_EMP_BADGE.EMPID, ESD_EMP_BADGE.DEPT
FROM ESD_EMP_BADGE LEFT JOIN Yesterday ON ESD_EMP_BADGE.[EMPID] = Yesterday.[EMPID] 
WHERE (((ESD_EMP_BADGE.DEPT) Not Like "EXE") AND ((Yesterday.EMPID) Is Null));  

对于一个范围，我无法采用与单一日期查询相同的方式进行处理，因为如果该人在查询范围内的某个时间进行了测试，则该范围内的单个测试会使该人看起来好像在所有日期中都经过了测试.
有没有一种方法可以在单个查询中单独考虑每个日期?
在employee表中以及在测试记录"EMPID"中只有一个字段被保证是唯一的.员工表中没有日期字段.
我希望这足以说明问题.我想我可能会超出Access的能力范围.

For a range, I cannot do it the same way as the single date query because if that person tested at some time during the range of the query a single test in that range will make it seem like that person tested for all dates.
Is there a way that each date can be considered separately within a single query?
There is only one field that is guaranteed to be unique in the employee table that is also in the test records "EMPID". There is no date field in the employee table.
I hope this is clear enough of an explanation. I think I might be reaching beyond what Access can do.

推荐答案

如果我正确理解了您要寻找的内容，那么您首先需要一个包含所有可能的员工和测试日期组合的数据集.该数据集可以通过包含雇员和不带连接子句的测试表的查询来生成.这是记录的笛卡尔关系-每个表中的每个记录都与另一个表中的每个记录联接.
SELECT Employees.EmpID, EmpTests.TestDate FROM Employees, EmpTests;

If I understand correctly what you are looking for - you will first need a dataset of all possible combinations of employees and test dates. This dataset can be generated with a query that includes employees and tests taken tables without a join clause. This is a Cartesian relationship of records - every record in each table joins with each record in the other table.
SELECT Employees.EmpID, EmpTests.TestDate FROM Employees, EmpTests;

然后将查询联接回到测试表.

Then join that query back to the testing table.

SELECT Query1.EmpID, Query1.TestDate, EmpTests.TestDate
FROM EmpTests RIGHT JOIN Query1 ON (EmpTests.TestDate = Query1.TestDate) AND (EmpTests.EmpID = Query1.EmpID)
WHERE (((Query1.TestDate) Between [start date] And [end date]) AND ((EmpTests.TestDate) Is Null));

这篇关于访问查询以查找谁没有在一定日期范围内进行测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！