 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2007Current Total Data
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'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 [quoted text clipped - 9 lines] > > Paul 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 > Paul > [quoted text clipped - 21 lines] > > > > Paul 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 >I have the following fields: PayDate, SSN, AnnGain, AnnUsed, AnnTot, > SickGain, SickUsed and SickTot. [quoted text clipped - 39 lines] >> > >> > Paul Are pay dates are the 15th and 30th. Thanks, Paul > Paul > [quoted text clipped - 52 lines] > >> > > >> > Paul 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 > Are pay dates are the 15th and 30th. > [quoted text clipped - 60 lines] >> >> > >> >> > Paul 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))  SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > So it sounds like you are saying, if you were saying this in 'English' > rather than in code: [quoted text clipped - 79 lines] >>> >> > >>> >> > Paul 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 [quoted text clipped - 9 lines] > >Paul 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? [quoted text clipped - 4 lines] >> >>Paul |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.