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...
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
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" <kelvin at mccsa dot com> wrote in message
news:O6rvJFOk...@TK2MSFTNGP04.phx.gbl...
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" <Larry.NoSpa...@verizon.net> wrote in message
news:%23shci9O...@TK2MSFTNGP05.phx.gbl...
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
--
-Larry-
--
"Kelvin Beaton" <kelvin at mccsa dot com> wrote in message
news:e4uY4UXk...@TK2MSFTNGP05.phx.gbl...
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
MPAP...@FortuneJames.com
Yes Excel doesn't sound so bad... ;-)
I'll continue to work on this and will incoporate your comments!
Thanks again!
Kelvin
"James A. Fortune" <MPAP...@FortuneJames.com> wrote in message
news:eCjSoddk...@TK2MSFTNGP02.phx.gbl...