基于缺失数据生成报告 [英] Generating a Report based on Missing Data

问题描述

我正在处理一小时跟踪日志。用户/员工在一天结束时提交他们的工作时间以确定工资。我正在尝试生成一份报告，以显示哪些员工不在任何一天提交他们的工作时间，我需要通过在文本字段中输入日期生成并填写报告，比较该日期使用所有条目的DB，将这些条目与员工名单（单独的DB）进行比较，并显示那些没有指定日期条目的员工。任何帮助，将不胜感激。员工名单和小时数据库已经单独创建并链接到正在使用的表格。


表格如下：

员工提交（小时日志）

名册


员工提交表中的必要字段是日期

名册表中的必要字段是全名

I am working on an hours tracking log. The users/employees submit their hours at the end of the day to determine pay. I am trying to generate a report to show which employees did not submit their hours on any given day, and I need the report to be generated and filled by entering a date in a text field, comparing that date with a DB of all the entries made, comparing those entries with the employee roster (separate DB), and showing those employees without an entry for the specified date. Any help would be appreciated. The employee roster and the Hours DB are already created separately and linked to the form being used.

Tables are as Follows:
Employee Submissions (hours log)
Roster

Necessary Field in the Employee Submissions Table is "Date"
Necessary Field in the Roster Table is "Full Name"

推荐答案

有趣的问题，并且有一个查询向导可用于查找缺少的条目。现在虽然我会指出这样一个查询所需的SQL。


基本上，为了从表B中找到缺少的项，表A中存在，你使用LEFT JOIN链接从表A到表B的链接字段（无论它们是什么）然后包含在WHERE子句中，检查表B中的（一个）链接字段是否为空。在进行外连接时（LEFT JOIN是其中之一），在外侧找不到的任何记录在其所有显示的字段中都由Null表示。显然，您检查的字段应该是在正常事件过程中不允许包含Null的字段。有关联接的更多信息，请参阅 SQL JOIN 。

在您的情况下，表格A将是[名单]和表格B [员工提交]。


顺便说一句，我假设当您引用单独的数据库时，您的意思是在任何地方都不提供数据库信息的单独表。我还假设[员工提交]是您在解释中称为 The Hours DB 的内容。


欢迎来到< b>字节！

Interesting question, and there is a query wizard available to find missing entries. For now though I''ll indicate the SQL required for such a query.

Essentially, to find missing items from table B, that exist in table A, you use a LEFT JOIN linking from Table A to Table B on the linking fields (whatever they are) then include in your WHERE clause, a check for (one of) the linking field(s) of Table B being Null. When doing an outer join (LEFT JOIN is one of those) any record that isn''t found on the outer side is represented by Nulls in all its displayed fields. Obviously, the field you check should be one that isn''t allowed to contain Nulls in the normal course of events. For more on joins see SQL JOINs.

In your case table A would be [Roster] and table B [Employee Submissions].

By the way, I assume when you refer to separate databases you really mean separate tables as you give no database information anywhere. I''m also assuming that [Employee Submissions] is what you refer to in the explanation as "The Hours DB".

Welcome to Bytes!

我认为我的帖子相当清楚，但我想我遗漏了一些信息。每个表都在它自己的数据库中。我正在使用Access 2007，但无法访问SQL或Sharepoint。我正在构建这个以在任何地方运行的方式运行系统，因此多个用户可以访问主数据库而无需实际打开它们（从表单中推送的信息会回到集中托管的数据库文件）。实际上，该系统涉及四个数据库，主要用于数据保护目的。直接受此影响的仅有两个是员工数据库（名册）和小时日志数据库（员工提交）。每个数据库实际上托管了几个通过各种表面和命令填充的表，但主表是列出的。

I thought I was fairly clear in my post, but I guess I left some information out. Each table is in it''s own database. I''m using Access 2007, but have no access to SQL or Sharepoint. I''m building this to run as a "run anywhere" system, so multiple users can be accessing the primary databases without actually opening them themselves (the information pushed from the forms talk back to a centrally hosted database file). There are actually four databases involved in this system, mostly for data protection purposes. The only two that are directly being effected by this are the employee database (the Roster) and the hours log database (Employee Submissions). Each database is actually hosting several tables that are populated through various intrefaces and commands, but the primary tables are as listed.

确实你在OP中提到了多个数据库，但是您还将数据库称为文本中的表格。

It''s true you mentioned multiple databases in your OP, but then you also referred to the databases as if they were tables in the text.

MOCaseA：将该日期与所有数据库进行比较参赛作品

MOCaseA:comparing that date with a DB of all the entries made

除非假设你真的是桌子，否则没有意义。无论如何，现在已经澄清了。


继续前进。您还要说：

Doesn''t make sense unless one assumes you really mean table. Anyway, that''s clarified now.

Moving on. You also say :

MOCaseA：

员工名单和小时数据库已经单独创建链接到正在使用的表单。

MOCaseA:
The employee roster and the Hours DB are already created separately and linked to the form being used.

这当然很重要，但它并没有说明它们是如何联系在一起的。我在第2篇文章中解释了查询中所需的设置，以指示缺少的链接项。


就链接表而言，可以处理它们在您的数据库中几乎就像它们是本地的一样。它们位于不同的数据库中的事实不会以任何我能看到的方式影响这个问题。


您在链接到表格中给出了几个用于链接表格。如果[全名]是其中之一，那么你真的应该密切关注你的设计。有101个理由说明为什么链接文本字段可能会有问题，而[全名]是许多陷阱常用的例子。这绝不是，永远，推荐。


坦率地说，如果您需要更详细的帮助，您必须提供更详细的信息。

This is important of course, but it doesn''t give any indication of how they are linked. I explained in post #2 the sort of setup that''s required in a query to indicate linked items that are missing.

As far as linked tables are concerned they can be dealt with in your database almost exactly as if they were local ones. The fact that they are in separate databases will not effect this issue in any way that I can see.

You give a couple of fields in the linked to tables that are to be used to link the tables. If [Full Name] is one of them then you really ought to be looking closely at your design. There are 101 reasons why linking on a text field can be problematical and [Full Names] are an often used example of many of the pitfalls. This is never, ever, recommended.

Frankly, if you need more detailed help with this you will have to provide more detailed information.

这篇关于基于缺失数据生成报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！