UpdateQuery That is not Updating 1

[quest4]

I'm not sure that I am doing this right, but I thought a update query would be the answer. I have two tables, tblEmployees and tblAttendence, which have a one-to-one relationship, with all of the casading, using the EmployeeNumber as the primary key. I tried using this update query to get tblEmployees to put new EmployeeNumber's into the tblAttendence, but nothing ever gets put into tblAttendence from tblEmployees. This is the query:

UPDATE tblEmployees LEFT JOIN tblAttendence ON tblEmployees.EmployeeNumber = tblAttendence.EmployeeNumber
SET tblEmployees.EmployeeNumber = [tblAttendence]![EmployeeNumber]
WHERE (((tblEmployees.DateTerminated) Is Null));

Can anyone spot my error? Thanks a million in advance.

 

[quest4]

Ok, I tried tlbroadbent's change, with one modification in the second part of the WHERE statement DateWeekStarting=tblEmployees.DateWeekStarting, which does not exist in the tblEmployees table only in tblAttendence table so I did make a modification. It now reads as:

INSERT INTO tblAttendence ( EmployeeNumber, NameFirst, NameInitial, NameLast, DateWeekStarting, HoursPerDay, WorkDay1, WorkDay2, WorkDay3, WorkDay4, WorkDay5, WorkDay6 )
SELECT [tblEmployees].[EmployeeNumber], [tblEmployees].[NameFirst], [tblEmployees].[NameInitial], [tblEmployees].[NameLast], NZ([Enter the Week Starting Date],Date()-7), NZ([Enter the Default Hours Per Day],9) AS HoursPerDay, [HoursperDay], [HoursPerDay], [HoursPerDay], [HoursPerDay], [HoursPerDay], [HoursPerDay]
FROM tblEmployees
WHERE (((tblEmployees.DateTerminated) Is Null)) AND Not Exists (Select * From tblAttendence
WHERE EmployeeNumber = tblEmployees.EmployeeNumber AND tblAttendence.DateWeekStarting)
ORDER BY [tblEmployees.EmployeeNumber];

The bad news is that I get the same results, no additional entries in tblAttendence. Thanks.

 

[tlbroadbent]

Sorry. My error. Change the criteria ever slow slightly.

WHERE (((tblEmployees.DateTerminated) Is Null)) AND Not Exists (Select * From tblAttendence
WHERE EmployeeNumber = tblEmployees.EmployeeNumber AND tblAttendence.DateWeekStarting=NZ([Enter the Week Starting Date],Date()-7)) Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

The table works. The form over works, it is bring up everything. Is there a quick way to resrict it to only the DateWeekStarting date entered? Also, in the query, I now have two places where I have an NZ([Enter the Week Starting Date],Date()-7), the first is in the SELECT and the second is the modification you just gave me, should I remove the one in SELECT? One last little thing, is there a way to apply an input mask, like in forms and tables, to the two pop-up windows? Thanks a million, if this keeps up I may have to start to think good can happen on the Interenet and there is really information on it.

 

[tlbroadbent]

There is no harm in having the parameter in two places but you can possibly make a change to eliminate one of the instances. So far as I know, you cannot apply a mask to the parameters. Yes, you can filter the results displayed in the form. Search HELP for information on filtering forms.

INSERT INTO tblAttendence ( EmployeeNumber, NameFirst, NameInitial, NameLast, DateWeekStarting, HoursPerDay, WorkDay1, WorkDay2, WorkDay3, WorkDay4, WorkDay5, WorkDay6 )
SELECT [tblEmployees].[EmployeeNumber], [tblEmployees].[NameFirst], [tblEmployees].[NameInitial], [tblEmployees].[NameLast], NZ([Enter the Week Starting Date],Date()-7) As StartingDate, NZ([Enter the Default Hours Per Day],9) AS HoursPerDay, [HoursperDay], [HoursPerDay], [HoursPerDay], [HoursPerDay], [HoursPerDay], [HoursPerDay]
FROM tblEmployees
WHERE (((tblEmployees.DateTerminated) Is Null))
AND Not Exists
(SELECT * From tblAttendence
WHERE EmployeeNumber = tblEmployees.EmployeeNumber
AND tblAttendence.DateWeekStarting=[StartingDate]
ORDER BY [tblEmployees.EmployeeNumber]; Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

I used format in the table and that works almost as good as a input mask in the parameter values. I do need one poece of information, what is the name of the parameter value for tblAttendence.WeekStartingDate? I need to use it in a filter to set it equal to the tblAttendence.WeekStartingDate. Thanks.

 

[quest4]

Well, sense I could figure out the parameter name, I decided to take the easy wat and use a tmp table, tblAttendence_Tmp. This works great on the form. So now I created another append query to take the data from tblAttendence_Tmp and append it into the main storage table, tblAttendence. It crashs with validation errors when run, here is the query:

INSERT INTO tblAttendence ( EmployeeNumber, DateWeekStarting, HoursPerDay, WorkDay1, WorkDay1Reason, WorkDay2, WorkDay2Reason, WorkDay3, WorkDay3Reason, WorkDay4, WorkDay4Reason, WorkDay5, WorkDay5Reason, WorkDay6, WorkDay6Reason )
SELECT [EmployeeNumber], [DateWeekStarting], [HoursPerDay], [WorkDay1], [WorkDay1Reason], [WorkDay2], [WorkDay2Reason], [WorkDay3], [WorkDay3Reason], [WorkDay4], [WorkDay4Reason], [WorkDay5], [WorkDay5Reason], [WorkDay6], [WorkDay6Reason]
FROM tblAttendence_Tmp
ORDER BY [tblAttendence_Tmp.EmployeeNumber],[tblAttendence_tmp.DateWeekStarting];

Very simply put transfer everything over, except three columns in tblAttendence_Tmp, which are the names, which I am going to leave out of the main table, tblAttendence. Any ideas on what is wroing with this query? Thanks.

 

[quest4]

I got. Thanks for all of the help you folks have been really great, in fact more than I ever expected. Now on to the hard part, the queries.