fill in previouse records

[aminihojat]

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?

 

[John Spencer]

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????]

 

[aminihojat]

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?

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.
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?

 

[John Spencer]

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
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?

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.
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?

 

[aminihojat]

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.

Good luck on solving your 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?

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????]



aminihojat wrote:

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?