Access - FULL OUTER JOIN without using UNION so recordset is updateable

If I use UNION for a subform recordset in Access, it disallows making that recordset updateable.

The problem is, I need to be able to update the recordset.

I have two tables - One is a list of employees (Employees). The other is a record of how many tickets each employee closed for the day. As a result, that table (INCTech) uses EmployeeID and Date as a joint primary key, since each employee ca开发者_运维技巧n only have one record for a given date.

I can't use a LEFT OUTER JOIN to retrieve a complete list of employees, because it will exclude any employee that has a record in INCTech for a date other than the given date. This doesn't work:

SELECT INCTech.EmployeeID, INCTech.Assigned, INCTech.Closed, INCTech.Submitted,
INCTech.StillOpen, INCTech.TheDate, Employees.FirstName, Employees.LastName
FROM Employees LEFT JOIN INCTech ON Employees.EmployeeID = INCTech.EmployeeID
WHERE Employees.GroupID = 8 AND (INCTech.TheDate = #4/15/2011# OR 
INCTech.TheDate IS NULL)

Because if an employee has a record for a different date than 4/15/2011, they don't meet either of the two date criteria - Date isn't NULL anymore, but it's also not the given date.

My query that works is:

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName,
Employees.FirstName & " " & Employees.LastName AS FullName, INCTech.TheDate,
INCTech.Assigned, INCTech.Closed, INCTech.Submitted, INCTech.StillOpen,
Employees.GroupID
FROM Employees
LEFT OUTER JOIN INCTech ON
Employees.EmployeeID=INCTech.EmployeeID 
WHERE (INCTech.TheDate)=Calendar.Value AND Employees.GroupID = ddlGroup.Value
UNION
SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, 
Employees.FirstName & " " & Employees.LastName AS FullName, NULL AS TheDate, 
NULL AS Assigned, NULL AS Closed, NULL As Submitted, NULL As StillOpen, 
Employees.GroupID 
FROM Employees 
LEFT OUTER JOIN INCTech ON Employees.EmployeeID=INCTech.EmployeeID 
WHERE Employees.EmployeeID NOT IN 
(SELECT INCTech.EmployeeID FROM INCTech WHERE INCTech.TheDate=Calendar.Value) 
AND Employees.GroupID = ddlGroup.Value

But since I have to use a UNION for that to work, the resulting recordset is read-only. This isn't going to work - I need to be able to modify or add data for each employee's daily record.

Help!

Build a form with this query as its Record Source:

SELECT e.EmployeeID, Date() AS TheDate, e.LastName, e.FirstName
FROM Employees AS e
ORDER BY e.LastName, e.FirstName;

Add a subform to it which uses INCTech as it's Record Source.

Use EmployeeID;TheDate as the Link Child Fields and Link Master Fields.

Choose Single Form as the subform's Default View. Set Navigation Buttons = No. Set the Cycle property to Current Record.

Then for any Employee record in the main form, your subform will display the matching INCTech row (same employee, same date) if there is a row, or allow you to add a row if none exists.

But you will probably want to edit INCTech data for dates other than today. In that case you could add an unbound text box control to the main form's header, say txtWhichDate, and allow the user to input a date value. In that case you can adjust the main form's query to reference the text box value instead of Date().

SELECT e.EmployeeID, Forms!YourFormName!txtWhichDate AS TheDate, e.LastName, e.FirstName
FROM Employees AS e
ORDER BY e.LastName, e.FirstName;

Or you can add code to txtWhichDate's After Update event to update the Record Source to match.

I think the issue is that your WHERE condition is restricting the results from the INCTech table to those records that match Calendar.Value.

What you could try doing is creating a separate query to retrieve the records from INCTech and apply the WHERE INCTech.TheDate = Calendar.Value clause in the query.

In your main query, reference this query instead of the INCTech table directly by using a LEFT JOIN.

This should return the records from Employee and show the relevant details for each employee record where there is a match in the query based on the INCTech table.

You're not going to be able to include multiple tables in a record set and make it updatable. Your form should edit the employee records table and link to the INCTech table as a subform.

Otherwise, you can create a query to display all of your data with the unions and multiple tables, but you'll have to code update statements on both tables to execute when a row is changed. You'll need to filter the row to update based on EmployeeID and EmployeeID and TheDate or the INCTech table. Do a search for updating multiple tables in Access.

This would solve your top query issue:

Select E.* 
  , I.TheDate
from Employees as E
Left Outer Join (
  Select * 
  from INCTech 
  Where TheDate = #4/15/2001#) as I
on E.EmployeeID = I.EmployeeID