Generating a Report based on Missing Data

[MOCaseA]

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"

[NeoPa]

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!

[MOCaseA]

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.

[NeoPa]

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: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:
The employee roster and the Hours DB are already created separately and linked to the form being used.

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.

[MOCaseA]

I am using two separate DBs with a single table in each for the Employee Roster (table is titled Roster) and the Hours log (table is titled Employee Submissions).

For the database Employee Roster the table fields are as follows:

1. First Name
2. Last Name
3. Full Name (Auto-fill from First and Last name fields; format [Last Name, First Name])
4. Team Leader
5. Team Lead

The Team Leader is a Yes/No check box to indicate if they are a team leader. If selected as yes the Full Name field auto-propagates to the Team Lead box (list box) which is used to indicate a specific employees team leader.

For the Hours database the available table fields are as follows:

1. Direct Hours
2. Indirect Hours
3. Date
4. Other Hours
5. Other Details

Direct Hours are hours that are used to directly support a contract. The indirect hours are hours used in support of the contract, but cannot be tied directly to a specific contract (like building this tool). The other hours field is for all hours that cannot be tied to a contract and are not in support of any contracts (such as paid lunches, down times and special projects for this company only). The other details field is a text box for entering info about what the employee was doing during the other hours. The date field is self-explanatory, and auto-fills with the current date (can be edited as needed for entering data on a different date).

I have built a switchboard (titled Employee Data Matrix) for switching to forms, linked with each DBs table, for entering and editing information in these and other tables. In this switchboard I also have a reports area for producing reports based on the data entered in the tables.

What I am specifically looking for is a filtered report that lists employees who do not have an entry on the table for a specific date. The tables are linked using the standard Access 2007 feature (Linked Table Manager).

FYI: Both tables also have ID fields which are the primary keys, but not used for anything other than keeping data separate. I am using the Full Name field to standardize data entry, as we also use many templates in other Office products (Word, PowerPoint and Outlook mostly) and the Full Name field is compatible with the mail-merge abilities on those templates. For example, the Full Name field is used in a signature block on a Word document used for interoffice memos, as well as an Outlook email template used for making final recommendations about a product. I'll build a second set of the tables and attach them to a later post for your perviewal...

[MOCaseA]

Attached is a zip file with the neccesary forms and tables.

[NeoPa]

I've been away since Friday of the week before last, so I've only just caught this. I haven't gone through it yet in any detail as I will need to download the db at home (I don't do that at work ever), but from what I've seen so far it seems to be a lot clearer and I'd be surprised if it doesn't have all the required information for further progress. As I say though, I'll need to look at it more carefully this evening.

[NeoPa]

What you've posted of the [Employee Submissions] table doesn't support connecting this data to any employees. Is this a problem with the design or just some detail you've omitted?

If the former then this is a serious design flaw. If the latter, then it would be helpful to rectify this before I get to look at this in detail this evening.

Otherwise, you've done a pretty decent job of explaining the situation, even as far as explaining why the use of a full name as an ID (which is not generally recommended for various good reasons, but which is adequately explained for your scenario).

[NeoPa]

I'm not sure why the database would be necessary, but if it turns out to be, then I use Access 2003 and have no access to 2007 (pardon the pun).

[MOCaseA]

I'll resubmit the form in 2003 mode... 2007 is the standard for the system I am using. The Full Name field is the only field that the [Employee Submissions] touches for generating data. I have the two connected through Access' generic table linking. Again, posting the 2003 version for your perview.

[NeoPa]

MOCaseA:
I have the two connected through Access' generic table linking.

I'm not sure I really understand, but maybe I'll get a better understanding when I look at the database tonight. I'll let you know then if I still struggle to understand.

[MOCaseA]

Here is the database in 2K3 format.

[NeoPa]

Sorry I've left this hanging somewhat. I haven't had much time recently to devote to the site (See NeoPa Absence).

I am trying to catch up with all my outstanding threads (I have quite a few so this may take some time).

[MOCaseA]

Well you can disreguard this post. I am no longer employed by the company, and thus, no longer working on this database.

[NeoPa]

I'm sorry to hear that. As you'll see from the link I'm in a very similar position myself just now. I hope all goes well with you and you manage to find your new way forward soon.