Timesheets and Holidays

[Guest]

Here's my table structure:

tblTask:

TaskID (pk)
TaskName
LabourPrice

tblTransaction:

TaskID (fk)
TransactionID (pk)
Authorised (where authorised is the amount paid)
EmployeeID (fk, related to Employees table with usual info)
TransactionDate
TransactionTypeID (fk, related to table with different types, Daywork, Price
Work etc)

Every task has a price. Employees book against a task. So if "Paint House"
is £100, and the employee completed 50% of the work, he will get paid £50.
Sometimes, if an employee is working on daywork, the amount paid will go over
the labour price.

However, all this is irrelevant, in as much as the amount the employee is to
be paid is stored in [Authorised]. All the transactions relating to how much
an employee is to be paid is stored in tblTransaction and always relates back
to a specific task. Now for the question.

How could I handle holidays?

I'm able to generate a timesheet showing what the employee is due to be
paid, based on the entries in tblTrnascation, but am not sure how to show
that a days holiday was taken. It would be easy if any weekday not worked
counted as a holiday, but this is not the case.

Would a seperate table for booking holidays be the way to go? How would I
then link this table (with the HolidayDate field), to the TransactionDate
field?

Any ideas for other ways to handle it would be appreciated.

Thanks,

Dave

 

[Pieter Wijnen]

check http://www.mvps.org/access/datetime/index.html
for how to handle holidays

Pieter

 

[Pieter Wijnen]

check http://www.mvps.org/access/datetime/index.html
for how to handle holidays

Pieter

Here's my table structure:

tblTask:

TaskID (pk)
TaskName
LabourPrice

tblTransaction:

TaskID (fk)
TransactionID (pk)
Authorised (where authorised is the amount paid)
EmployeeID (fk, related to Employees table with usual info)
TransactionDate
TransactionTypeID (fk, related to table with different types, Daywork,
Price
Work etc)

Every task has a price. Employees book against a task. So if "Paint House"
is £100, and the employee completed 50% of the work, he will get paid £50.
Sometimes, if an employee is working on daywork, the amount paid will go
over
the labour price.

However, all this is irrelevant, in as much as the amount the employee is
to
be paid is stored in [Authorised]. All the transactions relating to how
much
an employee is to be paid is stored in tblTransaction and always relates
back
to a specific task. Now for the question.

How could I handle holidays?

I'm able to generate a timesheet showing what the employee is due to be
paid, based on the entries in tblTrnascation, but am not sure how to show
that a days holiday was taken. It would be easy if any weekday not worked
counted as a holiday, but this is not the case.

Would a seperate table for booking holidays be the way to go? How would I
then link this table (with the HolidayDate field), to the TransactionDate
field?

Any ideas for other ways to handle it would be appreciated.

Thanks,

Dave

--

 

[Craig Alexander Morrison]

You do know your answers are showing up twice, don't you?

 

[Craig Alexander Morrison]

You do know your answers are showing up twice, don't you?

 

[Craig Alexander Morrison]

You do know your answers are showing up twice, don't you?

 

[Craig Alexander Morrison]

(g)

 

[Craig Alexander Morrison]

Don't you?

 

[Craig Alexander Morrison]

Ironic this!

Looks like your spamfighter is spewing spam all over these newsgroups.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

"Pieter Wijnen"

 

[Pieter Wijnen]

yepp, I think I need to remove SpamFighter & get my act together

Pieter

You do know your answers are showing up twice, don't you?

--

 

[Guest]

Thanks, but that is not quite what I need.

I have now moved forward and added a Holdidays table:

tblHolidays:

HolidayID (pk)
EmployeeID (fk)
HolidayDate


Here is my current query for timesheets, this works very well:

SELECT tblGroup.GroupID, tblLocation.LocationID, tblTask.TaskID,
tblTransaction.TransactionID, tblTransaction.TransactionDate,
tblTransaction.TransactionTypeID, tblTransaction.EmployeeID,
tblTransaction.Authorised, KTFJobs.JobNumber
FROM ((tblGroup INNER JOIN KTFJobs ON tblGroup.JobNumber =
KTFJobs.JobNumber) INNER JOIN tblLocation ON tblGroup.GroupID =
tblLocation.GroupID) INNER JOIN (tblTask INNER JOIN tblTransaction ON
tblTask.TaskID = tblTransaction.TaskID) ON tblLocation.LocationID =
tblTask.LocationID
WHERE (((tblTransaction.TransactionDate) Between [Date1] And [Date2]));

I now need to somehow show, for every employee, in a single query, all the
information from tblTransaction as shown above, plus any holidays on a given
date. In the end I aim to have a report that shows:

Employee: Ben Holmes

Date Description Type Pay/Deduct
JobNumber

17/07/06 Blah de Blah Scheduled 30
1558
18/07/06 Blah Scheduled 40
1558
19/07/06 Holiday
20/07/06 Bleh Scheduled 30
1558

TOTAL 100
HOLIDAY 1 DAY

On the report, [Description] is the TaskName (and various other bits of
information from related tables that isn't relevant here). If it isn't
possible to have this information displayed as one, I will have to add a
subreport to show holidays.

Ideas?

Thanks,

Dave

check http://www.mvps.org/access/datetime/index.html
for how to handle holidays

Pieter

Here's my table structure:

tblTask:

TaskID (pk)
TaskName
LabourPrice

tblTransaction:

TaskID (fk)
TransactionID (pk)
Authorised (where authorised is the amount paid)
EmployeeID (fk, related to Employees table with usual info)
TransactionDate
TransactionTypeID (fk, related to table with different types, Daywork,
Price
Work etc)

Every task has a price. Employees book against a task. So if "Paint House"
is £100, and the employee completed 50% of the work, he will get paid £50.
Sometimes, if an employee is working on daywork, the amount paid will go
over
the labour price.

However, all this is irrelevant, in as much as the amount the employee is
to
be paid is stored in [Authorised]. All the transactions relating to how
much
an employee is to be paid is stored in tblTransaction and always relates
back
to a specific task. Now for the question.

How could I handle holidays?

I'm able to generate a timesheet showing what the employee is due to be
paid, based on the entries in tblTrnascation, but am not sure how to show
that a days holiday was taken. It would be easy if any weekday not worked
counted as a holiday, but this is not the case.

Would a seperate table for booking holidays be the way to go? How would I
then link this table (with the HolidayDate field), to the TransactionDate
field?

Any ideas for other ways to handle it would be appreciated.

Thanks,

Dave