Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Timesheets and Holidays

Discussion in 'Information Technology' started by David M C, Jul 28, 2006.

  1. David M C

    David M C
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Row over school closures for Muslim Hindu and Sikh holidays Breaking News Oct 18, 2009
    FAQs for Students Do Sikh celebrate holidays? Questions and Answers Aug 17, 2009
    Happy Holidays ! Spiritual Articles Dec 12, 2006
    Sikh News Schools Juggle Holidays for Other Faiths (ABC News) Breaking News Jul 21, 2006
    Sikh News Schools juggle holidays for Muslims, others (The Canton Repository) Breaking News Jul 21, 2006

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter


    "David M C" <DavidMC@discussions.microsoft.com> wrote in message
    news:81FCFB3A-00DD-48FF-A118-94F2AEE4CCD4@microsoft.com...
    > 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
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter


    "David M C" <DavidMC@discussions.microsoft.com> wrote in message
    news:81FCFB3A-00DD-48FF-A118-94F2AEE4CCD4@microsoft.com...
    > 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




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  5. Craig Alexander Morrison

    Craig Alexander Morrison
    Expand Collapse
    Guest

    You do know your answers are showing up twice, don't you?
     
  6. Craig Alexander Morrison

    Craig Alexander Morrison
    Expand Collapse
    Guest

    You do know your answers are showing up twice, don't you?
     
  7. Craig Alexander Morrison

    Craig Alexander Morrison
    Expand Collapse
    Guest

    You do know your answers are showing up twice, don't you?
     
  8. Craig Alexander Morrison

    Craig Alexander Morrison
    Expand Collapse
    Guest

    Ironic this!

    >>>I am using the free version of SPAMfighter for private users.
    >>>It has removed 4367 spam emails to date.
    >>>Paying users do not have this message in their emails.


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

    --
    Slainte

    Craig Alexander Morrison
    Crawbridge Data (Scotland) Limited

    "Pieter Wijnen"
    <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
    wrote in message news:ukqoRfVsGHA.2376@TK2MSFTNGP04.phx.gbl...
    > check http://www.mvps.org/access/datetime/index.html
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter

    "Craig Alexander Morrison" <cam@microsoft.newsgroups.public.com> wrote in
    message news:ubGVbJWsGHA.3480@TK2MSFTNGP04.phx.gbl...
    > You do know your answers are showing up twice, don't you?
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  10. David M C

    David M C
    Expand Collapse
    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


    "Pieter Wijnen" wrote:

    > check http://www.mvps.org/access/datetime/index.html
    > for how to handle holidays
    >
    > Pieter
    >
    >
    > "David M C" <DavidMC@discussions.microsoft.com> wrote in message
    > news:81FCFB3A-00DD-48FF-A118-94F2AEE4CCD4@microsoft.com...
    > > 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

    >
    >
    >
     

Share This Page