 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / December 2006fill in previouse records
Hi all, Suppose we have 12 months a year and each year has only 30 days. For example a worker until month 8 has worked for 170 days and I'd like to fill in 5 months(150=5*30 days) and 20 days salary for him.(170=5*30+20) Month 8 30 day Month 7 30 day Month 6 30 day Month 5 30 day Month 4 30 day Month 3 20 day Is there a VBA code or query for this problem? Do the previous records already exist? Or do you have to create them? If they exist and the previous month has a number of days in it already, do you overwrite the existing value, add to it (up to 30), etc. What is the structure of the previous records? Were does the starting number of days get entered? In a record, in a variable? How do you know which month to start with? Do you have multiple years of data? If not, what do you do if you start with February (max 28 or 29 days) and have a number like 125? Just give Feb and Jan 30 each and ignore the other 65 days? All those problems (and others) can be solved with VBA or perhaps in an SQL query, but until you define how you handle each situation, there is no use developing a solution. Some starting hints. StartDays\30 will tell you the number of whole months StartDays Mod 30 will tell you the number of remaining days. 13 - (StartDays \30) could tell you the number of the first month that gets 30 days. Depends on your rules. If StartDays Mod 30 = 0 Then you don't need to add any partial 30 day period. If the records already exist, then you would need something like UPDATE YourTable SET NumberDays = 30 WHERE MonthNumber Between [SomeCurrentMonthNumberFromSomewhere] and [SomeCurrentMonthNumberFromSomewhere] +1 - (StartDays \30) AND EmployeeID = [????SomeValueFromSomewhere????] > Hi all, > Suppose we have 12 months a year and each year has only 30 days. [quoted text clipped - 7 lines] > Month 3 20 day > Is there a VBA code or query for this problem? Hi john, I wrote about structure of my table in previous thread "populate previous fields in access". I need a virtual year and month not real.before creating a record for month 5 for example i have created month 1 to 4. thanks for your help > Do the previous records already exist? Or do you have to create them? > [quoted text clipped - 40 lines] > > Month 3 20 day > > Is there a VBA code or query for this problem? I searched for and found the previous thread in this newsgroup. I did not see the information I requested there. Good luck on solving your problem. > Hi john, > I wrote about structure of my table in previous thread "populate previous [quoted text clipped - 58 lines] >> > Month 3 20 day >> > Is there a VBA code or query for this problem? Hi john, I apologise for delay to answer your questions and appreciate you for taking time to response.I have changed the structure of table a little. previous records already exists. the previous months doesn't have a date in it . the structure of records is so: fldmonth fldname fldworkdays fldsalary flddatediff fldsalarydiff 1 peter 30 30000 2 peter 30 30000 3 peter 30 30000 4 peter 30 45000 2006/02/21 5 peter 30 45000 1 amini 30 90000 2 amini 30 90000 3 amini 30 120000 2006/01/26 4 amini 30 120000 5 amini 30 120000 I want to update table as follow: fldmonth fldname fldworkdays fldsalary flddatediff fldsalarydiff 1 peter 30 30000 2 peter 30 30000 3 peter 30 30000 4 peter 30 45000 2006/02/21 20000 5 peter 30 45000 1 amini 30 90000 2 amini 30 90000 3 amini 30 120000 2006/01/26 35000 4 amini 30 120000 5 amini 30 120000 for example in month 4 we understand that peter's monthly salary has raised from 30000 to 45000 from 2006/02/11 and in this month we must calculate difference of his salary. our salary is monthly and for 30 days eventhough the month is 29,30 or 31 days. We don’t have multiple years of data and for the new year we copy the records of table for only month 12 for each person to a new table in new database for month 1. For february, January and other months we have only 30 days for salary. Salary difference for peter:((45000-30000)/30)*((2006/04/30-2006/02/21)-30)=20000 Salary difference for amini:((120000-90000)/30)*((2006/03/30-2006/01/26)-30)=35000 Thanks you for your help amini > I searched for and found the previous thread in this newsgroup. I did not > see the information I requested there. [quoted text clipped - 62 lines] > >> > Month 3 20 day > >> > Is there a VBA code or query for this problem? |
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.