How to Use SQL in MS Access to List All Employee Assessments, Even When Not Submitted

Описание: Discover an effective SQL query approach to display complete employee assessment data from MS Access, including those who did not submit assessments for a date.
---
This video is based on the question https://stackoverflow.com/q/69199143/ asked by the user 'DG_Riverhawk' ( https://stackoverflow.com/u/16919846/ ) and on the answer https://stackoverflow.com/a/69200791/ provided by the user 'June7' ( https://stackoverflow.com/u/7607190/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Access SQL query to compare two tables and list all data, not just those that dont match

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Use SQL in MS Access to List All Employee Assessments, Even When Not Submitted

In the realm of database management and data collection, it's vital to track not only what has been submitted but also what remains outstanding. This is particularly important in workplaces where consistent employee self-assessments can drive more informed coaching and development. If you're working with two separate tables in Microsoft Access – one for employee information and another for assessments – you may find yourself at a crossroads: how can you efficiently query these tables to ensure you have a complete list of all employees, including those who didn't submit their assessments on a specific date? In this post, we’ll explore how to achieve this using SQL queries in MS Access.

The Problem

You have two tables in your database:

Employee Info: This table contains essential information about employees and includes fields such as:

Employee Name

Employee Number

Assessment: This holds the self-assessment data submitted through a form, with fields like:

Employee Name

Employee Number

Self Assessed (with a yes/no checkbox)

Date Signed

The challenge arises when trying to generate a query that retrieves a complete list of employees, showing when they signed their assessments, even if some employees did not submit assessments for that day. Your initial SQL query was only returning those who had submitted their assessments, not the full list of employees.

The Solution

To tackle this, we will use SQL queries with a combination of LEFT JOIN to include all employees, regardless of whether they submitted their assessments on a specified date. Here’s a breakdown of how this can be accomplished.

Step 1: Use a LEFT JOIN

The first step is to join the two tables using a LEFT JOIN. This ensures that all records from the Employee Info table are shown, even if there's no corresponding record in the Assessment table.

Here's a revised SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Define the Date Range

In this query, [Start Date] and [End Date] are placeholders for the date range you want to filter by. These can easily be replaced with input fields in your form, allowing users to specify the date range dynamically.

Step 3: Displaying Non-Submissions

To ensure you can also capture the cases where employees did not submit assessments, you might want to implement a UNION query as follows:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Enable Data Editing

If your goal is to create a query that allows for editing of each employee's assessment data, new records must be populated in the Assessment table for every employee. You can accomplish this through an INSERT SELECT action SQL:

[[See Video to Reveal this Text or Code Snippet]]

Step 5: Consider Adding More Fields

For better tracking, you might want to adjust your database structure slightly. Consider having two separate date fields: AssessmentDate (when the assessment should be done) and SignedDate (indicating when it was actually completed). This allows for more robust data handling.

Conclusion

In summary, effectively using SQL queries in MS Access can assist you in retrieving a comprehensive list of employees alongside their assessment completion status, even when some have not submitted their data. Through the strategic use of LEFT JOIN, UNION queries, and dynamic date selection, you can enhance your employee assessment tracking capabilities. By following the steps outlined above, you can ensure that you have an efficient and complete oversight of self-assessments in your organization.

For further assistance with SQL queries and Access database managem