update query

[KKruz]

I have two tables
Employees table, Hours table

Employees table has fields : Names, Autonumber, Phone number, ......
Hours table has fields : Names, Autonumber, Phone number, hours, total hours

Each time an entry is made for hours table the table enters the name, phone
and hours.

Emloyees table is one to Hours table many

I now realize I may have my relationships wrong. My database is working
well as is but looking down the road for updating names or phone numbers I
have a flaw. Before diving into changing the relationships I was hoping a
quick update query would allow me to update names or phone numbers in the
many table (Hours) with a change in the Employees table name/phone number.

Any suggestions on how I can work around the relationships for now and
change all the many names in the Hours table once I change the name in the
Employees table?

Example

Employees Table Data
Rob Jones with other emloyee data(phone, address, DOB)
Dave White " " " "

Hours table Data
name hours total hours
Rob Jones 10 10
Rob Jones 12 22
Dave White 8 8
Rob Jones 4 26
Dave White 4 12

Would like to change Rob to Robert and Dave to David in Hours table.

Relationship is between Employee Name and Hours Name

Hope I explained what I have so far.

Thanks,

 

[Jeanette Cunningham]

Hi,
before you try to update the names, delete the relationship between the two
tables in the relationships window.

To make an update query
--use the wizard - create query in design view
--use the employees table
--drag the employees field from the table to the query grid
--on the menu bar click Query, choose Update
--in the Update To: row of the query type in the new name
--in the Criteria: row of the query type in the name you want to change
--click the run icon on the toolbar
--repeat for as many names as need updating
--repeat for the hours table
--recreate the relationship between the 2 tables

Jeanette Cunningham

 

[Ken Sheridan]

I assume that you are using Names and Phone Number as two-column keys against
the possibility of two employees having the same name. In which case all you
need to do is enforce cascade updates in the relationship between Employees
and Hours on these two columns. Any changes you make to a value in the Names
or Phone Number column in a row in Employees will automatically change the
values in those column in the matching rows in Hours.

However, as you have an autonumber column in Employees, which I'll assume is
called EmployeeID, you can simply have a corresponding EmployeeID column (of
straightforward long integer number data type, not an autonumber) as a
foreign key in Hours and remove the names and Phone Number columns from Hours.

Having added an EmployeeID column to hours and indexed it non-uniquely
(duplicates allowed) you can populate it with the following update query:

UPDATE Hours INNER JOIN Employees
ON Hours.Names = Employees.Names
AND Hours.[Phone Number] = Employees.[Phone Number]
SET Hours.EmployeeID = Employees.EmployeeID;

Once you are happy that the column has been filled with the correct values
you can delete the Names and Phone Number columns from Hours and create a
relationship between the tables on the EmployeeID columns. You don't need to
enforce cascade updates in this case as an autonumber column's value can't be
changed, but you should enforce referential integrity, and possibly cascade
deletes if you want to be able to delete a row in Employees and automatically
have any matching rows in Hours deleted. If you elect not to enforce cascade
deletes then enforcing referential integrity will ensure that (a) you can't
delete a row from Employees until any matching rows in Hours have been
deleted, and (b) you can't enter or amend a row in Hours so that it refers to
a non-existent row in Employees.

Finally the Total Hours column in Hours is redundant as its value can be
computed in a query. Redundancy is not only unnecessary but leaves the door
open to inconsistent data being entered. To compute the total hours per
employee as a running sum you will need a column recording the date worked
(which I'd guess you do have) in which case you can use a subquery like so:

SELECT EmployeeID, DateWorked, Hours,
(SELECT SUM(Hours)
FROM Hours AS H2
WHERE H2.EmployeeID = H1.EmployeeID
AND H2.DateWorked <= H1.DateWorked)
AS TotalHours
FROM Hours AS H1
ORDER BY EmployeeID, DateWorked;

The way it works is by distinguishing between two instances of the Hours
table with aliases H1 and H2 and correlating the subquery with the outer
query so that it returns the sum of hours for each employee up to and
including the current row retuned by the outer query.

If you reply to this post and by any chance I don't respond, my apologies.
The email account in which I am normally notified of replies appears to have
given up the ghost; I'm not even getting any spam! I've changed my email
address for notifications here, so hopefully I will hear of any responses
from now on.

Ken Sheridan
Stafford, England

 

[KKruz]

This is great. Plenty to play with and make something much better. Thank you.
--
learning by playing

I assume that you are using Names and Phone Number as two-column keys against
the possibility of two employees having the same name. In which case all you
need to do is enforce cascade updates in the relationship between Employees
and Hours on these two columns. Any changes you make to a value in the Names
or Phone Number column in a row in Employees will automatically change the
values in those column in the matching rows in Hours.

However, as you have an autonumber column in Employees, which I'll assume is
called EmployeeID, you can simply have a corresponding EmployeeID column (of
straightforward long integer number data type, not an autonumber) as a
foreign key in Hours and remove the names and Phone Number columns from Hours.

Having added an EmployeeID column to hours and indexed it non-uniquely
(duplicates allowed) you can populate it with the following update query:

UPDATE Hours INNER JOIN Employees
ON Hours.Names = Employees.Names
AND Hours.[Phone Number] = Employees.[Phone Number]
SET Hours.EmployeeID = Employees.EmployeeID;

Once you are happy that the column has been filled with the correct values
you can delete the Names and Phone Number columns from Hours and create a
relationship between the tables on the EmployeeID columns. You don't need to
enforce cascade updates in this case as an autonumber column's value can't be
changed, but you should enforce referential integrity, and possibly cascade
deletes if you want to be able to delete a row in Employees and automatically
have any matching rows in Hours deleted. If you elect not to enforce cascade
deletes then enforcing referential integrity will ensure that (a) you can't
delete a row from Employees until any matching rows in Hours have been
deleted, and (b) you can't enter or amend a row in Hours so that it refers to
a non-existent row in Employees.

Finally the Total Hours column in Hours is redundant as its value can be
computed in a query. Redundancy is not only unnecessary but leaves the door
open to inconsistent data being entered. To compute the total hours per
employee as a running sum you will need a column recording the date worked
(which I'd guess you do have) in which case you can use a subquery like so:

SELECT EmployeeID, DateWorked, Hours,
(SELECT SUM(Hours)
FROM Hours AS H2
WHERE H2.EmployeeID = H1.EmployeeID
AND H2.DateWorked <= H1.DateWorked)
AS TotalHours
FROM Hours AS H1
ORDER BY EmployeeID, DateWorked;

The way it works is by distinguishing between two instances of the Hours
table with aliases H1 and H2 and correlating the subquery with the outer
query so that it returns the sum of hours for each employee up to and
including the current row retuned by the outer query.

If you reply to this post and by any chance I don't respond, my apologies.
The email account in which I am normally notified of replies appears to have
given up the ghost; I'm not even getting any spam! I've changed my email
address for notifications here, so hopefully I will hear of any responses
from now on.

Ken Sheridan
Stafford, England

I have two tables
Employees table, Hours table

Employees table has fields : Names, Autonumber, Phone number, ......
Hours table has fields : Names, Autonumber, Phone number, hours, total hours

Each time an entry is made for hours table the table enters the name, phone
and hours.

Emloyees table is one to Hours table many

I now realize I may have my relationships wrong. My database is working
well as is but looking down the road for updating names or phone numbers I
have a flaw. Before diving into changing the relationships I was hoping a
quick update query would allow me to update names or phone numbers in the
many table (Hours) with a change in the Employees table name/phone number.

Any suggestions on how I can work around the relationships for now and
change all the many names in the Hours table once I change the name in the
Employees table?

Example

Employees Table Data
Rob Jones with other emloyee data(phone, address, DOB)
Dave White " " " "

Hours table Data
name hours total hours
Rob Jones 10 10
Rob Jones 12 22
Dave White 8 8
Rob Jones 4 26
Dave White 4 12

Would like to change Rob to Robert and Dave to David in Hours table.

Relationship is between Employee Name and Hours Name

Hope I explained what I have so far.

Thanks,

 

[Ken Sheridan]

I did get the notification of your reply at my other email address I'm glad
to say.

KS

This is great. Plenty to play with and make something much better. Thank you.
--
learning by playing

I assume that you are using Names and Phone Number as two-column keys against
the possibility of two employees having the same name. In which case all you
need to do is enforce cascade updates in the relationship between Employees
and Hours on these two columns. Any changes you make to a value in the Names
or Phone Number column in a row in Employees will automatically change the
values in those column in the matching rows in Hours.

However, as you have an autonumber column in Employees, which I'll assume is
called EmployeeID, you can simply have a corresponding EmployeeID column (of
straightforward long integer number data type, not an autonumber) as a
foreign key in Hours and remove the names and Phone Number columns from Hours.

Having added an EmployeeID column to hours and indexed it non-uniquely
(duplicates allowed) you can populate it with the following update query:

UPDATE Hours INNER JOIN Employees
ON Hours.Names = Employees.Names
AND Hours.[Phone Number] = Employees.[Phone Number]
SET Hours.EmployeeID = Employees.EmployeeID;

Once you are happy that the column has been filled with the correct values
you can delete the Names and Phone Number columns from Hours and create a
relationship between the tables on the EmployeeID columns. You don't need to
enforce cascade updates in this case as an autonumber column's value can't be
changed, but you should enforce referential integrity, and possibly cascade
deletes if you want to be able to delete a row in Employees and automatically
have any matching rows in Hours deleted. If you elect not to enforce cascade
deletes then enforcing referential integrity will ensure that (a) you can't
delete a row from Employees until any matching rows in Hours have been
deleted, and (b) you can't enter or amend a row in Hours so that it refers to
a non-existent row in Employees.

Finally the Total Hours column in Hours is redundant as its value can be
computed in a query. Redundancy is not only unnecessary but leaves the door
open to inconsistent data being entered. To compute the total hours per
employee as a running sum you will need a column recording the date worked
(which I'd guess you do have) in which case you can use a subquery like so:

SELECT EmployeeID, DateWorked, Hours,
(SELECT SUM(Hours)
FROM Hours AS H2
WHERE H2.EmployeeID = H1.EmployeeID
AND H2.DateWorked <= H1.DateWorked)
AS TotalHours
FROM Hours AS H1
ORDER BY EmployeeID, DateWorked;

The way it works is by distinguishing between two instances of the Hours
table with aliases H1 and H2 and correlating the subquery with the outer
query so that it returns the sum of hours for each employee up to and
including the current row retuned by the outer query.

If you reply to this post and by any chance I don't respond, my apologies.
The email account in which I am normally notified of replies appears to have
given up the ghost; I'm not even getting any spam! I've changed my email
address for notifications here, so hopefully I will hear of any responses
from now on.

Ken Sheridan
Stafford, England

I have two tables
Employees table, Hours table

Employees table has fields : Names, Autonumber, Phone number, ......
Hours table has fields : Names, Autonumber, Phone number, hours, total hours

Each time an entry is made for hours table the table enters the name, phone
and hours.

Emloyees table is one to Hours table many

I now realize I may have my relationships wrong. My database is working
well as is but looking down the road for updating names or phone numbers I
have a flaw. Before diving into changing the relationships I was hoping a
quick update query would allow me to update names or phone numbers in the
many table (Hours) with a change in the Employees table name/phone number.

Any suggestions on how I can work around the relationships for now and
change all the many names in the Hours table once I change the name in the
Employees table?

Example

Employees Table Data
Rob Jones with other emloyee data(phone, address, DOB)
Dave White " " " "

Hours table Data
name hours total hours
Rob Jones 10 10
Rob Jones 12 22
Dave White 8 8
Rob Jones 4 26
Dave White 4 12

Would like to change Rob to Robert and Dave to David in Hours table.

Relationship is between Employee Name and Hours Name

Hope I explained what I have so far.

Thanks,