Advice Needed

[steve711]

I have a database that has one aspect that is highly dependent on certain payroll information. For example:

When a pilot begins entering his flight for the day at the bottom of the entry form it tells him the payroll dates are from x to x and you have worked X number of days in this pay period.

This part I have gotten down quite nicely using a payperiod table. Problem is that I must enter that information manually and it is time consuming to do that for the whole year. Point of fact I only put the first day of the pay period in this table. Our pay periods are every two weeks.

I also generate a report for payroll based on these pay periods.

My question/advice is a new way of going about this pay period deal without having to enter it in manually every year.

We have these wonderful computers but I feel I am doing too much work but just don't see a different method around it.

A bit wordy but I wanted to be clear. Any advice in a new direction?

 

[pbaldy]

It would not be difficult to create a function that accepted a date as an input argument and appended records into your table for that date and any number of following dates at 14 day intervals. Think For/Next loop and the DateAdd() function.

 

[wkalo]

Hi There
Could You Send A Sample Your Database And Mark What Is You Want To Be Automated It Would Be very Helpfull

 

[steve711]

Not quite following you there how about an example? I'm a visual kind of person.

 

[pbaldy]

Me too, but at least give it a try! It just occurred to me that a Do Until loop would be better, so you could make it stop when it got to the next year. You want a recordset on the table, a variable to keep track of the date being set and another to test for the end of the year. You want a loop that adds the current date to the table, then increments it using DateAdd.

 

[steve711]

Another way to understand what I would like it to do.

I need it to display the start date and end date of the current pay period we are in based on the date entered by the pilot.

What I think that in order to do this I have to know the first start date. From there I then can calculate, based on the date entered, the start and end dates.

Does that make more sense? I believe this method, though, will be very slow say in 2 years, assuming I used the first start date as January 4th 2007.

 

[pbaldy]

Ah, a moving target. Same basic method will work, except you don't need the recordset. Just keep incrementing a variable until it's greater than your test date, then you know the previous value was the start date and the current value less a day is the end date.

 

[WayneRyan]

Steve,

No need to loop through a recordset.

You can use the DMax function to get the PP start date that is less than
the pilot's entered date.

The end date can just use the DateAdd function.

Wayne

 

[WayneRyan]

Steve,

Just read the rest of this thread.

To keep your PayPeriod table current, use the form's
OnOpen event and do something like the following to
check for the validity of the latest pay date:

Syntax might be off a tad ...

If DateAdd("d", 14, CDate(Dmax("[PayPeriod], "tblPayPeriods")) > Date Then
   DoCmd.RunSQL "Insert Into tblPayPeriods " & _
                "Values (#" & DateAdd("d", 14, CDate(Dmax("[PayPeriod], "tblPayPeriods")) & "#)"
End If

Something like that should keep the table up-to-date.

Then, you can use the DMax to retrieve it.

Note: In actual usage, traversing a recordset of two years data, as opposed to
using the DMax function, should be transparent to the end-user. Both should be
pretty quick.

hth,
Wayne

 

[pbaldy]

For the record Wayne, I wasn't suggesting a loop to find records, as you and I both know that would be highly inefficient. I was suggesting using one to create a year's worth of entries to the table, as that's how I interpreted the original question.

The follow-up question seems to be getting away from storing the dates at all, so I used a loop to find the current period from a known starting point, without the periods being saved anywhere.

 

[WayneRyan]

Paul,

Rereading it again, I don't think there is a specific question here.
There are any number of ways to proceed.

At least we gave Steve some ideas.

Happy New Year,
Wayne

 

[pbaldy]

Amen to that, my friend. I think I was recently in your area. My sister-in-law lives near Thousand Oaks, and I think I remember driving by/through Camarillo. Am I remembering right, or did I just have too much wine with dinner? If so, next time we visit her I'll have to swing by and buy you a frosty cold one.

 

[WayneRyan]

Paul,

Sounds great. Camarillo is about 7-10 miles north of T.O.
Great area, but growing rapidly!

See ya,
Wayne

 

[The_Doc_Man]

The "right" way to do this depends on the pay periods. I have worked for companies that have strict pay periods based on an interval and uneven pay periods based on a date. Which one you have will govern how you do this. Another issue is whether the strict-interval pay period changes across year boundaries. First decide this answer. Then you can build a table of pay periods once you know the rules.

Now, to do this you might choose the following:

Table PayPer
PPfirst, date, date that starts the period
PPlast, date, date that ends the period
PPID, number, the pay period number. You can make this the prime key of the PayPer table, it will probably be easier to manipulate than either of the date fields as keys.

PPID in this table can either reset each year or it can be numbered continuously. (For my current employer, it would be continuous with the prior year's number. We are currently somewhere around pay period 140+)

The dates would be generated based on the rules. For my current employer, the dates are the 1st through 14th and 15th through last day of each month. So we have 24 pay periods per year. My previous employer, though, used exactly 14 days per pay period and we had 26 per year plus the odd fraction that had to be split across year boundaries. Doesn't matter, though, because even if you have to manually create oddball periods, this method works.

OK, now you have a pay-period lookup table. All you need is to read up on the BETWEEN ... AND operator so that you can decide in which pay period the pilot started. And in which pay period it ended. (Two DLookups, perhaps?) Anyway, this will allow you to detect actions that crossed pay periods (start PPID and end PPID don't match, of course) and those that are completely contained in the same PPID. And once you have the PPID for a given action, you can do your Pay Period payroll based on all actions having the same PPID.

This is a special case where I would store the result of the lookup in the table that holds the pilot's actions. There are purists - and sometimes I am one of them - who would say "don't store what you can look up again." In this case, though, all I would store is the PPID from the lookup. This is because it is small and efficient as a way to link back to the PayPer table. ALSO, if you are storing other things in the PayPer table, it becomes a foreign key to let you access the other things.

How do you generate the entries in the lookup table? Well, that depends on the rules you have to live by. In the case where the pay periods are based on absolute intervals (and the last period can cross year-end boundaries), you can do an Append query to generate a new entry that has the correct pay period number, a start date one day greater than DMax of the last date in the table, and an end date that is X days greater than DMax of the last date in the table. If the intervals are not so neat and clean, this becomes a clear case where VBA might be preferable, though an IIF still works in a query.