total between two fields

[Clan McCreery]

To clarify an earlier post, I'm trying to get the total
of two fields with like information, based on the
following table structure in my employee time clock
database:

tblSchedule
[EmployeeID] - joined to tblEmployees
[ScheduleTimeIn] - date/time, short time
[ScheduleTimeOut] - ""
[ClockTimeIn] - ""
[ClockTimeOut] - ""
[PayStatus] - Text from tblPayStatus
[AlternatePayStatus] - ""

I have a query behind the "time clock" form, with the
following additional fields:

[ScheduleTotal] = total of hours in schedule
([ScheduleTimeIn] - [ScheduleTimeOut]

[ClockTotal] = [clockTimeOut] - [ClockTimeIn]

[Difference] = [ScheduleTotal] - [clockTotal]

On monday an employee will call in sick all day, so the
[PayStatus] = "paid time off"
On Tuesday, the employee will clock in for say 5 hours
with [PayStatus] = "regular pay", then clock out sick
with the remaining 3 hours of [AlternatePayStatus]
= "paid time off".

How do a run a query that will combine the data from both
[PayStatus], [ScheduleTotal] and [AlternatePayStatus],
[Difference] to show that this employee has a pay summary
of:

Regular pay = 8 hours
Paid time off = 11 hours

Thanks!!!!!

Clan McCreery

 

[Steve Schapel]

Clan,

Frankly, this is more difficult than it needs to be because of the
same category of data (pay status) being stored in 2 different fields.
Is there any chance of the table design being modified?

- Steve Schapel, Microsoft Access MVP

 

[Clan McCreery]

Sure, the table could be modified.....I'm assuming you
have a good idea that I have missed along the way?

Thanks so much!

Clan McCreery

-----Original Message-----
Clan,

Frankly, this is more difficult than it needs to be because of the
same category of data (pay status) being stored in 2 different fields.
Is there any chance of the table design being modified?

- Steve Schapel, Microsoft Access MVP

To clarify an earlier post, I'm trying to get the total
of two fields with like information, based on the
following table structure in my employee time clock
database:

tblSchedule
[EmployeeID] - joined to tblEmployees
[ScheduleTimeIn] - date/time, short time
[ScheduleTimeOut] - ""
[ClockTimeIn] - ""
[ClockTimeOut] - ""
[PayStatus] - Text from tblPayStatus
[AlternatePayStatus] - ""

I have a query behind the "time clock" form, with the
following additional fields:

[ScheduleTotal] = total of hours in schedule
([ScheduleTimeIn] - [ScheduleTimeOut]

[ClockTotal] = [clockTimeOut] - [ClockTimeIn]

[Difference] = [ScheduleTotal] - [clockTotal]

On monday an employee will call in sick all day, so the
[PayStatus] = "paid time off"
On Tuesday, the employee will clock in for say 5 hours
with [PayStatus] = "regular pay", then clock out sick
with the remaining 3 hours of [AlternatePayStatus]
= "paid time off".

How do a run a query that will combine the data from both
[PayStatus], [ScheduleTotal] and [AlternatePayStatus],
[Difference] to show that this employee has a pay summary
of:

Regular pay = 8 hours
Paid time off = 11 hours

Thanks!!!!!

Clan McCreery

.

 

[Steve Schapel]

Clan,

I am making the assumption that each record in the Schedule table
relates to one day, or one shift, or some such. And then, according
to the example you gave, for each scheduled period, there can be more
than one Clocked/Pay period. Therefore the clocked time should be in
a separate table. Something along these lines...

tblSchedule
[ScheduleID]
[EmployeeID]
[ScheduleTimeIn]
[ScheduleTimeOut]

tblPayPeriod
[PayPeriodID]
[ScheduleID]
[ClockTimeIn]
[ClockTimeOut]
[PayStatus]

- Steve Schapel, Microsoft Access MVP