[Solved] Employee Punch Clock DB

[justintree3]

Hey,
I am trying to put together a database for use as a punch clock, but it has been years since I have dabbled in the world of databases, and most of my experience was in MS Access.

What I want is for there to be a form, where an employee types their ID number, clicks a "punch in" or "punch out" button, and it saves the timestamp.

What I think I need:
Two tables, one for employees, one for time stamps.
A form for admins to enter employee info.
The above mentioned punch clock form

If I am correct, I would want the time stamp table to have a multi-field primary key, a time stamp ID and the employee ID (linked to the employee ID on the employee table), but I am not sure how to go about programming the buttons to populate the fields with the current date/time. I am also not sure how to add protection against things like signing out without first signing in, signing in twice, etc.

[Villeroy]

Unless you use some other database engine such as MySQL, THIS is your database engine if the status bar of your document reads "Embedded Database HSQL database engine".

And this is the database I would start with:
menu:Tools>SQL...

Code: Select all

CREATE TABLE PERSONS(
      N VARCHAR_IGNORECASE(32) NOT NULL,
      ID INTEGER GENERATED BY DEFAULT AS IDENTITY
);
CREATE VIEW "vPresent" AS
      SELECT P.N, I.TS 
      FROM PERSONS AS P, INCOMING AS I LEFT JOIN OUTGOING AS O ON I.ID=O.INID 
      WHERE O.INID IS NULL AND P.ID=I.PID;
ALTER TABLE PERSONS ADD CONSTRAINT "uniqPerson" UNIQUE (N);
CREATE TABLE INCOMING(
      TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PID INTEGER NOT NULL,
      ID INTEGER GENERATED BY DEFAULT AS IDENTIT,
      FOREIGN KEY (PID) REFERENCES PERSONS(ID)
);
CREATE TABLE OUTGOING(
      TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INID INTEGER NOT NULL,
      PRIMARY KEY (INID),
      FOREIGN KEY (INID) REFERENCES INCOMING(ID)
);

menu:View>Refresh Tables

Now you have a list of persons with unique case-insensitive names, a list of incoming events and one outgoing event for each incoming events with automatic time stamps plus a view which shows the names of currently present persons.
An ingoing form would add a new record to the ingoing table by means of a list box offering all persons that are not in "vPresent" yet.
An outgoing form would have a main form of "vPresent" where you pick the person to log off, and then adding a record to the outgoing table by means of a subform.

[justintree3]

Thanks, that got me started.

How do you populate a list box with items that are not in a view? Having some trouble with that...

[MTP]

List boxes are usually populated by a SELECT statement (no need for views). Are you looking for help writing the SQL code? Or looking for help navigating the GUI to find the field where the SQL is entered?

[justintree3]

I found the field, but my problem lies in trying to do what Villeroy said, populating the box with people who are not in vPresent (so, using the table to populate the items that are not in a view).

I suspect my biggest problem is syntax, as I have not used SQL in about a decade, and (as mentioned) I learned in MS Access. What I have been trying is to compare the PERSONS.N column with the vPresent.N column, and where it does not equal, populate. I have tried a variety of methods, the latest being this:

Code: Select all

SELECT CASE "PERSONS.N" WHEN "PERSONS.N" != "vPresent.N"

The debugger says it is expecting THEN or OR.

I have also tried this:

Code: Select all

SELECT "PERSONS.N" FROM "PERSONS" WHERE "nPresent.N" != "PERSONS.N"

That doesn't give any direct syntax errors, but when opening the form it gives a "Column not found" error for PERSONS.N.

[Villeroy]

Data properties of a list box showing not present persons to be entered into the incoming table:

Linked Field: PID
Content Type: SQL
Content: SELECT "P"."N", "P"."ID"
FROM PERSONS AS "P", "INCOMING" AS "I" LEFT JOIN "OUTGOING" AS "O" ON "I"."ID"="O"."INID"
WHERE NOT "O"."INID" IS NULL AND "P"."ID"="I"."PID"
ORDER BY "P"."N"
Bound Field: 1 (which is the ID field. the visible N is field #0)

The above is untested.

[Villeroy]

I'm sorry. The first version of my embedded HSQLDB was not finished. One person had downloaded that file so far. Here is the one with an almost working "Departure" form. It reveals a one of Base's weak points. In order to get a "dirty record" you've got to change something manually in the form. So I added a meaningless boolean field to the table and subform.
You select a present person in the grid, click the check box, then you can click the save button. Finally you refresh the parent form. A macro can reduce the procedure from 3 clicks to one click.

[justintree3]

Thanks, that did it! All that's left is tweaking the appearance and doing up some reports.