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]

Do you have tblattendence.EmployeeNumber typed correctly?
In your previous post you said Access was asking you for tblAttendence.EmployeeNumber, but in your last post you said Access prompted you for tblAttendence.Employee.number (extra dot). Which is it? Perhaps that is the problem.

Also, what do you mean by "the view". Design view? SQL view?

Have a good evening!

Maq B-)
<insert witty signature here>

 

[quest4]

Sorry, fudge fingers. The first one is correcttblAttendence.EmployeeNumber. After Showing it to the Accounting folks, they agreed about the EmployeNumber,then asked if the pop-up could ask for the date and default to Date() and have a second pop-up ask for the WorkDay1-6 hours. Is this doable, or should it be done on the form only? Thanks again, sorry about the missteak, I am not the worlds best typest, I love spell check and this doesn't have it like my normal e-mail does.

 

[quest4]

Is there anyone who knows how to change what is being asked in the popup windows in this query? Thanks.

 

[tlbroadbent]

Please post the current query code. I'm not sure why you are getting the result and current pop-ups based on what you've given us. It will also be helpful to see the table structure and column names.

Thanks, Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

Gradly, here it is, I have some feel it has something to do with the No In near the bottom:

INSERT INTO tblAttendence ( EmployeeNumber, NameFirst, NameInitial, NameLast, DateWeekStarting )
SELECT [EmployeeNumber], [NameFirst], [NameInitial], [NameLast], Date()
FROM tblEmployees
WHERE (((tblEmployees.DateTerminated) Is Null)) AND ((tblEmployees.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence))
ORDER BY [tblAttendence].[EmployeeNumber];

Like I previously said, in the popup windows we need it to ask for the date starting, with a default of Date() and a second popup window asking for the hours for WorkDay1-6. Thaks alot, it seems like it is almost there, just a teak or two.

 

[tlbroadbent]

The problem is in the Order By clause at the end of the query. You are selecting columns from tblEmployees but ordering by the column in tblAttendence. Change the Order By clause to reference the correct table.

INSERT INTO tblAttendence ( EmployeeNumber, NameFirst, NameInitial, NameLast, DateWeekStarting )
SELECT [EmployeeNumber], [NameFirst], [NameInitial], [NameLast], Date()
FROM tblEmployees
WHERE (((tblEmployees.DateTerminated) Is Null)) AND ((tblEmployees.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence))
ORDER BY [tblEmployees].[EmployeeNumber]; Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

You were right, but it is not using the values I am entering in the popup windows. I would like the values on the select line to be defaults that can be over written. Here are my changes:

INSERT INTO tblAttendence ( EmployeeNumber, NameFirst, NameInitial, NameLast, WeekStartingDate, HoursPerDay)
SELECT [EmployeeNumber], [NameFirst], [NameInitial], [NameLast], Date()-7, &quot;8.00&quot;
FROM tblEmployees
WHERE (((tblEmployees.DateTerminated) Is Null)) AND ((tblEmployees.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence))
ORDER BY [tblAttendence].[DateWeekStarting] AND[tblAttendence].[HoursPerDay];

I am sure there is just a minor change or two needed. Thanks.

 

[quest4]

Is there a reason why the ORDER BY popup windows is not sending data to the table, as in the above query? Thanks.

 

[tlbroadbent]

The Order By clause doesn't insert data. It only orders the result. You cannot order by columns tblAttendence. You are not selecting from that table.

If you want the query to have parameters for the values to be inserted, the parameters must appear in the SELECT list not in the ORDER BY clause. Try the following query.

INSERT INTO tblAttendence ( EmployeeNumber, NameFirst, NameInitial, NameLast, WeekStartingDate, HoursPerDay)
SELECT [EmployeeNumber], [NameFirst], [NameInitial], [NameLast], NZ([Enter the week starting date],Date()-7), nz([Enter the default hours per day], 8)
FROM tblEmployees
WHERE (((tblEmployees.DateTerminated) Is Null)) AND ((tblEmployees.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence))
ORDER BY tblEmployees.EmployeeNumber Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

Works almost perfect, except that it is sorting decending instead of acsending. I tried changing ORDER BY to SORT BY, but it did not like that to well, it errored out. I take it that SORT BY is not an option here, is there anoher like trick up your sleeve like that NZ thing? That is really great, is there someplace that can tell what else NZ can do? Thanks a million.

 

[quest4]

I found the problem, my typing. I do have one last little thing. How do I get the HoursPerDay entry to become the value of the WorkDay1-6 fields, without entering it six times? I tried in the default value in properties, but no go and I tried in On Got Focus and that too was a no go. I was tring to do that part in the form. Any suggestions? Thanks a million for all of your great help so far.

 

[tlbroadbent]

You can read about NZ at

http://msdn.microsoft.com/library/en-us/office97/xhtml/output/F1/D2/S5A159.asp

As with most things there are multiple ways to get a single value entered into multiple columns. Here is one suggestion.

INSERT INTO tblAttendence ( EmployeeNumber, NameFirst, NameInitial, NameLast, WeekStartingDate, WorkDay1, WorkDay2, WorkDay3, WorkDay4, WorkDay5, WorkDay6)
SELECT [EmployeeNumber], [NameFirst], [NameInitial], [NameLast], NZ([Enter the week starting date],Date()-7), NZ([Enter the default hours per day], 8) As HoursPerDay, [HoursPerDay], [HoursPerDay], [HoursPerDay], [HoursPerDay], [HoursPerDay]
FROM tblEmployees
WHERE (((tblEmployees.DateTerminated) Is Null)) AND ((tblEmployees.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence))
ORDER BY tblEmployees.EmployeeNumber

If for some reason that doesn't work, try the following. Note the reapeating parameter. It will not requery if you type it exactly the same every time.

INSERT INTO tblAttendence ( EmployeeNumber, NameFirst, NameInitial, NameLast, WeekStartingDate, WorkDay1, WorkDay2, WorkDay3, WorkDay4, WorkDay5, WorkDay6)
SELECT [EmployeeNumber], [NameFirst], [NameInitial], [NameLast], NZ([Enter the week starting date],Date()-7), NZ([Enter the default hours per day], 8), NZ([Enter the default hours per day], 8), NZ([Enter the default hours per day], 8), NZ([Enter the default hours per day], 8), NZ([Enter the default hours per day], 8), NZ([Enter the default hours per day], 8)
FROM tblEmployees
WHERE (((tblEmployees.DateTerminated) Is Null)) AND ((tblEmployees.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence))
ORDER BY tblEmployees.EmployeeNumber Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[quest4]

It does work, but not quite the right way. I created a macro to run this in a form, which I placed in Form Properties, Event Tab, On Load.When I open the form, I get the two popup windows and I enter the data and that dat is going to the table correctly, but nothing is displaying in the form. Then when I open the form again, I get the two popup windows and I enter the data, and it does not go to the table, but ZI get data in the form, but it is the previously entered data. Also, the table only has the first entry data, shouldn't it have both entries, because I used two different dates? I do need to be able to look back at the data, at least 13 weeks worth, to be able to figure out bonuses and warnings and the like. By the way I am using the first example that you gave me, thanks again.

 

[quest4]

I cured the problem of the empty form on the first pass, by changing changing the Switchboard Manager to run a macro instead of running a form. The rest of the problems still exist. Please let me know if anyone has any ideas on straightening this out. Thanks.

 

[quest4]

Any ideas on the above problem? Thanks.

 

[quest4]

OK, I have removed the Primary Key fromtblAttendence aand allowed Dupes and the relationship table is now 1 to many. The table still won't accept any data other than the first entry. It won't even overwrite the older data, I have no idea where the newer data is going, but it sure looks like it ran ok til you open the table. Any help on this would be greatly appreciated. Thanks.

 

[Maquis]

Ok, I've been on vacation for the past 2 days. I'm afraid you lost me about 20 posts ago. X-)

You say that you are now trying to run this query through a form? What recordsource is the form based on? It should be tblattendance. A form will store added records in the table that it is based upon. A form should not be based upon an action query, only tables or select type queries

Also, I would strongly recommend putting the primary key back in. If your table structure is the same as was mentioned a few days ago it should be a combination of employee number and week. Maq B-)
<insert witty signature here>

 

[quest4]

No, now I'm run the query in a macro and then opening the form as the last step of the macro. I' sorry I thought I mentioned that. The only thing that is wrong is that you can only run this one time. If you run the query or the macro a second time nothing happens, the data does not appear in the table or it does not even over write the older data. I am also use just a couple of test subjects and I am using different dates andf hours per day. Here is a copy of the latest code that is running in the macro, under openquery:
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.EmployeeNumber) Not In (Select EmployeeNumber From tblAttendence)) AND ((tblEmployees.DateTerminated) Is Null))
ORDER BY [tblEmployees.EmployeeNumber];

The first time you run it it looks great, just what I wanted to see. Then I tried it again and I get to same data, even though I used different input. The macro is simple, it is beep-echo(off)-openquery(the one above)-echo(on)-openform( a form to display the table info for the week enter in the popup. Is this explain what is happening? Any ideas? Thanks.

 

[tlbroadbent]

The query only works once because you tell it to only add if the EmployeeNumber doesn't exist in tblAttendence. You need to add additional criteria because yo've added another key column - DateWeekStarting. Change the WHERE clause as follows.

WHERE tblEmployees.DateTerminated Is Null
AND Not Exists (Select * From tblAttendence
WHERE EmployeNumber=tblEmployees.EmployeeNumber
AND DateWeekStarting=tblEmployees.DateWeekStarting) Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Maquis]

Well, the 2nd time you run it there is probably nothing to append unless you've added more data to the database.

Here's what I would do. Take all off the Hours per day fields out of your append query. They really do not belong there. Your append query should really just be run once at the beginning of each week and should just create a blank record containing only the employee number and week.

Then you can use your form to allow your users to fill in the hours per day. Have the form pull up records for the current week and put 7 textboxes on it for the hours per day.

Also, please do not copy the employee's name into the attendance table. It really should only be stored in the employee table, otherwise you are violating normalization rules. (But you can worry about that after you get this working.) Maq B-)
<insert witty signature here>