Compute year growth for a field

[jfca283]

Hi
I'm lost. That's the beginning.
I need to compute the increase for the field Area the following:
yyz(t)-yyz(t-12).
T es month. The table has this structure
Area yyz date Y
Area represents different locations. Each area has only one record per month. Month is obviously the field Date. And Y is yyz(t)-yyz(t-12).
I tried to use DateAdd on access 2013 but the function seems strange, and besides, i don't know how to link
Area yyz(t) date Y:yyz(t)-yyz(t-12).
I just want to create a query in which i can see for each record his yyz value this month and yyz the last year on Y.
I hope i made myself clear.
I'm really a new using access.
Thanks for time and interest.

 

[plog]

Sample data would make this more clear. Post 2 sets of data:

A: Sample starting data that would be in your table.
B: Resulting data that should be generated by your query when you feed it A.

Use this format for posting sample data:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 2/13/2009, 91
Larry, 5/15/2008, 44
Sally, 6/17/2007, 31

 

[jfca283]

Sheet1 has the data i need to process.
Sheet2 has what i would like to get.
I just need to obtain the monthly increase for a variable, in this case, yyz.
Again, it's just calculate the differences among each Area record ( A, B, C, D, ...) . Each record is asociated with a date, which is unique. I mean, there no 2 inputs for A on 1-1-99. No, there is only one.
I really need to get that field.
Then i could by myself process differences of different order, like yaer increases, quarterly increases.
The big issue is i don't know how to work with the DataAdd function and all i tried was pointless.
Thank you very much for the interest related to this topic. Really.

 

[plog]

First, 'Date' is a horrible name for a field, because it is a reserved word (http://support.microsoft.com/kb/286335) and will cause you issues when you write queries or code. I suggest renaming it by prefixing it with what the Date is for (e.g. AreaDate, IncreaseDate, etc.)

For your data, you are going to need a subquery, the point of which to determine the prior date to use in your calculation. This is the SQL for that subQuery:

SELECT Area, yyz, Date, CDate(IIf(IsNull(DMax("[Date]","YourTableNameHere","[Area]='" & [Area] & "' AND [Date]<#" & [Date] & "#")),DMin("[Date]","YourTableNameHere")-1,DMax("[Date]","YourTableNameHere","[Area]='" & [Area] & "' AND [Date]<#" & [Date] & "#"))) AS PriorDate
FROM YourTableNameHere;

Paste that into a query, replace all instances of 'YourTableNameHere' with the name of your table and save it as 'subIncrease'. The key is the PriorDate field. It determines the date of the record just prior to the current one. I did not use DateAdd, because I don't trust your data to have every single date represented (i.e. 12/25/2009, or any other holiday). Using my method will ensure the prior date is used, if even it isn't a date immediately prior.

Next, use this SQL to get the results you want:

SELECT subIncrease.Area, subIncrease.yyz, subIncrease.Date, [subIncrease].[yyz]-[YourTableNameHere].[yyz] AS Increase
FROM subIncrease LEFT JOIN YourTableNameHere ON (subIncrease.Area = YourTableNameHere.Area) AND (subIncrease.PriorDate = YourTableNameHere.Date)
ORDER BY subIncrease.Area, subIncrease.Date;

Replace all instances of 'YourTableNameHere' with the name of your table. This one bumps the subquery against your table and lines up the data between the 2 records (current and its prior) then subtracts the yyz value to get the increase.

Let me know if you have any questions or issues.

 

[jfca283]

It didn't work.

 

[plog]

Allow me to apologize for being unclear when I said 'Let me know if you have any questions or issues'. Technically that is an issue, so you have me there. Unfortunately, I can't do much with just 'It didn't work'. So could you expound on 'It didn't work'?

Did you get an error message? Invalid data? What makes you believe it didn't work?

 

[jfca283]

I shows me this the final query:
syntax error (missing operator) in query expression '[Area]='xxx' AND [date]<#01-05-2010#'.
(i use access in spanish, so i translated the error message)

 

[plog]

IS the field [Date] a Date/Time field or text?

 

[jfca283]

Yes. Date is a field which contains dates in the format dd/mm/yyyy.

 

[plog]

Can you post your database?

 

[jfca283]

There is the data.
The table is named like the excel file, C1_AS.
Please, consider that sometimes the field Area has spaces at the beginning, so the match isn't exact.
I really appreciate your time.
I tried to do the same with DLookUp and it collapsed.
Thanks again.

 

[plog]

I don't understand. I gave you SQL to use. Did you use that? If so, you should have it in an access file not Excel.

 

[jfca283]

Yes i did. I only posted it on xls because i thought it would be simpler to import. Sorry.