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.

 

[Maquis]

Try this:

UPDATE tblEmployees LEFT JOIN tblAttendance ON [tblEmployees].EmployeeNumber = tblAttendance.EmployeeNumber
SET tblAttendance.EmployeeNumber = tblEmployees.EmployeeNumber
WHERE (((tblAttendance.EmployeeNumber) Is Null) AND (([tblEmployees].DateTerminated) Is Null));
Maq B-)
<insert witty signature here>

 

[quest4]

I made the change and got the following error:
Access can't update all the records in the update query.
Access didn't update 0 field(s) due to type of conversion failure, 0 record(s) due to key violations, 0 record(s) dues to lock violations, and 1 record(s) due to validation violations.
I'm not using any validation rules on this field, except that they are primary keys, one in each table. I have required check and zero length set to no and no duplicated is set. That is everything I did to the field in the table.

 

[Maquis]

Hmmm, It's hard to say what the problem could be without seeing your data. I've gotten lots of &quot;conversion type&quot; failures in the past, but never a validation error. You're absolutely positive that the fields don't have any validation rules, right? If that's the case, the only thing I can suggest is to look carefully at the record that failed to update and see if there is something odd about the id. Perhaps it contains some weird characters or something else that is hindering the update.

Perhaps someone else who reads this has encountered that particular error before and can offer some advice.
Maq B-)
<insert witty signature here>

 

[quest4]

I am absolutely positve the validation rules are blank. But this gets better and better, now I can get it to run in a view, but still not transfer data from one table to the other. The dumby enters are just simple names, with no special charactors. Well thanks for the help, anyway, it is maybe a half a step closer.

 

[tlbroadbent]

If I understand what you are attempting to do then you want to use an INSERT rather than an UPDATE query. If you want to create a new record in tblAttendance for a new Employee in tblEmployees then try the following.

Insert Into tblAttendence (EmployeeNumber)
Select EmployeeNumber
FROM tblEmployees
WHERE tblEmployees.DateTerminated Is Null
AND EmployeeNumber Not In = (Select EmployeeNumber From tblAttendence); Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Maquis]

Duh, <<slaps head in stupidity>> X-)

Yep, Terry is right. Sorry, I didn't see it earlier. Maq B-)
<insert witty signature here>

 

[quest4]

Basically, tblAttendednce it a weekly collection of the daily hours of each employee who has not been terminated. at the beginning of the week, I would like to go to tblEmployees and get all of the EmployeeNumber's of all the employees, who are not terminated, so I can enter another weeks worth of daily hours worked. Does this kind of make any sense?

 

[tlbroadbent]

It makes perfect sense and is easier to understand than your original post. I assume that tblAttendnace has a week indicator of some kind - either a date or week number. Just modify the INSERT query as needed. For example, it might look something like the following.

Insert Into tblAttendence (EmployeeNumber, WeekNo)
Select EmployeeNumber, 43
FROM tblEmployees
WHERE tblEmployees.DateTerminated Is Null
AND EmployeeNumber Not In = (Select EmployeeNumber From tblAttendence);

In this case, I used a constant 43 for the WeekNo column. You could build a query in code or use a table look up to find the next week number to insert. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Maquis]

Another quick note: You originally said that employee number was the primary key for both tables, both now it sounds like you would want the primary key for the attendance table to be a combination of employee number and week #.

I'm thinking that the attendance table is laid out something like this:
EmployeeNumber
WeekNumber
Day1Hours
Day2Hours
.
.
Day7Hours


Maq B-)
<insert witty signature here>

 

[quest4]

Actually I was going to use a date column and for the week starting and 7 day columns for the hours worked per day. Then on each Monday enter the, Date(), and then enter the daily hours workedfor each employee.

 

[quest4]

I just tried the Inseret query and it errors out on save. It looks like it does not like something about the Not In, it says in operator without()in query expression.

 

[tlbroadbent]

I apologize. There is an &quot;=&quot; in my post that shouldn't be there. The final line should read...

AND EmployeeNumber Not In (Select EmployeeNumber From tblAttendence);
Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

Ok, I tryed this code:

INSERT INTO tblAttendence ( EmployeeNumber, EmployeeNumber )
SELECT tblEmployees.EmployeeNumber, tblAttendence.EmployeeNumber
FROM tblEmployees LEFT JOIN tblAttendence ON tblEmployees.EmployeeNumber = tblAttendence.EmployeeNumber
WHERE (((tblEmployees.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence)) AND ((tblEmployees.DateTerminated) Is Null))
ORDER BY tblAttendence.EmployeeNumber;

Which is what you told me, with the relationship added. When I view it, I get a blank in the tblAttendence.EmployeeNumber field. When I run it I get a message that reads duplicate output destination 'EmployeeNumber'. As you can see, I list the complete field everywhere and I can't see where the duplicate is.

 

[Maquis]

Look at your 1st line. You have EmployeeNumber written there twice. Maq B-)
<insert witty signature here>

 

[quest4]

I changed it to this:

INSERT INTO tblAttendence ( EmployeeNumber )
SELECT tblEmployees.EmployeeNumber
FROM tblEmployees LEFT JOIN tblAttendence ON tblEmployees.EmployeeNumber = tblAttendence.EmployeeNumber
WHERE (((tblEmployees.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence)) AND ((tblEmployees.DateTerminated) Is Null))
ORDER BY tblAttendence.EmployeeNumber;


Now I get that original message, about the validation rule, again. I have looked again and the EmployeeNumber in both tables is exactly the same, and yet the EmployeeNumber from tblEmployees will not go into the EmployeeNumber of tblAttendence.

 

[Maquis]

Hmmm, I think I see it. (I hope)

What is the primary key of your attendance table? Is it employee number, or is it a combination of Employee and week like I suggested earlier? If it's the combination then you cannot leave the week field blank. You must append both the Employee number and the week field at the same time.


Maq B-)
<insert witty signature here>

 

[tlbroadbent]

There is no need for the Left Join in the query. It complicates the query unnecessarily.

Insert Into tblAttendence (EmployeeNumber, WeekDate)
Select EmployeeNumber, date()
FROM tblEmployees
WHERE tblEmployees.DateTerminated Is Null
AND EmployeeNumber Not In (Select EmployeeNumber From tblAttendence); Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

Tried it, at least in the view, and it asked for for tblAttendence.EmployeeNumber, I the answered &quot;*&quot; and it poped up a table and the date() was under a new column called Expr1001. My question is why is it asking me for a number, I was for automatic and in a form I could then bring theses employees up one at a time and make the proper enteries. Well it is getting closer. Thanks.

 

[quest4]

I tried run the code and it appears to work, except that it is still asking for tblAttendence.Employee.number. It seems the Expr1001 is only in the view, for some reason. Thanks a million, it is so close I can almost taste it. It is time to go home now, I will try again in the AM.