Needing some input on how to take this db to the next level...

[Kelvin Beaton]

I'm trying to create a time sheet database. I have some of the basics worked
out, but would like some input on how to deal with the subform that displays
the days in the current pay period.
Some how I would like the dropdown to generate payperiods so I don't have to
enter every one myself.
I'd like the subform to display the 14 days for that pay period.

Any input would be appreciated

Kelvin

here is what I have pretty much so far:
http://www.mccsa.com/Temp/TimeAndBilling.zip

PS I haven't gone through and fixed tab orders and the like so there is lots
of work to polish it up...

 

[dbahooker]

anyone using Microsoft Access in the year 2007 should be usnig Access
Data Projects


and it sounds to me like you need to understand more about the schema
I would reccomend looking at www.databaseanswers.org to see if one of
those schemas can help you to better understand

 

[Larry Daugherty]

Actually, you probably don't have it "worked out" just yet. If you're
talking Excel spreadsheets then you should post your issue in an Excel
newsgroup.

My response assumes that you are really trying to get something done
in Access.

You may get some flak about generating empty records. It begins here:
Don't generate empty records unless that turns out to be the best way
to solve a particular data issue. Of course, I'm young and have a lot
to learn but I haven't seen a convincing case yet.

In just about any case in which you need to show events for a person
over a pay period you can *calculate* in order to display meaningful
results.

You can do some very intelligent things with child tables ~ tblPay and
a subform based on that table. You can establish a default date for
each new record that will make a lot of sense in the context of what
you are doing. For example you could have the date value default to
the next working date higher than the last date entered for that
person. Unless you have highly variable work (day) schedules, that
proposed date will be the correct date. If it wasn't, either over
write it or double click to bring up a calendar from which you can
pick a date.... On it goes.

But creating an empty record except for the date so that you'll know
that the date was there and that nothing was noted just doesn't make
sense. It makes better sense that if the date isn't there then
nothing was noted for that date.

If you have digested the above and you or your management absolutely,
positively insist that the "wrong way" is the way you want to do it
then post back.

In any case, post back with questions-
HTH
-
-Larry-

 

[Kelvin Beaton]

Hi Larry

I hear what you're saying about blank records... did you look at the
database? http://www.mccsa.com/Temp/TimeAndBilling.zip
We are currently using a spreadsheet and I would like to develope an Access
database, hence the post and the link to a copy of what I'm working on.

My thought is, in the spreadsheet user can see a pay period at a time.

If you have any other ideas on how to structure this differently I'd be glad
to hear them.

Thanks

Kelvin

 

[Larry Daugherty]

No, I haven't seen the database. You've saved it in 2K2 format. I
don't have that version mounted anywhere and am not curious enough
about anything in 2k2 to mount it.

Are you striving to meet a demand of your manager or of the user base
or have you simply assumed that they'll only want what they already
have? You can only go so far in making Access look and feel like
Excel. They aren't minor variations on a common theme. They are
hugely different in their internals and in their specific behaviors.
Microsoft folks have worked long and hard to make many things behave
the same in both platforms.

It sounds like the view of the whole pay period at once is a
requirement that you've made up. Your user base may be willing to
deal with a different paradigm, have you polled them and their
managers?

HTH

 

[James A. Fortune]

No, I haven't seen the database. You've saved it in 2K2 format. I
don't have that version mounted anywhere and am not curious enough
about anything in 2k2 to mount it.

Are you striving to meet a demand of your manager or of the user base
or have you simply assumed that they'll only want what they already
have? You can only go so far in making Access look and feel like
Excel. They aren't minor variations on a common theme. They are
hugely different in their internals and in their specific behaviors.
Microsoft folks have worked long and hard to make many things behave
the same in both platforms.

It sounds like the view of the whole pay period at once is a
requirement that you've made up. Your user base may be willing to
deal with a different paradigm, have you polled them and their
managers?

HTH

Kevin,

While you're polling, maybe I can generate some ideas.

See:

http://groups.google.com/group/microsoft.public.access/msg/52899be712104374

That effort got placed on hold so I still don't have code to post for
it. My point is that the logic for handling time tickets in Access will
eventually get a little involved. Excel does have some advantages over
Access when it comes to complicated conditions for pay periods, but it
also has some notable drawbacks. One way to begin to handle the fact
that your pay period is two weeks is to use an expression to calculate
whether a given date is in week 1 or week 2 based on the very first pay
period starting date:

InitialPPDate: 3/3/1997 (Monday)

PPWeekNumber: (DateDiff("d", #3/3/1997#, Date())) \ 7 Mod 2 + 1

For today (May 8, 2007) this returns 2, indicating that today falls in
the second week if the initial pay period started on 3/3/1997. This
expression is a warm-up for later.

To get the date of a previous or following day I use the following two
module functions:

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

So, for today (May 8, 2007), I can find the Monday on or before today as
follows:

LEDay(#5/8/2007#, 2)

So if you have a given date and want to know which pay period it falls
in, you could do something like (but don't):

dtStartOfPP = IIf((DateDiff("d", #3/3/1997#, Date())) \ 7 Mod 2 + 1 = 1,
LEDay(Date(), 2), LEDay(DateAdd("ww", Date(), -1), 2))
dtEndOfPP = DateAdd("ww", 2, dtStartOfPP)

That is, go back to the previous Monday on or equal to Date() if Date()
is in week 1. Go back to the previous Monday on or equal to a week ago
if Date() is in week 2. That was more warm-up.

Although the logic above works for any input date, it makes sense to
show just the initial date for each pay period in your dropdown. Maybe
the RowSource can look something like (air SQL):

SELECT DISTINCT LEDay([TimeTicketDate], 2) AS PPStarting FROM
tblTimeTickets WHERE (DateDiff("d", #3/3/1997#, LEDay([TimeTicketDate],
2))) \ 7 Mod 2 = 0;

That should put every distinct pay period starting date for every
existing time ticket into your dropdown. That may or may not give you
all the dates you want. When one of the dates is selected, the query
for the subform's RecordSource can use something like 'BETWEEN
Nz(Forms!MyForm!MyDropdown.Value, Date()) AND DateAdd("d", 13,
Nz(Forms!MyForm!MyDropdown.Value, Date())' to select the date range of
time tickets you want to show on the subform (it may be necessary to
requery the subform in an event such as the dropdown's AfterUpdate event).

Another way to get dates into your dropdown is to use a table of integers:

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
3 3
....
N N

where N is large enough that your pay period date range is always within
N weeks.

Your dropdown's RowSource can look something like:

PARAMETERS Forms!MyForm!txtStartDate.Value DateTime,
Forms!MyForm!txtEndDate.Value DateTime; SELECT DateAdd("ww", I - 1,
LEDay(Forms!MyForm!txtStartDate.Value, 2)) AS PPStarting FROM tblI WHERE
DateAdd("ww", I - 1, LEDay(Forms!MyForm!txtStartDate.Value, 2)) <=
Forms!MyForm!txtEndDate.Value;

That will put all the Monday's between the two dates into the dropdown.
But you want only the Mondays whose week number is 1:

PARAMETERS Forms!Form1!txtStartDate.Value DateTime,
Forms!Form1!txtEndDate.Value DateTime; SELECT DateAdd("ww", I - 1,
LEDay(Forms!Form1!txtStartDate.Value, 2)) AS PPStarting FROM tblI WHERE
DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)) <=
Forms!Form1!txtEndDate.Value And (DateDiff("d", #3/3/1997#,
DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)))) \ 7 Mod
2 + 1 = 1;

I created a form to test this out and used a start date of 3/3/2007 and
an end date of 5/1/2007. The query produced the following:

PPStarting
3/5/2007
3/19/2007
4/2/2007
4/16/2007
4/30/2007

When I changed #3/3/1997# to #3/10/1997# I got:

PPStarting
2/26/2007
3/12/2007
3/26/2007
4/9/2007
4/23/2007

To add a final touch you can add some kind of calendar control for each
of the dates that are to be placed in txtStartingDate and txtEndingDate
on the form. Lock the textboxes, have appropriate default dates placed
in the text boxes and only let the user change dates by using the
calendar control.

Hopefully the information above gives you a better idea of the
differences between Access and Excel when it comes to doing something
like time tickets. Access can use a single table for everything but the
implementation can get a little tricky later when all the weird things
that can happen with time tickets start cropping up. The single table
allows much more flexibility for all kinds of report calculations,
totals and averages. A time ticket table I use includes a job number
that allows a form to compute in almost real time how much labor has
been charged to that job.

James A. Fortune
(e-mail address removed)

 

[Kelvin Beaton]

Thanks James for your input...

Yes Excel doesn't sound so bad... ;-)

I'll continue to work on this and will incoporate your comments!

Thanks again!

Kelvin

No, I haven't seen the database. You've saved it in 2K2 format. I
don't have that version mounted anywhere and am not curious enough
about anything in 2k2 to mount it.

Are you striving to meet a demand of your manager or of the user base
or have you simply assumed that they'll only want what they already
have? You can only go so far in making Access look and feel like
Excel. They aren't minor variations on a common theme. They are
hugely different in their internals and in their specific behaviors.
Microsoft folks have worked long and hard to make many things behave
the same in both platforms.

It sounds like the view of the whole pay period at once is a
requirement that you've made up. Your user base may be willing to
deal with a different paradigm, have you polled them and their
managers?

HTH

Kevin,

While you're polling, maybe I can generate some ideas.

See:

http://groups.google.com/group/microsoft.public.access/msg/52899be712104374

That effort got placed on hold so I still don't have code to post for it.
My point is that the logic for handling time tickets in Access will
eventually get a little involved. Excel does have some advantages over
Access when it comes to complicated conditions for pay periods, but it
also has some notable drawbacks. One way to begin to handle the fact that
your pay period is two weeks is to use an expression to calculate whether
a given date is in week 1 or week 2 based on the very first pay period
starting date:

InitialPPDate: 3/3/1997 (Monday)

PPWeekNumber: (DateDiff("d", #3/3/1997#, Date())) \ 7 Mod 2 + 1

For today (May 8, 2007) this returns 2, indicating that today falls in the
second week if the initial pay period started on 3/3/1997. This
expression is a warm-up for later.

To get the date of a previous or following day I use the following two
module functions:

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function

So, for today (May 8, 2007), I can find the Monday on or before today as
follows:

LEDay(#5/8/2007#, 2)

So if you have a given date and want to know which pay period it falls in,
you could do something like (but don't):

dtStartOfPP = IIf((DateDiff("d", #3/3/1997#, Date())) \ 7 Mod 2 + 1 = 1,
LEDay(Date(), 2), LEDay(DateAdd("ww", Date(), -1), 2))
dtEndOfPP = DateAdd("ww", 2, dtStartOfPP)

That is, go back to the previous Monday on or equal to Date() if Date() is
in week 1. Go back to the previous Monday on or equal to a week ago if
Date() is in week 2. That was more warm-up.

Although the logic above works for any input date, it makes sense to show
just the initial date for each pay period in your dropdown. Maybe the
RowSource can look something like (air SQL):

SELECT DISTINCT LEDay([TimeTicketDate], 2) AS PPStarting FROM
tblTimeTickets WHERE (DateDiff("d", #3/3/1997#, LEDay([TimeTicketDate],
2))) \ 7 Mod 2 = 0;

That should put every distinct pay period starting date for every existing
time ticket into your dropdown. That may or may not give you all the
dates you want. When one of the dates is selected, the query for the
subform's RecordSource can use something like 'BETWEEN
Nz(Forms!MyForm!MyDropdown.Value, Date()) AND DateAdd("d", 13,
Nz(Forms!MyForm!MyDropdown.Value, Date())' to select the date range of
time tickets you want to show on the subform (it may be necessary to
requery the subform in an event such as the dropdown's AfterUpdate event).

Another way to get dates into your dropdown is to use a table of integers:

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
3 3
...
N N

where N is large enough that your pay period date range is always within N
weeks.

Your dropdown's RowSource can look something like:

PARAMETERS Forms!MyForm!txtStartDate.Value DateTime,
Forms!MyForm!txtEndDate.Value DateTime; SELECT DateAdd("ww", I - 1,
LEDay(Forms!MyForm!txtStartDate.Value, 2)) AS PPStarting FROM tblI WHERE
DateAdd("ww", I - 1, LEDay(Forms!MyForm!txtStartDate.Value, 2)) <=
Forms!MyForm!txtEndDate.Value;

That will put all the Monday's between the two dates into the dropdown.
But you want only the Mondays whose week number is 1:

PARAMETERS Forms!Form1!txtStartDate.Value DateTime,
Forms!Form1!txtEndDate.Value DateTime; SELECT DateAdd("ww", I - 1,
LEDay(Forms!Form1!txtStartDate.Value, 2)) AS PPStarting FROM tblI WHERE
DateAdd("ww", I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)) <=
Forms!Form1!txtEndDate.Value And (DateDiff("d", #3/3/1997#, DateAdd("ww",
I - 1, LEDay(Forms!Form1!txtStartDate.Value, 2)))) \ 7 Mod 2 + 1 = 1;

I created a form to test this out and used a start date of 3/3/2007 and an
end date of 5/1/2007. The query produced the following:

PPStarting
3/5/2007
3/19/2007
4/2/2007
4/16/2007
4/30/2007

When I changed #3/3/1997# to #3/10/1997# I got:

PPStarting
2/26/2007
3/12/2007
3/26/2007
4/9/2007
4/23/2007

To add a final touch you can add some kind of calendar control for each of
the dates that are to be placed in txtStartingDate and txtEndingDate on
the form. Lock the textboxes, have appropriate default dates placed in
the text boxes and only let the user change dates by using the calendar
control.

Hopefully the information above gives you a better idea of the differences
between Access and Excel when it comes to doing something like time
tickets. Access can use a single table for everything but the
implementation can get a little tricky later when all the weird things
that can happen with time tickets start cropping up. The single table
allows much more flexibility for all kinds of report calculations, totals
and averages. A time ticket table I use includes a job number that allows
a form to compute in almost real time how much labor has been charged to
that job.

James A. Fortune
(e-mail address removed)