Expression to extract data

[dwatt]

Hi, hope someone can help…


Want to create a calculated field called 'PreviousN' which will take 10% of the value from the 'UpdatedN' field from previous year (2002) add it to current 'N' to give 'UpdatedN' for current year (2003) etc…

Year........PreviousN.....N.........UpdatedN
2002............0..............40..........40
2003............4..............35...........39
2004............3.9...........21...........24.9



Can I use a criteria expression to carry out the above function or any other method?

ie., For (currentyear - 1) select UpdatedN and put 10% of value in PreviousN for current year

 

[namliam]

Possible but...

You will need to do an innerjoin with another instance of the same query... If that makes sence to you.... (maybe post a sample?)

Regards

 

[dcx693]

I agree with namliam, you can create a query that joins the data table to a copy of itself, thereby allowing you to match up a given year with a previous year.

You could also write yourself a custom function to find the previous years' data. I think you can also use Dlookup functions, but that would be my last resort.

 

[dwatt]

Namliam/dcx693
You suggested using an instance of same query or write a custom function. I am sorry but am not quite sure how to go about doing this....can you give me some guidelines.

Regards
David

 

[namliam]

Possible but...

You will need to do an innerjoin with another instance of the same query... If that makes sence to you.... (maybe post a sample?)

Regards

maybe post a sample?

Could you possibly post a bit of sample data so we dont have to make examples our selves? Taking yet more of our time to help you?

Regards

 

[dcx693]

namliam, the user did post some sample data in the original post.

dwatt, here is a link to a post about using a self-join query: variation of Running Sum in query .

 

[dwatt]

Hi
I am having problems...I have enclosed a copy of the query and form in a zipped doc.
Hope makes sense

Thank you

 

[dwatt]

Hi,
I am not a programmer so please forgive me if I appear to be fumbaling...
I have attached part of my database(44.7kb zipped).
Please goto form FieldDetail...

When the program first sets up I want to enter data for the previous year (2002) and therefor be asked for a value for PrevManNAdj. The idea is then for UpdatedManN to calculate automatically and place that value in PrevManNAdj for the current year (2003). 10% of that value is then added to NRec to give the new UpdatedManN and this value is stored in PrevManNAdj for 2004..

It was said I should use a custom function and all I can think of using is the IIF function:
I am not sure about the expression I should use for the Year but here goes...I hope you have a sense of humour...

IIF([Field_Crop].[strHarvestyear]=Date(Year),[Field_Crop].[PrevManNAdj]=[UpdatedManN].Date(Year)-1,0

Thank you for any help recieved.
(I am throwing stones in the dark)
David

 

[namliam]

dont have time to really get into the post and or file, sorry for that

to get the current year: year(date())
Prev year: Year(date())-1 or year(dateadd("YYYY",-1,date()))

Hope that helps you along.

Regards