Previous Year Data Query Expression

[NotReese]

I need an expression that will pull previous year's assets into the same row as its current year data. All of the data that I have is for the current year (e.g. Payments, Assets, Liabilities, Expenses, etc. for 2010, 2011, and 2012) but I need to pull in 2009 assets into the same database row as the rest of the 2010 data (2010 assets into the 2011 data, 2011 assets into the 2012 data, etc.). Right now I 'm having to copy and paste this information manually into a new column once the query is exported to Excel which is getting cumbersome.



I figure there has to be a straightforward solution to this but after hours of searching support sites I have come up with absolutely nothing. Is there some sort of lag or "Year-1" expression that would help me solve this issue?

[rpeare]

I think what you're asking for is possible, but I've got no clue what your data structure is or what your SQL statments are.

Is there an chance you can provide a sample database.

Off the top of my head I can think of a couple of things to do, the first is if your current query is deriving the year from a field on a form or from a [enter the year] type criteria you can create the exact same query for a year prior (just using [Enter the year] -1 as a criteria for example) then linking that 'previous year' query to your current year query and just adding the fields necessary to give you the result you want.

[NTC]

It requires that there is a cross referencing ID that is common year to year and unique line to line....such as an Account#. Presuming that exists make a single year query for each year needed...i.e. 2010q 2011q etc.. and then create a new query using those annual queries as the tables. Join them by the Account# and then add the fields you want to see.

[NotReese]

Thanks folks, I'm close to figuring it out I think. I'll post again if I run into any unforeseen issues.

[Dal Jeanis]

The general answer is

SELECT 
   T1.KeyField, 
   T1.YearField, 
   T1.Field1, 
   T1.Field2, 
   T1.Field3, 
   T2.YearField, 
   T2.Field1, 
   T2.Field2, 
   T2.Field3
FROM
   MyTable As T1
   INNER JOIN 
   MyTable as T2
   ON T1.KeyField = T2.KeyField
   AND T1.Yearfield = T2.YearField + 1;

Of course, MyTable may need to be an aggregate query that sums up all the individual transactions or something.