Main form- subform for -logging hours worked for day/week

  • Thread starter Thread starter babs
  • Start date Start date
B

babs

Previous post below - still can't figure this out -???

We are using data to Generate Paychecks(weekly for the employees) based
on their number of hours worked

One table is Employees

Soc Sec # is PK Text
Man Name - text
address - text
city-text
state-text
HrlyRate-currency
Plus just other info like date Hired, not much more here

The second TABLE is the Time Card MD
Right now this data is Manually ADDED in from an EXCEL spreadsheet(on a
daily basis) where the person totals Up FOR the WEEK the number of hours
worked for STRAight time, double time, etc for that WEEK(puts in the START
week date(Sunday DATE)- same on spreadsheet.

Really trying to set it up so that the person putting in the excel info can
just directly put it into ACCESS - after that we are good - set up an append
query and generate paychecks and Payroll table(GOOD on that!!)

The person putting in the data into Access would have to put it in daily and
maybe set up the WEEks Schedule in advance and click some button- actualy
worked for the day.

Here are the Column headings in EXCEL:
Man Name (Employee), Job Name, Job Number,Day(going down is the
Day-Mon,tues,wedetc) StraightTimeHrs, OverTimeHrs, DoubletimeHrs, NOTES
So can see what type of hours are work on Mon, tues, etc.
Each TAB is a seperate WEEK(starting on Sunday)-Actual DATE worked doesn't
seem to matter?? Just what Day for that week.

I made a Form - using the Form Wizard grabbing the Employee Table1st and
then the Timecard Table(not sure of best way - to list the hours worked field
for each day of straight time, over time, double time-have MonST, TueST, etc,
MonOT, TuesOT-the usere needs to Visually see the DAY!!)-ideas- also on
timecard table is soc sec#, job#, job name, startwkDATE(this is what is one
the tAB in excel)

they are a One to Many Link so the form ends up being the Main(single record
) is the employee and then what they have worked is in the subform -- but I
see ALLLLL of what they have worked

REally would like to put the date field(for beginning of week -like what is
On the TAB for Excel file)so the user can visually see ONE week at a time for
each employee- NOT sure how to see Only what is scheduled/worked for ONE
employee for One week(only put in the Sunday date for that week anyway) just
not sure how the Many side(subform) can show just ONE WEEK


thanks sooo much for helping,
barb
 
Hi Babs,

I use this simple function to get the start day of the week for the date
that the time entry is for:

code snippet begin=========================================

Public Function GetWeekStartDate(ReferenceDate As Date) As Date
Const PROC_NAME As String = "GetWeekStartDate"

On Error GoTo ErrorHandler

GetWeekStartDate = DateValue(DateAdd("d", 1 - Weekday(ReferenceDate),
ReferenceDate))

Cleanup:
Exit Function

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Function

code snippet end=========================================

Below is the SQL for my query that uses this function. The part you are
interested in is GetWeekStartDate([tblTimeLog].[TimeIn]) AS WeekStart

sql snippet begin=========================================

SELECT nz([Username],[tblTimeLog].[UserID]) AS Employee,
GetWeekStartDate([tblTimeLog].[TimeIn]) AS WeekStart,
DateValue(nz([TimeIn],#1/1/1901#)) AS CheckInDate,
Round(IIf(Nz([Ignore],False)=True,0,(DateDiff("n",[TimeIn],nz([TimeOut],IIf(DateValue(Now())=DateValue([TimeIn]),Now(),[TimeIn])))/60)+Nz([AdjustmentHours],0)+(Nz([AdjustmentMinutes],0)/60)+IIf(nz([HourLunch],False),-1,0)),2)
AS Hours, Nz([AdjustmentHours],0)+(Nz([AdjustmentMinutes],0)/60) AS
Adjustment, tblTimeLog.*, IIf([ChargeTo]="RegularTime",[Hours],Null) AS
RegularTime, IIf([ChargeTo]="Leave",[Hours],Null) AS Leave,
IIf([ChargeTo]="Holiday",[Hours],Null) AS Holiday
FROM tblTimeLog LEFT JOIN tblUsers ON tblTimeLog.UserID = tblUsers.UserID;

sql snippet begin=========================================
 
I really don't need the start date of the week - they actually ONLY put that
date in anyway - just wondering the best fields to use for the timecard with
the MON, tues, wed, ect.

the supervisor like to see the days of the week and what he is scheduling on
what day.

also just really have an issue on how to set up the form, subform and what
fields based on post above.

thanks for helping anyone!!!
Barb

Kipp Woodard said:
Hi Babs,

I use this simple function to get the start day of the week for the date
that the time entry is for:

code snippet begin=========================================

Public Function GetWeekStartDate(ReferenceDate As Date) As Date
Const PROC_NAME As String = "GetWeekStartDate"

On Error GoTo ErrorHandler

GetWeekStartDate = DateValue(DateAdd("d", 1 - Weekday(ReferenceDate),
ReferenceDate))

Cleanup:
Exit Function

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Function

code snippet end=========================================

Below is the SQL for my query that uses this function. The part you are
interested in is GetWeekStartDate([tblTimeLog].[TimeIn]) AS WeekStart

sql snippet begin=========================================

SELECT nz([Username],[tblTimeLog].[UserID]) AS Employee,
GetWeekStartDate([tblTimeLog].[TimeIn]) AS WeekStart,
DateValue(nz([TimeIn],#1/1/1901#)) AS CheckInDate,
Round(IIf(Nz([Ignore],False)=True,0,(DateDiff("n",[TimeIn],nz([TimeOut],IIf(DateValue(Now())=DateValue([TimeIn]),Now(),[TimeIn])))/60)+Nz([AdjustmentHours],0)+(Nz([AdjustmentMinutes],0)/60)+IIf(nz([HourLunch],False),-1,0)),2)
AS Hours, Nz([AdjustmentHours],0)+(Nz([AdjustmentMinutes],0)/60) AS
Adjustment, tblTimeLog.*, IIf([ChargeTo]="RegularTime",[Hours],Null) AS
RegularTime, IIf([ChargeTo]="Leave",[Hours],Null) AS Leave,
IIf([ChargeTo]="Holiday",[Hours],Null) AS Holiday
FROM tblTimeLog LEFT JOIN tblUsers ON tblTimeLog.UserID = tblUsers.UserID;

sql snippet begin=========================================

babs said:
Previous post below - still can't figure this out -???

We are using data to Generate Paychecks(weekly for the employees) based
on their number of hours worked

One table is Employees

Soc Sec # is PK Text
Man Name - text
address - text
city-text
state-text
HrlyRate-currency
Plus just other info like date Hired, not much more here

The second TABLE is the Time Card MD
Right now this data is Manually ADDED in from an EXCEL spreadsheet(on a
daily basis) where the person totals Up FOR the WEEK the number of hours
worked for STRAight time, double time, etc for that WEEK(puts in the START
week date(Sunday DATE)- same on spreadsheet.

Really trying to set it up so that the person putting in the excel info can
just directly put it into ACCESS - after that we are good - set up an append
query and generate paychecks and Payroll table(GOOD on that!!)

The person putting in the data into Access would have to put it in daily and
maybe set up the WEEks Schedule in advance and click some button- actualy
worked for the day.

Here are the Column headings in EXCEL:
Man Name (Employee), Job Name, Job Number,Day(going down is the
Day-Mon,tues,wedetc) StraightTimeHrs, OverTimeHrs, DoubletimeHrs, NOTES
So can see what type of hours are work on Mon, tues, etc.
Each TAB is a seperate WEEK(starting on Sunday)-Actual DATE worked doesn't
seem to matter?? Just what Day for that week.

I made a Form - using the Form Wizard grabbing the Employee Table1st and
then the Timecard Table(not sure of best way - to list the hours worked field
for each day of straight time, over time, double time-have MonST, TueST, etc,
MonOT, TuesOT-the usere needs to Visually see the DAY!!)-ideas- also on
timecard table is soc sec#, job#, job name, startwkDATE(this is what is one
the tAB in excel)

they are a One to Many Link so the form ends up being the Main(single record
) is the employee and then what they have worked is in the subform -- but I
see ALLLLL of what they have worked

REally would like to put the date field(for beginning of week -like what is
On the TAB for Excel file)so the user can visually see ONE week at a time for
each employee- NOT sure how to see Only what is scheduled/worked for ONE
employee for One week(only put in the Sunday date for that week anyway) just
not sure how the Many side(subform) can show just ONE WEEK


thanks sooo much for helping,
barb
Click to expand...
Click to expand...
 
Back
Top