Update/Append/AddColumn

[Guest]

I routinely run a left sided update query on a dataset imported from an ASCII
file. This appends new employees to a Master Employee Table and updates any
changes in base rate pay based on their hours worked and gross wage. The
Overtime Rate, which can change every pay period based on shifts and
differentials etc., is also calculated based on Hours Worked and Gross
Overtime Wage.

I would like to preserve each pay period's wage rates.

Can anyone suggest code that will add a new column each time I run the
query, one for NewBaseRate and one for NewOvertimeRate? If there is a
suggestion for a different method of accomplishing the same thing,
please....offer, I am just trying to get the job done. Thank you in advance.

 

[Guest]

My suggestion would involve a bit of redesign, but I think that might be
necessary to accomplish what you want.

Instead of your existing update query, which overwrites the wage rate info,
add a field to indicate if a record is active or inactive (i.e. archived).
Then when doing the updates, instead of overwriting the wage info mark the
record as inactive and then do an append of the new record (as well as any
new employees) - this probably would take 2 queries; the first one to do the
update and the second to append.

Then set any query, form, or report that uses the table to filter for only
the active records (this would be the redesign part - how difficult depends
on how many things need to be changed to deal with the new way of using the
table).

An alternative - not involving redesign - would be to keep a second table as
your archive and move the old records in there (with an append query) rather
than "deactivating" them in the original table. Then do your updates and
appends as you are currently doing.

 

[Guest]

The active/inactive idea is a good one but I am afraid that with the size of
business and number of different employee situations there are it would be
impractical. The redesign would be a huge challenge.

I am just not having luck in finding a way to add a new field/column to my
table.

 

[Guest]

That was the main concern I had. But if you want to retain all the old
information in the existing table, you need some way of "flagging" records
that are no longer applicable or they will appear in all reports, etc. (i.e.
if you ran a list of employees, the same person could appear several times).
And you need to plan for an unknown number of changes per person, so you
can't simply have one field for "prior wage rate" or anything like that. No
matter how you do it, I am afraid that trying to keep the old records in your
table will lead to problems with existing queries and reports.

Perhaps, then, having a separate archive table would be the way to go. Keep
everything - even your update/append query - the way it is, but before you
run your query find any matching employee records by joining your imported
records to the existing employee list by some unique match (e.g. employee
number) and use the result as an append query that adds the OLD record values
to your archive table. Once these records are archived you can perform your
normal update/import. This will assure that any employee info that got
overwritten has its prior value stored in the archive (you may want or need
to add a date field to the archive to indicate the date it was archived; this
would give you a way of tracking the changes by date).

If you ever needed to use the old and new records together in a query or
report, you could do it by using a Union query.

This approach has the advantage of not changing any of your current tables,
queries, reports, or anything. It just adds a new table (the archive) and
one extra step to your import.

 

[Guest]

Ok, Thanks for your help, I'll append records every two weeks for all
employees to an archive table rather than create a field every two weeks for
the current information, if that's the best way to go

 

[JC]

By the way, I too work with data analysis at a large hospital small
world, thanks again.