Timestamp for Append Query

Question

Hi All,

I need to run an append query weekly to add my new hires from one table to my job history table.  This is probably redundant but I'm still learning and have gone too far to correct it at this point.  Anyways, when I run the append query, I only want the new records.  I did some research that said to create a timestamp.  This sounds like what I need but I was confused about what to put where, such as where to put my DateTime field and what to add to the append query and form.

But then again I could be going about this all wrong but my objective is the same:  to only add new records that are entered into the form.

Thanks for any help.

Answer 1

Why not have a table for all positions rather than just the vacant ones.  You could include a Boolean (Yes/No) column, Current, for instance to indicate current positions and set its value to false if a position is abolished.  That way you can keep track of past and present positions.  In the Employees table you'd them have a foreign key column, PositionID say, referencing the primary key of the Positions table.

The total number of current positions can be returned with:

SELECT COUNT(*)

FROM Positions

WHERE Current;

Occupied positions with:

SELECT COUNT(*)

FROM Positions INNER JOIN Employees

ON Positions.PositionID = Employees.PositionID

WHERE Current AND Status <> "Terminated";

Vacant positions with:

SELECT COUNT(*)

FROM Positions

WHERE Current

AND NOT EXISTS

    (SELECT *

     FROM Employees

     WHERE Status <> "Terminated"

     AND Employees.PositionID = Positions.PositionID);

Ideally you should have a means of preventing more than one row being inserted into the Employees table with the same PositionID where the employee's Status is not 'Terminated'.  This can't be done with an index as it would be legitimate to have multiple rows with same PositionID and a Status value of 'Terminated'.  It can be done by applying a CONSTRAINT to the Employees table, however.  This is done by executing a DDL statement, DDL (data definition language) being that part of SQL used for creating or amending table definitions.  In Access, however, this can't be executed directly from the query designer as you would a normal SQL 'action' query, but must be done with code, which can simply be entered in the debug window (aka 'immediate' window).  In this case it would be:

CurrentProject.Connection.Execute "ALTER TABLE Employees ADD CONSTRAINT EmployeePosition CHECK((SELECT COUNT(*) FROM Employees WHERE Status <> "Terminated" GROUP BY EmployeeID, PositionID HAVING COUNT(*) > 1) IS NULL)"

Enter this as a single line.  It should prevent the same position being occupied by two employees simultaneously (unless you have job sharing of course).  I think I've got the logic right, but if it doesn't work you can remove the constraint with:

CurrentProject.Connection.Execute "ALTER TABLE Employees DROP CONSTRAINT EmployeePosition"

Answer 2

You can add a criterion such as

>= Date()

to the Timestamp field on your append query, to include only those records with a timestamp later than midnight last night; or any other suitable criterion. My concern is that this is only a rough guide to "what needs to be added". What if you added some records via the form today, and didn't get around to running the append until tomorrow? What if you need to enter some backdated records?

These are just SOME of the anomalies and problems that arise when you store data redundantly!

One idea might be to have a unique Index - on the EmployeeID or some other suitable field or combination of fields - in the job history table to prevent duplicate records from getting into the table in the first place; but I don't know the purpose or structure of the table so I can't say just what would do the job.

Answer 3

This does indeed answer my question.  The suggested criteria works.  I appreciate your additional words of wisdom, which have set me in the direction to redo my database.  Hopefully it won't take too long.  I can see this timestamp being useful for other projects.  Thanks again!

Answer 4

Thank you John.  I did not know that Timestamp was a reserved word.  With your help, I was able to add a timestamp.  I created the WhenCreated field and added a text box to my form as you instructed.  I was not sure if I also needed to add Now() to the criteria of my append query?  I tried doing so but no records returned which would make sense because Now has passed:)

So, I took Now() out of my append query criteria and the query returned all records.  But I don't want all records, I just want the newly created records (the new hires).  For example, if I add two new hires today and ran my append query, it would return only two records and not the records I created last week or before.  How can I do this?

Yes, I hope to fix my data redundancy problem in the future but I'm pushed for time.  I hope I can do so without too much of a hassle.  What I said about adding new records to the form, I meant to say to the table via a form.

Answer 5

It's easy to add a timestamp field to a table; just include a Date/Time field (WhenCreated or something similar, don't use a reserved word like Timestamp or Time or Date though). In your Append query include a calculated field by typing:

WhenCreated: Now()

in a vacant Field cell, and appending it to this field.

It's just as easy on a Form; just bind a textbox to the WhenCreated field and set that textbox's DefaultValue property to =Now().

However - I'm perplexed at what you're doing. It certainly sounds redundant to have the same data in two tables, and I hav no idea what you mean by "only add new records... into the form". Data isn't "added to" a form; a form is just a window displaying data stored in a Table. Am I misinterpreting?