simple time sheet input bi-weekly

[Danny]

All,

I have a few tables in Ms access 2003. Timesheet tbl, Employeetbl,
Payperiodtbl, Sickreasonstbl .

Employeetbl fields
Empid (primary key) however employee names could be primary because
they aren't duplicated.
employee name

Timesheet tbl fields
autosysnumber (primary key)
Date
Time in
Time out
Pay Period Number
Pay Period Year
sick reason

Payperiodtbl,Reasontbl and employeetbl are strictly for selecting from
a list box in the form.

Here is my questions . Sorry to ask so many but something I thought
would be simple turns to be more difficult. Or I am making it that
way


There is only one date field in Timesheet table. Is it possible to
have multiple date fiields in a form that will enter data into the one
date field in the timesheettbl? say have 10 days for the payperiod.

I want to populate the dates for the payperiod and payperiod year
selected that is selected so supervisors won't have to type in dates
into the form. How would you do this?

I don't want the form to show historical records, just be blank for
new input?

Any help on this is greatly appreciated. If more info is needed
please don't hesitate to ask.

Thanks
D

 

[John W. Vinson]

All,

I have a few tables in Ms access 2003. Timesheet tbl, Employeetbl,
Payperiodtbl, Sickreasonstbl .

Employeetbl fields
Empid (primary key) however employee names could be primary because
they aren't duplicated.
employee name

Use FirstName and LastName fields so you can sort by last name and display
them more flexibly. Keep the EmpID: you don't want to have to tell Jim Smith,
the really well qualified applicant, "we can't hire you because we already
have a Jim Smith".

Timesheet tbl fields
autosysnumber (primary key)
Date

Change the fieldname. Date is a reserved word and will cause Access to get
confused.

Time in
Time out
Pay Period Number
Pay Period Year

I suspect that the Pay Period Year can be derived from the PayDate, and is
therefore redundant and should be removed from the table. The same may apply
to PayPeriodNumber.

sick reason

Payperiodtbl,Reasontbl and employeetbl are strictly for selecting from
a list box in the form.

Here is my questions . Sorry to ask so many but something I thought
would be simple turns to be more difficult. Or I am making it that
way

You are. :-{)

There is only one date field in Timesheet table. Is it possible to
have multiple date fiields in a form that will enter data into the one
date field in the timesheettbl? say have 10 days for the payperiod.

It would be possible and a Very Bad Idea. "Fields are expensive, records are
cheap". Each "bout" of work (each day, or each contiguous period of work)
should be in a separate record.

I want to populate the dates for the payperiod and payperiod year
selected that is selected so supervisors won't have to type in dates
into the form. How would you do this?

Yoiu could use an unbound Form with a Subform. Put a textbox or combo box on
the main form with a default value of Date(), or just set it manually to the
date for which you want to enter data. Use that control name as the Master
Link Field for the subform based on the Timesheet table, and use the PayDate
as the child link field.

Or... put VBA code in the AfterUpdate event of the PayDate field (or any field
that you want to be "sticky", holding its value for new records) such as

Private Sub txtPayDate_AfterUpdate()
Me!txtPayDate.DefaultValue = """" & Me!txtPayDate & """""
End Sub

I don't want the form to show historical records, just be blank for
new input?

Set the form's Data Entry property to Yes.

 

[Danny]

Use FirstName and LastName fields so you can sort by last name and display
them more flexibly. Keep the EmpID: you don't want to have to tell Jim Smith,
the really well qualified applicant, "we can't hire you because we already
have a Jim Smith".


Change the fieldname. Date is a reserved word and will cause Access to get
confused.


I suspect that the Pay Period Year can be derived from the PayDate, and is
therefore redundant and should be removed from the table. The same may apply
to PayPeriodNumber.




You are. :-{)




It would be possible and a Very Bad Idea. "Fields are expensive, records are
cheap". Each "bout" of work (each day, or each contiguous period of work)
should be in a separate record.


Yoiu could use an unbound Form with a Subform. Put a textbox or combo box on
the main form with a default value of Date(), or just set it manually to the
date for which you want to enter data. Use that control name as the Master
Link Field for the subform based on the Timesheet table, and use the PayDate
as the child link field.

Or... put VBA code in the AfterUpdate event of the PayDate field (or any field
that you want to be "sticky", holding its value for new records) such as

Private Sub txtPayDate_AfterUpdate()
Me!txtPayDate.DefaultValue = """" &  Me!txtPayDate & """""
End Sub


Set the form's Data Entry property to Yes.

John

Thanks for taking your time to provide me with some very useful
suggestions suggestions. However the Date field I am referring to is
the Date a person clocks in (workdate) not the payddate. Is that
possible to have 10 days on a form that populate the same workdate?
Sorry I wasn't clear on that in my post especially just calling the
field a date field :0).

Would it be also possible if when a say payperiod 2 and payperiod year
is 2008 then these fields would populate automatically with the
correct dates for that payperiod so supervisors won't have to fill it
out.

I appreciate your help
D

 

[John W. Vinson]

Thanks for taking your time to provide me with some very useful
suggestions suggestions. However the Date field I am referring to is
the Date a person clocks in (workdate) not the payddate. Is that
possible to have 10 days on a form that populate the same workdate?
Sorry I wasn't clear on that in my post especially just calling the
field a date field :0).

Would it be also possible if when a say payperiod 2 and payperiod year
is 2008 then these fields would populate automatically with the
correct dates for that payperiod so supervisors won't have to fill it
out.

That would be possible with a little VBA code. Your best bet might be to have
a little table with three fields, Workyear, Payperiod and Workdate; fill it
out manually or (easier) using Excel's fill-series feature. This would have
every workday from 2008 through (say) 2020 filled out; you could take out
holidays as well.

With this auxiliary table you could write some VBA code to look up the next
scheduled work dates based on unbound year and payperiod textboxes on the
form. I've got to run shortly so I can't put this together right now but it
should give you a start - post back if this doesn't help.

 

[Danny]

That would be possible with a little VBA code. Your best bet might be to have
a little table with three fields, Workyear, Payperiod and Workdate; fill it
out manually or (easier) using Excel's fill-series feature. This would have
every workday from 2008 through (say) 2020 filled out; you could take out
holidays as well.

With this auxiliary table you could write some VBA code to look up the next
scheduled work dates based on unbound year and payperiod textboxes on the
form. I've got to run shortly so I can't put this together right now but it
should give you a start - post back if this doesn't help.

John,

I have a table with 3 fields
Period Year
Period Number
Workdate
I imported into this table with all of the data from an Excel
spreadsheet.

Sorry to have more questions, not 100% sure which direction we are
going on this.
Question
So are you saying in the Form using VBA I will be able to populate
Each day of the week based on the pay period number and year within
the form? (Vba would be great not sure how to code it)

Will this mean that the form will require a field for each date?
Which is 14 days within payperiod? (which is what I would like the
user to see in the form)

Finally if there are 14 fields in the form for these dates would that
mean I could have one date field in the time sheet table that is going
to be populated with the data from the 14 date fields in the form?

Am I following where you are going with this, am I close, or am I a
missing entirely

Thanks for your help I really do appreciate it
D

 

[John W. Vinson]

I have a table with 3 fields
Period Year
Period Number
Workdate
I imported into this table with all of the data from an Excel
spreadsheet.

And this has all the potential workdays? Cool.

Sorry to have more questions, not 100% sure which direction we are
going on this.
Question
So are you saying in the Form using VBA I will be able to populate
Each day of the week based on the pay period number and year within
the form? (Vba would be great not sure how to code it)

Well, I've been thinking about it... it might be better to base the subform on
a Query left joining this table to your timesheet table, by workdate. Put a
textbox on your form for the year (named txtPeriodYear) and the pay period
(txtPayPeriod), and create a Query joining this new table to your timesheet
table by Workdate. Click the join line and choose "Show all records in <your
new period table> and matching records in <timesheet table>". Put a criterion
on Period Year of

=[Forms]![yourformname]![txtPeriodYear]
and on Pay Period

=[Forms]![yourformname]![txtPayPeriod]

Now your subform will display 14 rows, one for each day during that pay
period.

Will this mean that the form will require a field for each date?

NO. IT WILL NOT.

"Fields are expensive, records are cheap". You'll enter one *RECORD* into the
pay table for each day worked.

Which is 14 days within payperiod? (which is what I would like the
user to see in the form)

You'll see 14 rows.

Finally if there are 14 fields in the form for these dates would that
mean I could have one date field in the time sheet table that is going
to be populated with the data from the 14 date fields in the form?

You'll get the date filled in for each row for which you enter anything else
in the record (e.g. by selecting the person).

 

[Danny]

I have a table with 3 fields
Period Year
Period Number
Workdate
I imported into this table with all of the data from an Excel
spreadsheet.

And this has all the potential workdays? Cool.

Sorry to have more questions, not 100% sure which direction  we are
going on this.
Question
So are you saying in the Form using VBA I will be able to populate
Each day of the week based on the pay period number and year within
the form?  (Vba would be great not sure how to code it)

Well, I've been thinking about it... it might be better to base the subform on
a Query left joining this table to your timesheet table, by workdate. Put a
textbox on your form for the year (named txtPeriodYear) and the pay period
(txtPayPeriod), and create a Query joining this new table to your timesheet
table by Workdate. Click the join line and choose "Show all records in <your
new period table> and matching records in <timesheet table>". Put a criterion
on Period Year of

=[Forms]![yourformname]![txtPeriodYear]
and on Pay Period

=[Forms]![yourformname]![txtPayPeriod]

Now your subform will display 14 rows, one for each day during that pay
period.

Will this mean that the form will require a field for each date?

NO. IT WILL NOT.

"Fields are expensive, records are cheap". You'll enter one *RECORD* into the
pay table for each day worked.

Which is 14 days within payperiod? (which is what I would like the
user to see in the form)

You'll see 14 rows.

Finally if there are 14 fields in the form for these dates would that
mean I could have one date field in the time sheet table that is going
to be populated with the data from the 14 date fields in the form?

You'll get the date filled in for each row for which you enter anything else
in the record (e.g. by selecting the person).

John,

Sorry I have been caught up with other stuff. I will try this out. I
am sure I will have questions, please be patient.

Thanks for your help.

D

 

[Danny]

And this has all the potential workdays? Cool.

Well, I've been thinking about it... it might be better to base the subform on
a Query left joining this table to your timesheet table, by workdate. Put a
textbox on your form for the year (named txtPeriodYear) and the pay period
(txtPayPeriod), and create a Query joining this new table to your timesheet
table by Workdate. Click the join line and choose "Show all records in <your
new period table> and matching records in <timesheet table>". Put a criterion
on Period Year of

=[Forms]![yourformname]![txtPeriodYear]
and on Pay Period

=[Forms]![yourformname]![txtPayPeriod]

Now your subform will display 14 rows, one for each day during that pay
period.

Will this mean that the form will require a field for each date?

NO. IT WILL NOT.

"Fields are expensive, records are cheap". You'll enter one *RECORD* into the
pay table for each day worked.

You'll see 14 rows.

You'll get the date filled in for each row for which you enter anything else
in the record (e.g. by selecting the person).

John,

Sorry I have been caught up with other stuff.  I will try this out.  I
am sure I will have questions, please be patient.

Thanks for your help.

D- Hide quoted text -

- Show quoted text -

John,

I am sure I am missing something. I have set up a query that left
joins timesheet tbl and workdate tbl
bringing in all fields from both tables in the query.

placing the criteria on period year of
=[forms]![formwhich is the above query name]![txtPeriodYear]

for payperiod criteria
=[forms]![queryname'which woudl be the aboce query name']!
[txtPayPeriod]

When I run this query it prompts as expected year and period it does
as expected showing all days for that period and year. (from query
results)

Here is where I am confused. Let's say I dont' have any forms
established at this point. You want me to build a sub form that uses
the above query and add 2 text boxes in it that are called
txtPeriodYear and txtPayPeriod?
Do these text boxes need a conrtol source in proprerties?
If I run it just like that no fields are displayin in the form.

Do I add the rest of the fields into this form from that same query
that I want filled out?

How does the form know that the 14 dates should show up in the form?

Sorry forgive my ignorance and confusion.
Again thanks for your help I really do appreciate it.


Thanks
D

 

[Danny]

I have a table with 3 fields
Period Year
Period Number
Workdate
I imported into this table with all of the data from an Excel
spreadsheet.

And this has all the potential workdays? Cool.

Sorry to have more questions, not 100% sure which direction  we are
going on this.
Question
So are you saying in the Form using VBA I will be able to populate
Each day of the week based on the pay period number and year within
the form?  (Vba would be great not sure how to code it)

Well, I've been thinking about it... it might be better to base the subform on
a Query left joining this table to your timesheet table, by workdate. Put a
textbox on your form for the year (named txtPeriodYear) and the pay period
(txtPayPeriod), and create a Query joining this new table to your timesheet
table by Workdate. Click the join line and choose "Show all records in <your
new period table> and matching records in <timesheet table>". Put a criterion
on Period Year of

=[Forms]![yourformname]![txtPeriodYear]
and on Pay Period

=[Forms]![yourformname]![txtPayPeriod]

Now your subform will display 14 rows, one for each day during that pay
period.

Will this mean that the form will require a field for each date?

NO. IT WILL NOT.

"Fields are expensive, records are cheap". You'll enter one *RECORD* into the
pay table for each day worked.

Which is 14 days within payperiod? (which is what I would like the
user to see in the form)

You'll see 14 rows.

Finally if there are 14 fields in the form for these dates would that
mean I could have one date field in the time sheet table that is going
to be populated with the data from the 14 date fields in the form?

You'll get the date filled in for each row for which you enter anything else
in the record (e.g. by selecting the person).

John,

Sorry if you get this twice. I am not sure if I replied to you or
myself when I sent this last time. Or does it matter when I post a
reply in this group if it is to my last post as a reply or what?

Anyway.
I am sure I am missing something. I have set up a query that left
joins timesheet tbl and workdate tbl
bringing in all fields from both tables in the query.


placing the criteria on period year of
=[forms]![formwhich is the above query name]![txtPeriodYear]


for payperiod criteria
=[forms]![queryname'which woudl be the aboce query name']!
[txtPayPeriod]


When I run this query it prompts as expected year and period it does
as expected showing all days for that period and year. (from query
results)


Here is where I am confused. Let's say I dont' have any forms
established at this point. You want me to build a sub form that uses
the above query and add 2 text boxes in it that are called
txtPeriodYear and txtPayPeriod?
Do these text boxes need a conrtol source in proprerties?
If I run it just like that no fields are displayin in the form.


Do I add the rest of the fields into this form from that same query
that I want filled out?


How does the form know that the 14 dates should show up in the form?


Sorry forgive my ignorance and confusion.
Again thanks for your help I really do appreciate it.


Thanks
D