在查询中查找缺少的日期 [英] Find Missing Dates in Query

问题描述

我有一个Access数据库，我每天上传一个数据提取。我想创建一个查询，它将为我提供一个无法找到数据的日期列表。例如。周末没有创建记录，因此周末日期将在此查询生成的列表中。


通过创建这样的查询，我想确保我的数据的完整性，不时地在这个查询中汲取信息会让我知道在这样的日期我可能已经忘记了上传新数据。


表称为声明SharePoint数据


日期字段为EntryDate。数据丢失的日期不在表格中，例如表中没有周末日期。周末日期必须在查询结果中出现。


我有点新的访问权限，所以我很难过。懒散在帮助中，找不到那样的东西...


谢谢，


Lena

I have an Access database that I upload a data extract intoto daily. I want to create a query that will give me a list of dates that no data can be found for. E.g. there are no records created on the weekends, hence weekend dates will be in the list generated by this query.

By creating such a query I want to ensure integrity of my data, e.i.looking at this query from time to time will give me an idea that on such and such date I might have forgotten to upload new data.

Table is called Claims SharePoint Data

The date field is EntryDate. The dates that data is missing will not be in the table, e.g. there are no weekend dates in the table. The weekend dates have to turn up in the query results.

I am kind of new with access, so I am stumped. Looed in Help and could not find anything like that...

Thanks,

Lena

推荐答案

要使用查询实现此目的，您需要创建一个表格，其中包含您想要匹配的范围（句点）中的所有日期。

拥有这样的表格将允许您要创建一个查询'用原始表连接tblDates并使其成为外连接（LEFT或RIGHT），可以检测到缺少的日期。


另一个解决方案可能是从VBA代码处理行并检测丢失的行。


您更喜欢什么？


Nic; o）

To achieve this using a query you''ll need to create a table with all dates in the range (period) you want to match.
Having such a table will allow you to create a query that''s JOINing the original table with the tblDates and by making it an outer join (LEFT or RIGHT), the missing dates can be detected.

Another solution could be to process the rows from VBA code and detect the missing ones.

What do you prefer ?

Nic;o)

我有一个Access数据库，我每天上传一个数据提取。我想创建一个查询，它将为我提供一个无法找到数据的日期列表。例如。周末没有创建记录，因此周末日期将在此查询生成的列表中。


通过创建这样的查询，我想确保我的数据的完整性，不时地在这个查询中汲取信息会让我知道在这样的日期我可能已经忘记了上传新数据。


表称为声明SharePoint数据


日期字段为EntryDate。数据丢失的日期不在表格中，例如表中没有周末日期。周末日期必须在查询结果中出现。


我有点新的访问权限，所以我很难过。在帮助中徘徊，找不到那样的东西...


谢谢，


Lena

I have an Access database that I upload a data extract intoto daily. I want to create a query that will give me a list of dates that no data can be found for. E.g. there are no records created on the weekends, hence weekend dates will be in the list generated by this query.

By creating such a query I want to ensure integrity of my data, e.i.looking at this query from time to time will give me an idea that on such and such date I might have forgotten to upload new data.

Table is called Claims SharePoint Data

The date field is EntryDate. The dates that data is missing will not be in the table, e.g. there are no weekend dates in the table. The weekend dates have to turn up in the query results.

I am kind of new with access, so I am stumped. Looed in Help and could not find anything like that...

Thanks,

Lena

Nico是对的。除非您有搜索的示例，否则无法在数据库中搜索不存在的内容。在这种情况下，临时表为您提供了一个值来搜索：

Nico is correct. You cannot search a database for what is not there unless you have an example of what you are searching for. In this case the temp table gives you a value to search for:

展开 | 选择 | Wrap | 行号

如果在temp中创建日期范围table（本例中的[MyDateTable]）匹配所需的日期范围，然后你不需要WHERE子句。

If you create the date range in the temp table ([MyDateTable] in this example) to match the date range required then you won''t need the WHERE clause.

展开 | < span class =codeLinkonclick =selectAll（this）;>选择 | Wrap | 行号

这篇关于在查询中查找缺少的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！