Best way to monitor attendance (1 Viewer)

Hi
I am in the middle of creating a database to control our contractors.
Creating tables for the companies and contractors details and forms for the inputting of this data.
Also I populate a field on the contractors record to show the last time they were on site.
What I now want to do, is somehow log the date of each and every time they have been to site, so that I can see how many times and the dates, each of the contractors have been on site during a dynamic time period.

I am not sure how the table(s) structure would look for this sort of data.

Any help would be appreciated

As you said, you just need to add a record EACH time the contractors are on site

Then use an aggregate query to COUNT the number of dates on site for each contractor within a specified date range

Do I do this in a separate table?
At present my tblContractorsDetails has the following fields
ID
LastName
FirstName
CompanyID_FK
InductionDate
LastDateOnSite

At present when they attend site, I press a button on a form that updates that record LastDateOnSite with Date()

So not sure how to record each date with regards to a new table structure

In theory you could do this in the existing table by just changing the LastDateOnSite field to DateOnSite.

However this would give you lots of unnecessary duplication

You say CompanyID is a FK so presumably there is another table tblContractors with that as the PK.

Suggest you modify tblContractorsDetails so it has fields:
ID
LastName
FirstName
CompanyID
InductionDate

Or the above fields could be included in the original tblContractors table

Create a new table tblContractorsVisits with fields:
ID (autonumber PK)
CompanyID
SiteVisitDate

Then change the code for your button from an update query to an append query:


To get the number of visits within a date range have 2 text boxes on a form txtStartDate & txtEndDate:

Then run a query similar to this:


If you use this in VBA code rather than a query, you'll need to wrap these dates in # delimiters. If not based in the US, you may also need to format the dates in US format mm/dd/yyyy

I have a table that has work days, tWorkDates, every day what was worked.
and a table for attendance, tAttend
EmpID
Date
Status (present, absent,excused)
memo (explain absent if needed)

this table is joined to tEmployee to get names.
data (dates) can be entered via the Date of work, where a query appends all workers to the
tAttend table , then a user sets Present, Absent, etc.

or viewed via employee record detail in a parent form/subform screen.

Bilbo1161 - since you claimed to be unsure about how to structure your attendance records, may I respectfully suggest doing some reading on the topic of database normalization and one-to-many (or parent/child) relationships?

Your contractor list would be a parent table. The attendance records, one per day per contractor (thus, many per contractor), would be your child table. If you look at the excellent suggestions from Ridders and Ranman, you would see these concepts at play.

Thanks very much for your answers, they are exactly what I was looking for.

I certainly will do lots more reading.
Its a shame there isn't 48hrs in a day