Current Total Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2003. I have a payroll database that has 45,000 payroll
records. I need to create a query that will display annual and sick time
totals for each employee from the last payroll.

I can create a query that will display every annual and sick time totals
from every payroll but I only want to display the totals from the last
payroll that was entered.

How would I create this query?

Thanks,

Paul
 
Paul

That will really depend on your data structure.

You didn't indicate how you store information about payrolls, so we'd have
no way to guess how you'd figure out the "last" payroll.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have the following fields: PayDate, SSN, AnnGain, AnnUsed, AnnTot,
SickGain, SickUsed and SickTot.

I need a way to query on SSN, AnnTot and Sick Tot after each payperiod
without user input. I just need the totals from the current payperiod and not
any past payperiods. I need these totals so I can display them for the next
payperiod.

Hope this helps,

Paul
 
Assuming your payroll ran every 14 days, couldn't you limit the Payroll date
to Date()-13 in the criteria line?
 
Paul

Where I work (day job), our "pay periods" last two weeks.

In my consulting business, a "pay period" may be a month or more.

How do YOU determine payperiods?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Actually, should be >Date()-13 . In too much of a hurry.
Assuming your payroll ran every 14 days, couldn't you limit the Payroll date
to Date()-13 in the criteria line?
I'm using Access 2003. I have a payroll database that has 45,000 payroll
records. I need to create a query that will display annual and sick time
Click to expand...
[quoted text clipped - 9 lines]
Paul
Click to expand...
Click to expand...
 
So it sounds like you are saying, if you were saying this in 'English'
rather than in code:

* go back until you get to the 15th or the 30th, whichever comes first
* from there, go back to the other one BEFORE the one you got to first
* add up all the values between those two

I'm being deliberately vague, since some months have 30 days and some have
31... and while we're on that topic, how will you handle a month with fewer
that 30 days?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
To get dates from the 1st to the 15th of the present month or the 16th to
the last day of the prior month based on today's date. Try something like
the following as your criteria. This should return the 16th to the last
day of the previous month as long as the current date is before the 16th.
On the 16th and later it should return the 1st to the 15th of the current
month

Between DateSerial(Year(Date()), Month(Date()) - IIF(Day(Date())>15,1,0),
IIF(Day(Date())>15,16,1))
AND DateSerial(Year(Date()), Month(Date()), IIF(Day(Date())>15,0,15))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top