Cleaning up a table

[RichO]

I'm looking for help on using a delete query to remove old records from a table.

We have one table with all of our employee information and a second table containing their pay scale. The two tables are linked by the employee's social security number.

When we remove an employee from the system, their pay data still remains in the second table and it is somewhat cluttered with outdated data. I have since changed the form to remove the pay information upon a delete but I'm now looking for a way to get rid of the old data. I would imagine I can easily do this with a delete query but I am missing a piece of the puzzle...


DELETE * FROM tblEmployeePay WHERE tblEmployeePay.EmployeeID (... does not exist in tblEmployees)


Thanks.

 

[pbaldy]

Try

DELETE * FROM tblEmployeePay WHERE tblEmployeePay.EmployeeID Not In(SELECT EmployeeID FROM tblEmployees)

 

[Pat Hartman]

I have since changed the form to remove the pay information upon a delete but I'm now looking for a way to get rid of the old data

- this is not necessary at all if you define the relationship between the two tables properly. Go to the relationships window and add both tables. Draw a line connecting the two related fields to create a relationship. In the dialog, make sure the correct fields are matched up in case you were a little off with your line. Then select the enforce RI checkbox and that will enable the cascade delete checkbox which you should also select. Now when you delete a record from the 1-side table, ALL related rows from the many-side will also be deleted. This method will prevent orphan records from being added or created by deleting their parent. You will need to remove any existing orphans before Jet will allow you to enforce RI though.

 

[RichO]

That did the trick, pbaldy. Thanks.

Although I didn't check, I was under the impression that the tables' relationships were properly defined. I didn't originally develop this database but I will check that out.

Thanks again.

 

[RichO]

Regarding form relationships, I attempted to establish the relationship between the two tables. The line links them correctly, but the Enforce RI and Cascade Delete options are not enabled.

Also, even though the relationships options box lists this as a one to many, the line between the 2 tables does not indicate this. It shows as just a plain line.

 

[Pat Hartman]

The primary keys are not properly defined in one or both the tables.

 

[RichO]

These are SQL Server tables. The table holding the "many" in the relationship can't have a primary key assigned to it because of duplicates, but SQL server tables require a primary key. Currently there are keys/indexes assigned to 2 fields in the "many" table. This is probably why the original developer did not set up the table relationships.

 

[Pat Hartman]

Relationships can ONLY be established in the source database. You can open the relationship window in your Access db and draw pretty lines connecting linked tables but they don't do anything. So, to establish relationships and enforce RI, you MUST do this in the SQL server database. You CANNOT do it via the Access .mdb interface. You MAY be able to do it if you create an .adp. But the .adp does not support full GUI access to the SQL server database. As a final alternative, you can create DDL (Data Definition Language) to Alter the table and add constraints. Don't forget that the DDL must be of the SQL Server variant rather than the Jet variant so you'll need a reference specifically for TSQL. You can run the query as a pass-through query from Access.

 

[raskew]

Is there never a need to provide historical information?

The idea of deleting an employee's data once he/she

leaves the organization scares me to death. Yeah, suppose

you could always go back to the paper-file (provided you've

retained it), but that just negates the automation aspects

and returns to a labor-intensive physical search.


An easier way is have both HireDate and TerminationDate

fields. Current employees are identified by specifying

IsNull(TerminationDate).


Guess it depends on an organization's circumstances, but

I've been asked (in response to EEO complaints from

disgruntled former employees) to provide racial and salary

breakouts for the past 1 to 5 years. Can't imagine doing

that when the data has been deleted. It'd be ugly!


Just food for thought-

Bob

 

[RichO]

We don't actually delete the payroll information for the employees. Just their general information and their pay scale, which just tells us how much they make when they work (there is a different rate for weekdays, weekends, seasons, etc). We also keep paper copies of their information just in case we ever need it access their information.

 

[raskew]

When we remove an employee from the system, their pay data still remains in the second table and it is somewhat cluttered with outdated data. I have since changed the form to remove the pay information upon a delete but I'm now looking for a way to get rid of the old data.

Rich -

I'd have to say that 'somewhat cluttered' is often in the mind of the beholder. It's kind of like the folks who write in with their problem that the report that they run only once a month takes 2150 millesconds to process, when they really think it should really run in 1000 milleseconds. Bottom line, who cares? If this process is so painful, go get a cup of coffee, smoke a cigarette, or whatever. In your situation, is the hard-drive overloaded, filled-up, hiccupping, whatever? We're talking millesconds here--tell us how your schedule is so important that you can't delay a second or two.

Persuade your company to put out a few bucks for a bigger hard-drive--they've never been cheaper. The investment will be more than repaid the first time there's a need to resurrect previously captured (but now deleted) supposedly 'extraneous' data.

Bob