populate previous fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aminihojat - 29 Sep 2006 11:48 GMT

Hi all,
When I enter 65 in totalday's field for month 3 I'd like 65 distributed
30,30 and 5 for month 3,2 and 1. formula is so: (65=30+30+5)
Fields: name month totalday workday
Records: jack 1 5
jack 2 30
jack 3 65 30
would you please help me?

Reply to this Message

Duane Hookom - 29 Sep 2006 15:02 GMT

UPDATE tblNoNameGiven
SET Workday = IIf([Month]=1, 5, 30)
WHERE [name]="Jack" AND [Month] Between 1 and 3;

BTW: you didn't give us any specifications for your distribution. Also name
and month are poor field names since one is a property of every object and
the other is a function name.

Signature

Duane Hookom
MS Access MVP

> Hi all,
> When I enter 65 in totalday's field for month 3 I'd like 65 distributed
[quoted text clipped - 4 lines]
> jack 3 65 30
> would you please help me?

Reply to this Message

aminihojat - 29 Sep 2006 15:36 GMT

thanks Duane Hookom
your response was helpful but if I wanted to enter 65 in month 4 I'd like
these results:30 for month 4 , 30 for month 3 and 5 for month 2 .
but not 5 for month 1

> UPDATE tblNoNameGiven
> SET Workday = IIf([Month]=1, 5, 30)
[quoted text clipped - 12 lines]
> > jack 3 65 30
> > would you please help me?

Reply to this Message

Duane Hookom - 29 Sep 2006 19:19 GMT

You are getting a little less vague. Do you always enter 65? Why divide the
values as you stated? Why not 20, 40, 5?

You apparently know your logic but are making this a puzzle for the rest of
us.

Signature

Duane Hookom
MS Access MVP

> thanks Duane Hookom
> your response was helpful but if I wanted to enter 65 in month 4 I'd like
[quoted text clipped - 19 lines]
>> > jack 3 65 30
>> > would you please help me?

Reply to this Message

aminihojat - 29 Sep 2006 20:12 GMT

Dear Duane Hookom
If my problem is vague please excuse me. I'm a beginner and I can write
English a little. 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
calculate 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?

> You are getting a little less vague. Do you always enter 65? Why divide the
> values as you stated? Why not 20, 40, 5?
[quoted text clipped - 24 lines]
> >> > jack 3 65 30
> >> > would you please help me?

Reply to this Message

Duane Hookom - 30 Sep 2006 04:50 GMT

I would create a table of dates "tblDates" with a single date field
"theDate". Enter all the dates from the entire range you will ever need. You
can then create a query that limits the returned records to a range of from
the start date to the current date.

Where is other information stored such as the start date or days worked or
whatever?

Signature

Duane Hookom
MS Access MVP

> Dear Duane Hookom
> If my problem is vague please excuse me. I'm a beginner and I can write
[quoted text clipped - 43 lines]
>> >> > jack 3 65 30
>> >> > would you please help me?

Reply to this Message