Question Brain Overload - Need a database

[ross135]

Hi guys.

I have been tasked with a project of making a fingerprint "punch clock" system, or payroll system. I have developed the application in vb.net, and I store the enrolled users in an ms access database. In this database I also store whether the employee is currently logged in or out.

Up to this point I have everything working, as a person clocks in it displays their name and whether they have clocked in or out. How ever I'm now stuck. I have to store this event somehow, and show a print out every week.

Not only the accumulated hours have to be stored, but details of the times they clocked in/out.

At first I was thinking of an access database (as my database knowledge is limited and access is what I have on hand) but further thinking got me to realise its more complex than I thought and i couldn't figure out what fields I could use in the table.

My second option was to just log them straight to a text file sequentially for example:

Tom Jones Mon Clocked In 07:43
Tom Jones Mon Clocked Out 10:54

etc etc.

Just thought I would get some more experienced views As the text file approach may be hard to get perfect as I can already think of problems such as it getting very muddled up.

If this is vague I will happily elaborate, if it is posted in the wrong section I apologise.

Thanks in advance,
Ross

 

[Hack]

You already have an Access database and at least one table in it.

It sounds like you simply need to add a couple of date/time fields for ClockedIN and ClockedOUT

Unless I'm missing something, of course...

 

[Tom]

A new table would be needed to store employee hours since each employee will have multiple records. I would definitely keep this in a database since your already needing reporting features and that usually has a tendency to grow... I would however suggest considering using Sql Server over Access though.

 

[cjard]

Table:

PersonID, ClockInDate, ClockOutDate
Tom Jones, 2009/12/01 09:00:00, 2009/12/01 17:00:00


In or Out?
SELECT CASE WHEN ClockOutDate IS NULL THEN 'IN' ELSE 'OUT' END as InOrOut FROM clocking WHERE personId = the person id AND ClockInDate = (SELECT MAX(ClockInDate) FROM clocking WHERE personID = person id)

Hours Worked each week:
SELECT personID, SUM(ClockOutDate - ClockInDate) * 24 FROM clocking WHERE clockInDate BETWEEN last week commencing AND this week commencing GROUP BY personID

The italic bits are values you supply.

DO NOT put values in there by concatenating strings together - read the PQ link in my signature, and the DW2 link, section Creating a Simple Data App

Use parameters!