converting year to date data to month to date

[ram]

I would like help with the following problem: I ‘m trying to have Access
calculate the month to date (MTD) value for each month. The result for each
month should be 10. I have tried to use the suggestions provided and
variations of the suggestion but haven’t been successful.

Thank you in advance for any help with this problem.


Table1

Employee Date1 Year to Date Hours MTD

Bill 1/1/08 10

Bill 2/1/08 20

Bill 3/1/08 30

Bill 4/1/08 40

Bill 5/1/08 50

Bill 6/1/08 60


Code I’m currently using:

SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, [YTD]- (SELECT Sum(YTD)
AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 > Dupe.Date1 AND Table1.Employee = Dupe.Employee)

AS MTD
From Table1
WHERE [Date1] > DateSerial(Year(Date()), 1, 1);

 

[Wayne-I-M]

Hi

I don't understand this bit

The result for each month should be 10

What are you rying to get ??

Have you tried to simplify the process like this

SELECT Table1.Employee, Table1.Date1, DateDiff("m",[Table1]![Date1],Now())
AS MTD
FROM Table1;

Can you explain what you are trying to get in a different way.

--
Wayne
Manchester, England.

I would like help with the following problem: I ‘m trying to have Access
calculate the month to date (MTD) value for each month. The result for each
month should be 10. I have tried to use the suggestions provided and
variations of the suggestion but haven’t been successful.

Thank you in advance for any help with this problem.


Table1

Employee Date1 Year to Date Hours MTD

Bill 1/1/08 10

Bill 2/1/08 20

Bill 3/1/08 30

Bill 4/1/08 40

Bill 5/1/08 50

Bill 6/1/08 60


Code I’m currently using:

SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, [YTD]- (SELECT Sum(YTD)
AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 > Dupe.Date1 AND Table1.Employee = Dupe.Employee)

AS MTD
From Table1
WHERE [Date1] > DateSerial(Year(Date()), 1, 1);

 

[ram]

Thank you for trying to help.

In Table1 The field year to Date hours equals the curent month plus the
previous month. So for each month it is increasing by 10. The result I'm
trying to find is the month to date hours which would be the current month
less the previous month; I can't seem to get the subtraction to work
correctly.

Thanks again.

Hi

I don't understand this bit

The result for each month should be 10

What are you rying to get ??

Have you tried to simplify the process like this

SELECT Table1.Employee, Table1.Date1, DateDiff("m",[Table1]![Date1],Now())
AS MTD
FROM Table1;

Can you explain what you are trying to get in a different way.

--
Wayne
Manchester, England.

I would like help with the following problem: I ‘m trying to have Access
calculate the month to date (MTD) value for each month. The result for each
month should be 10. I have tried to use the suggestions provided and
variations of the suggestion but haven’t been successful.

Thank you in advance for any help with this problem.


Table1

Employee Date1 Year to Date Hours MTD

Bill 1/1/08 10

Bill 2/1/08 20

Bill 3/1/08 30

Bill 4/1/08 40

Bill 5/1/08 50

Bill 6/1/08 60


Code I’m currently using:

SELECT TABLE1.Employee, TABLE1.Date1, TABLE1.YTD, [YTD]- (SELECT Sum(YTD)
AS SumOfYTD
FROM TABLE1 AS Dupe
WHERE TABLE1.Date1 > Dupe.Date1 AND Table1.Employee = Dupe.Employee)

AS MTD
From Table1
WHERE [Date1] > DateSerial(Year(Date()), 1, 1);

 

[elizabeth]

what?