Updating fields...

G

Guest

Hello.

Here's my issue:

I have one master table, named Employees (primary key = EmployeeID), and
another Table, named Overtime (Foreign Key is EmployeeID). One field in the
Employee table is named Position Grade (i.e. 5.1, 5.2...). I enter new
records via a form using a basic form/subform strategy and that part works OK
(there can be multiple Overtime records for a given employee ID). Problem is
when I have to change the Position Grade, say to a higher number, all of the
records with that field also chnage to the higher grade, screwing up my
previous calculations. So, if I saved a record three months ago with a Grade
of 5.1, and the Grade changes to, say, 7.1 yesterday, and I make the change
to the Employee table Grade field, all of the records change also.

How do I make it so that all previous records are not changed when there is
a Grade change? I hope there is a non-code solution available, if not, guess
I settle with code.

Thank you.

NWO
 
J

John Vinson

Hello.

Here's my issue:

I have one master table, named Employees (primary key = EmployeeID), and
another Table, named Overtime (Foreign Key is EmployeeID). One field in the
Employee table is named Position Grade (i.e. 5.1, 5.2...). I enter new
records via a form using a basic form/subform strategy and that part works OK
(there can be multiple Overtime records for a given employee ID). Problem is
when I have to change the Position Grade, say to a higher number, all of the
records with that field also chnage to the higher grade, screwing up my
previous calculations. So, if I saved a record three months ago with a Grade
of 5.1, and the Grade changes to, say, 7.1 yesterday, and I make the change
to the Employee table Grade field, all of the records change also.

How do I make it so that all previous records are not changed when there is
a Grade change? I hope there is a non-code solution available, if not, guess
I settle with code.

Thank you.

NWO
Click to expand...

Evidently you'll need to store the Grade in the Overtime table - that
is, the grade as of the time of the position.

The records in Overtime aren't actually changing: you're just seeing
the current value of Grade from the Employees table.

You'll need just a bit of VBA code in the Subform's Beforeinsert event
to do this: include the Grade field in the subform's recordsource, and
put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Grade] = Parent![Grade]
End Sub

in the BeforeInsert event.

John W. Vinson[MVP]
 
P

peregenem

John said:
Evidently you'll need to store the Grade in the Overtime table - that
is, the grade as of the time of the position.
Click to expand...

IMO this is would result in Grade being stored redundantly. From the
OP's description, Grade changes at a slower rate than overtime rows are
generated. I also assume that Grade is required for functionality other
than overtime.

I'd recommend a separate table (employee_ID, grade, start_date,
end_date) to which the Overtime table could be joined to glean the
grade in effect on the date the overtime was performed.
 
J

John Vinson

IMO this is would result in Grade being stored redundantly. From the
OP's description, Grade changes at a slower rate than overtime rows are
generated. I also assume that Grade is required for functionality other
than overtime.

I'd recommend a separate table (employee_ID, grade, start_date,
end_date) to which the Overtime table could be joined to glean the
grade in effect on the date the overtime was performed.
Click to expand...

That might indeed be preferable; the date-range query is a bit
complicated but may well be justified under the circumstances.

John W. Vinson[MVP]
 
G

Guest

Thank you for the reply.

Just so I understand, are you suggesting that I remove the Grade from the
Eemployee table, create a seperate table, and link this new table to the OT
table via the EmployeeID field? I foreget to inlcude in my original post
that the overtime hourly rate is dependent on the Grade - would you recommend
storing this value in this new table as well?

To clarify, PK in the Employee table woudl be EmployeeID, the PK in the
Overtime table would be OTID with the FK being Employee ID, and the new table
would have an autonumber as the PK and EmployeeID as the FK, dows this sound
correct?

Thank you kindly.

NWO :)
----------------
 
G

Guest

Thanks John - worked great. I'm not really worried about redudant records at
this point.

Now, since the hourly OT rate is based on the grade, could I do the same
thing as I did with Grade - meaning:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Grade] = Parent![Grade]
Me![OTRate] = Parent [OTRate]
End SUb

I can then run all of my queries from the OT table.

Please advise if I'm on the right track, or if you have anohter approach.

Thanks again.

NWO :)
--------------------------

John Vinson said:
Hello.

Here's my issue:

I have one master table, named Employees (primary key = EmployeeID), and
another Table, named Overtime (Foreign Key is EmployeeID). One field in the
Employee table is named Position Grade (i.e. 5.1, 5.2...). I enter new
records via a form using a basic form/subform strategy and that part works OK
(there can be multiple Overtime records for a given employee ID). Problem is
when I have to change the Position Grade, say to a higher number, all of the
records with that field also chnage to the higher grade, screwing up my
previous calculations. So, if I saved a record three months ago with a Grade
of 5.1, and the Grade changes to, say, 7.1 yesterday, and I make the change
to the Employee table Grade field, all of the records change also.

How do I make it so that all previous records are not changed when there is
a Grade change? I hope there is a non-code solution available, if not, guess
I settle with code.

Thank you.

NWO
Click to expand...

Evidently you'll need to store the Grade in the Overtime table - that
is, the grade as of the time of the position.

The records in Overtime aren't actually changing: you're just seeing
the current value of Grade from the Employees table.

You'll need just a bit of VBA code in the Subform's Beforeinsert event
to do this: include the Grade field in the subform's recordsource, and
put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![Grade] = Parent![Grade]
End Sub

in the BeforeInsert event.

John W. Vinson[MVP]
Click to expand...
 
P

peregenem

NWO said:
Just so I understand, are you suggesting that I remove the Grade from the
Eemployee table, create a seperate table, and link this new table to the OT
table via the EmployeeID field? I foreget to inlcude in my original post
that the overtime hourly rate is dependent on the Grade - would you recommend
storing this value in this new table as well?
Click to expand...

Yes and yes.

Here is a suggested structure. I've made numerous assumptions about
business rules so it's just an example for ideas rather than a working
solution.

To actually run the code, execute each statement individually against
the OLE DB provider for Jet 4.0, either by putting Access into
'ANSI-92' mode
(http://office.microsoft.com/en-us/assistance/HP030704831033.aspx) or
using code (e.g. in Access: CurrentProject.Connection.Execute "SQL
statement here"):

CREATE TABLE Employee (
EmployeeID INTEGER NOT NULL PRIMARY KEY
)
;
CREATE TABLE Grades (
grade CHAR(3) NOT NULL PRIMARY KEY,
CHECK(grade LIKE '[1-9].[0-9]')
)
;
CREATE TABLE EmployeesGrades (
EmployeeID INTEGER NOT NULL
REFERENCES Employee (EmployeeID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
position_grade CHAR(3) NOT NULL
REFERENCES Grades (grade)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK(start_date <= end_date),
PRIMARY KEY (position_grade, start_date, EmployeeID)
)
;
CREATE VIEW EmployeesCurrentGrades
(EmployeeID, position_grade)
AS
SELECT T1.EmployeeID, T1.position_grade
FROM EmployeesGrades AS T1
WHERE T1.start_date = (
SELECT MAX(start_date)
FROM EmployeesGrades
WHERE EmployeeID = T1.EmployeeID
AND end_date IS NULL)
;
CREATE TABLE Overtime (
EmployeeID INTEGER NOT NULL
REFERENCES Employee (EmployeeID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK(start_date < end_date),
CHECK(DATEDIFF('h', start_date, end_date) <= 5),
PRIMARY KEY (EmployeeID, start_date)
)
;
CREATE TABLE OvertimeRates (
position_grade CHAR(3) NOT NULL
REFERENCES Grades (grade)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
overtime_rate DECIMAL(8, 4) NOT NULL,
CHECK(overtime_rate >= 0),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK(start_date <= end_date),
PRIMARY KEY (position_grade, start_date)
)
;
CREATE VIEW OvertimeCurrentRates (
position_grade, overtime_rate)
AS
SELECT T1.position_grade, T1.overtime_rate
FROM OvertimeRates AS T1
WHERE T1.start_date = (
SELECT MAX(start_date)
FROM OvertimeRates
WHERE position_grade = T1.position_grade
AND end_date IS NULL)
;
CREATE VIEW OvertimeGrades
(EmployeeID, overtime_start_date, overtime_end_date, position_grade)
AS
SELECT Overtime.EmployeeID, Overtime.start_date, Overtime.end_date,
EmployeesGrades.position_grade
FROM Overtime LEFT JOIN EmployeesGrades
ON Overtime.EmployeeID = EmployeesGrades.EmployeeID
AND (Overtime.start_date BETWEEN EmployeesGrades.start_date AND
IIF(EmployeesGrades.end_date IS NULL, NOW(), EmployeesGrades.end_date))
;
CREATE VIEW OvertimeDetails
(EmployeeID, overtime_start_date, overtime_end_date, position_grade,
overtime_rate)
AS
SELECT OvertimeGrades.EmployeeID, OvertimeGrades.overtime_start_date,
OvertimeGrades.overtime_end_date, OvertimeGrades.position_grade,
OvertimeRates.overtime_rate
FROM OvertimeGrades LEFT JOIN OvertimeRates
ON OvertimeGrades.position_grade = OvertimeRates.position_grade
AND (OvertimeGrades.overtime_start_date BETWEEN
OvertimeRates.start_date AND IIF(OvertimeRates.end_date IS NULL, NOW(),
OvertimeRates.end_date))
;
INSERT INTO Employee (EmployeeID) VALUES (1)
;
INSERT INTO Employee (EmployeeID) VALUES (2)
;
INSERT INTO Grades (grade) VALUES ('5.1')
;
INSERT INTO Grades (grade) VALUES ('5.2')
;
INSERT INTO Grades (grade) VALUES ('7.1')
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date,
end_date) VALUES ('5.1', 100, #2004-01-01#, #2004-12-31#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date,
end_date) VALUES ('5.2', 120, #2004-01-01#, #2004-12-31#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date,
end_date) VALUES ('7.1', 200, #2004-01-01#, #2004-12-31#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date)
VALUES ('5.1', 110, #2005-01-01#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date)
VALUES ('5.2', 150, #2005-01-01#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date)
VALUES ('7.1', 210, #2005-01-01#)
;
INSERT INTO EmployeesGrades (EmployeeID, position_grade, start_date,
end_date) VALUES (1,'5.1',#2003-09-01#,#2005-03-31#)
;
INSERT INTO EmployeesGrades (EmployeeID, position_grade, start_date)
VALUES (1,'5.2',#2005-04-01#)
;
INSERT INTO EmployeesGrades (EmployeeID, position_grade, start_date,
end_date) VALUES (2,'5.2',#2004-04-30#,#2004-11-30#)
;
INSERT INTO EmployeesGrades (EmployeeID, position_grade, start_date)
VALUES (2,'7.1',#2004-12-01#)
;
INSERT INTO Overtime (EmployeeID, start_date, end_date) VALUES (1,
#2004-10-01 17:00:00#, #2004-10-01 20:00:00#)
;
INSERT INTO Overtime (EmployeeID, start_date, end_date) VALUES (2,
#2004-10-01 17:00:00#, #2004-10-01 20:00:00#)
;
INSERT INTO Overtime (EmployeeID, start_date, end_date) VALUES (1,
#2005-10-01 17:00:00#, #2005-10-01 20:00:00#)
;
INSERT INTO Overtime (EmployeeID, start_date, end_date) VALUES (2,
#2005-10-01 17:00:00#, #2005-10-01 20:00:00#)
;
SELECT EmployeeID, overtime_start_date, overtime_end_date,
position_grade, overtime_rate
FROM OvertimeDetails
ORDER BY overtime_start_date, position_grade
;
 
G

Guest

Hello.

Thank you so much for the time you put into this response. However, I am
trying to avoid code at all cost, other than a code snippet here and there.
I think I'm just going to settle with redundant data and write the records to
the Overtime table as John Vinson suggested. The procedure works as it
captures the records based on that point in time as to the Grade of the
empoyee. This way when I run queries, the total OT amonts will be correct
because hey will be based on the Grade level of the employee for a given
record.

Again, thank you

NWO :)
---------------------

[email protected] said:
Just so I understand, are you suggesting that I remove the Grade from the
Eemployee table, create a seperate table, and link this new table to the OT
table via the EmployeeID field? I foreget to inlcude in my original post
that the overtime hourly rate is dependent on the Grade - would you recommend
storing this value in this new table as well?
Click to expand...

Yes and yes.

Here is a suggested structure. I've made numerous assumptions about
business rules so it's just an example for ideas rather than a working
solution.

To actually run the code, execute each statement individually against
the OLE DB provider for Jet 4.0, either by putting Access into
'ANSI-92' mode
(http://office.microsoft.com/en-us/assistance/HP030704831033.aspx) or
using code (e.g. in Access: CurrentProject.Connection.Execute "SQL
statement here"):

CREATE TABLE Employee (
EmployeeID INTEGER NOT NULL PRIMARY KEY
)
;
CREATE TABLE Grades (
grade CHAR(3) NOT NULL PRIMARY KEY,
CHECK(grade LIKE '[1-9].[0-9]')
)
;
CREATE TABLE EmployeesGrades (
EmployeeID INTEGER NOT NULL
REFERENCES Employee (EmployeeID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
position_grade CHAR(3) NOT NULL
REFERENCES Grades (grade)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK(start_date <= end_date),
PRIMARY KEY (position_grade, start_date, EmployeeID)
)
;
CREATE VIEW EmployeesCurrentGrades
(EmployeeID, position_grade)
AS
SELECT T1.EmployeeID, T1.position_grade
FROM EmployeesGrades AS T1
WHERE T1.start_date = (
SELECT MAX(start_date)
FROM EmployeesGrades
WHERE EmployeeID = T1.EmployeeID
AND end_date IS NULL)
;
CREATE TABLE Overtime (
EmployeeID INTEGER NOT NULL
REFERENCES Employee (EmployeeID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK(start_date < end_date),
CHECK(DATEDIFF('h', start_date, end_date) <= 5),
PRIMARY KEY (EmployeeID, start_date)
)
;
CREATE TABLE OvertimeRates (
position_grade CHAR(3) NOT NULL
REFERENCES Grades (grade)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
overtime_rate DECIMAL(8, 4) NOT NULL,
CHECK(overtime_rate >= 0),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK(start_date <= end_date),
PRIMARY KEY (position_grade, start_date)
)
;
CREATE VIEW OvertimeCurrentRates (
position_grade, overtime_rate)
AS
SELECT T1.position_grade, T1.overtime_rate
FROM OvertimeRates AS T1
WHERE T1.start_date = (
SELECT MAX(start_date)
FROM OvertimeRates
WHERE position_grade = T1.position_grade
AND end_date IS NULL)
;
CREATE VIEW OvertimeGrades
(EmployeeID, overtime_start_date, overtime_end_date, position_grade)
AS
SELECT Overtime.EmployeeID, Overtime.start_date, Overtime.end_date,
EmployeesGrades.position_grade
FROM Overtime LEFT JOIN EmployeesGrades
ON Overtime.EmployeeID = EmployeesGrades.EmployeeID
AND (Overtime.start_date BETWEEN EmployeesGrades.start_date AND
IIF(EmployeesGrades.end_date IS NULL, NOW(), EmployeesGrades.end_date))
;
CREATE VIEW OvertimeDetails
(EmployeeID, overtime_start_date, overtime_end_date, position_grade,
overtime_rate)
AS
SELECT OvertimeGrades.EmployeeID, OvertimeGrades.overtime_start_date,
OvertimeGrades.overtime_end_date, OvertimeGrades.position_grade,
OvertimeRates.overtime_rate
FROM OvertimeGrades LEFT JOIN OvertimeRates
ON OvertimeGrades.position_grade = OvertimeRates.position_grade
AND (OvertimeGrades.overtime_start_date BETWEEN
OvertimeRates.start_date AND IIF(OvertimeRates.end_date IS NULL, NOW(),
OvertimeRates.end_date))
;
INSERT INTO Employee (EmployeeID) VALUES (1)
;
INSERT INTO Employee (EmployeeID) VALUES (2)
;
INSERT INTO Grades (grade) VALUES ('5.1')
;
INSERT INTO Grades (grade) VALUES ('5.2')
;
INSERT INTO Grades (grade) VALUES ('7.1')
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date,
end_date) VALUES ('5.1', 100, #2004-01-01#, #2004-12-31#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date,
end_date) VALUES ('5.2', 120, #2004-01-01#, #2004-12-31#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date,
end_date) VALUES ('7.1', 200, #2004-01-01#, #2004-12-31#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date)
VALUES ('5.1', 110, #2005-01-01#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date)
VALUES ('5.2', 150, #2005-01-01#)
;
INSERT INTO OvertimeRates (position_grade, overtime_rate, start_date)
VALUES ('7.1', 210, #2005-01-01#)
;
INSERT INTO EmployeesGrades (EmployeeID, position_grade, start_date,
end_date) VALUES (1,'5.1',#2003-09-01#,#2005-03-31#)
;
INSERT INTO EmployeesGrades (EmployeeID, position_grade, start_date)
VALUES (1,'5.2',#2005-04-01#)
;
INSERT INTO EmployeesGrades (EmployeeID, position_grade, start_date,
end_date) VALUES (2,'5.2',#2004-04-30#,#2004-11-30#)
;
INSERT INTO EmployeesGrades (EmployeeID, position_grade, start_date)
VALUES (2,'7.1',#2004-12-01#)
;
INSERT INTO Overtime (EmployeeID, start_date, end_date) VALUES (1,
#2004-10-01 17:00:00#, #2004-10-01 20:00:00#)
;
INSERT INTO Overtime (EmployeeID, start_date, end_date) VALUES (2,
#2004-10-01 17:00:00#, #2004-10-01 20:00:00#)
;
INSERT INTO Overtime (EmployeeID, start_date, end_date) VALUES (1,
#2005-10-01 17:00:00#, #2005-10-01 20:00:00#)
;
INSERT INTO Overtime (EmployeeID, start_date, end_date) VALUES (2,
#2005-10-01 17:00:00#, #2005-10-01 20:00:00#)
;
SELECT EmployeeID, overtime_start_date, overtime_end_date,
position_grade, overtime_rate
FROM OvertimeDetails
ORDER BY overtime_start_date, position_grade
;
Click to expand...
 
P

peregenem

NWO said:
Thank you so much for the time you put into this response.
Click to expand...

No sweat, just something for me to do in a coffee break (OK, so the
coffee went cold on this occasion said:
I am
trying to avoid code at all cost
Click to expand...

I'll just point out that this code *describes* the schema (i.e. run it
once only to create the structure). You could build an equivalent
schema using e.g. Access's GUI tools. It's not a code *solution* as
such.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table Foreign Key. Access 2003, Windows XP. 7
Need to select record based on date 5
What tables do I need? 4
Six of one, half dozen? 13
database setup 1
identify employees that have/haven't taken certain training course 2
Unable to append due to key violations 1
Saving a value in another cell 1

Top