RE: update query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

KS

"KKruz" wrote:

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


"Ken Sheridan" wrote:

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" wrote:

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,

--
learning by playing

.

Relevant Pages

  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: OT: SQL
    ... said was a totally failed attempt to formalize and automate database ... query processing. ... create table Employees ... Manager and Employee Salaries. ...
    (sci.logic)
  • Re: query a number stored as text
    ... Your query is quite complex, ... I need to join the Employees table to the Orders table like so ... ... "Lee" wrote in message ... >> GlobalSign digital certificate is a forgery and should be deleted without ...
    (microsoft.public.access.queries)
  • Re: OT: SQL
    ... said was a totally failed attempt to formalize and automate database ... query processing. ... create table Employees ... Manager and Employee Salaries. ...
    (sci.logic)
  • Re: Multi-Value Field Query
    ... It was directed at what Microsoft calls a Multi-Valued field, which is not the same as what you have ... This doesn't work very well in the query interface in Access, so you probably need to write a VBA function that does this for you. ... three areas 'CreatedBy', 'Owner', 'Employees'. ... appropriately and it lists all employees involved ...
    (microsoft.public.access.queries)