mistake

[rich]

Hello,

I discovered a mistake in the design of my database. I have the same data
stored in two different tables, which happen to have a parent/child
relationship. I believe the data should really be in the child table, but
the parent table happens to be similar to an employee_master table so I can't
really change it.

I was wondering if Access supports triggers, or a similar concept. Is so,
I'm thinking that anytime the two fields in question change in the parent
table, the changes can also be made to the child.

Any ideas?

TIA,
Rich

 

[Jerry Whittle]

Access doesn't support triggers. I wish it did. I'd also like to see Sequences.

However if you do all your data input through a from, you could do something
similar with the appropriate event such as On Update or After Update.

 

[Pat Hartman \(MVP\)]

The sooner mistakes are corrected, the better.

 

[Jamie Collins]

I discovered a mistake in the design of my database. I have the same data
stored in two different tables, which happen to have a parent/child
relationship. I believe the data should really be in the child table, but
the parent table happens to be similar to an employee_master table so I can't
really change it.

I was wondering if Access supports triggers, or a similar concept. Is so,
I'm thinking that anytime the two fields in question change in the parent
table, the changes can also be made to the child.

Any ideas?

My advise also is to take the hit and correct the design flaw sooner
rather than later.

If you are concerned about breaking existing apps, consider renaming
the employee_master table and creating a VIEW (Access Query object
defined using a non-parameterized SELECT query) named employee_master
which *queries* (rather than stores) the required data. Here's a rough
sketch -- structure only, no constraints etc (ANSI-92 Query Mode SQL
code):

Before:

CREATE TABLE employee_master
(
employee_number CHAR(10) NOT NULL UNIQUE,
current_salary_amount DECIMAL(12, 4) NOT NULL
)
;
CREATE TABLE EmployeeSalaryHistory
(
employee_number CHAR(10) NOT NULL,
start_date DATETIME NOT NULL,
UNIQUE (employee_number, start_date),
end_date DATETIME ,
salary_amount DECIMAL(12, 4) NOT NULL
)
;

After:

CREATE TABLE Employees
(
employee_number CHAR(10) NOT NULL,
)
;
CREATE VIEW employee_master
AS
SELECT DISTINCT E1.employee_number,
H1.salary_amount AS current_salary_amount
FROM Employees AS E1
LEFT JOIN EmployeeSalaryHistory AS H1
ON E1.employee_number = H1.employee_number
WHERE H1.end_date IS NULL
;

The bad news is that the resultant VIEW is not updateable (oh for
INSTEAD OF triggers for VIEWs <g>) and the chances are that renaming
the base table will cause more problems than it would solve.

Jamie.

--

 

[rich]

Thanks everyone for replies.

I went ahead and corrected the design flaw. It was not as bad as I thought
it would be.

 

[Jamie Collins]

Thanks everyone for replies.

I went ahead and corrected the design flaw. It was not as bad as I thought
it would be.

Good work!

Jamie.

--