Previous year value in columns (1 Viewer)

[russiver]

Hi all,

Simple example of what I'm trying to achieve...

Table with two fields:

read_date---read_value
--------------------------
01/01/13-----10
01/02/13-----20
01/01/14-----30
01/02/14-----40

Would like to create a query or queries that summarises the data as follows:

read_date--2014--2013
------------------------------
01/01/14----30----10
01/02/14----40----20

In words, read_date would list dates for the current year. Second column of query would give read_value for current year. Third column would give read_value for same day/month of previous year.

Have thought about using Dlookup to get value one year back, but not sure if is the best approach?

Thanks,

Russ

 

[CJ_London]

You need to use a subquery or dlookup

This includes a subquery

SELECT ReadDate, ReadValue AS [2014], (SELECT First(ReadValue) FROM myTable as T WHERE ReadDate=DateAdd('y',-1,myTable.ReadDate) AS [2013]
FROM myTable

If you want to use Dlookup then see this link which asks pretty much the same question

http://www.access-programmers.co.uk/forums/showthread.php?t=272034

Third column would give read_value for same day/month of previous year.

What about leap years?

 

[plog]

I think the best way is with a subquery:

SELECT YourTableNameHere.read_date, YourTableNameHere.read_value, DateAdd("yyyy",-1,[read_date]) AS PriorYearDate
FROM YourTableNameHere;

Paste that into a query and save it as 'ReadByDate_sub1'. Be sure to replace 'YourTableNameHere' with the name of your actual table.

It prepares the data so that it can find the prior year's date for each record. Then to get the results you want, this query will do it:

SELECT ReadByDate_sub1.read_date, ReadByDate_sub1.read_value AS CurrentYearValue, 1*Nz([ReadByDate_sub1_1].[read_value],0) AS PriorYearValue
FROM ReadByDate_sub1 LEFT JOIN ReadByDate_sub1 AS ReadByDate_sub1_1 ON ReadByDate_sub1.PriorYearDate = ReadByDate_sub1_1.read_date
WHERE (((Year([ReadByDate_sub1].[read_date]))=Year(Date())));

To use this, read_date must be unique in the table. Otherwise you will need to perform an aggregate query to sum up all the values on that date, then use this method.

 

[JHB]

Have also a look into the crosstab query.

TRANSFORM First(read_Value) AS FirstOfread_Value
SELECT Format([read_date],"mm\/dd") AS MonthAndDay
FROM YourTableName
GROUP BY Format([read_date],"mm\/dd")
ORDER BY Year([read_date]) DESC 
PIVOT Year([read_date]);

 

[russiver]

Many thanks for all the replies. Have tested all suggestions. Findings as follows...

CJ_London, could not get your sql to work. Tried several minor alterations, but the following error kept presenting. Any ideas?

Plog, read_date is unique and your code works fine.

JHB, crosstab works very well.

Russ

 

[CJ_London]

there should be 2 brackets after read_date - my mistake

 

[russiver]

there should be 2 brackets after read_date - my mistake

Thanks. I should have spotted that.

One final question. Why do you need the table alias -

FROM myTable as T WHERE

Is it because the subquery uses the same table as the main query?

 

[CJ_London]

Is it because the subquery uses the same table as the main query?

Correct

 

[Galaxiom]

This includes a subquery

SELECT ReadDate, ReadValue AS [2014], (SELECT First(ReadValue) 
FROM myTable as T 
WHERE ReadDate=DateAdd('y',-1,myTable.ReadDate) AS [2013]
FROM myTable

That is a correlated subquery. I would normally do this kind of thing with a self join.

SELECT T1.ReadDate, ReadValue AS [2014], T2.ReadDate AS [2013]
FROM myTable as T1
INNER JOIN myTable AS T2
ON T2.ReadDate=DateAdd('y',-1,T1.ReadDate)

I really don't know if it is a better solution.

The engine might even decide to run the same query for both versions after optimisation. I would be curious if anyone has compared the performance of a correlated subquery versus a self join.

JHB's crosstab could be a better solution because it doesn't hardcode the years so it will keep working year after year.

If you want to use Dlookup then see this link which asks pretty much the same question

A DLookup is a very inefficient solution. It is like running a separate isolated query for each record. The engine gets the whole job as a single query in the other techniques.

 

[CJ_London]

I really don't know if it is a better solution.

I use both types and in my experience it does depend on the situation - the bigger the data set of the subquery the more a self join comes to the fore.

There are times when I don't believe you can use a self join - if you can, I'll be most interested to know how where for example there are missing dates - might be a price table where you want to know the price on a particular date - something like this

SELECT eTable.Item, eTable.eDate, pTable.FromDate, pTable.Price 
FROM eTable INNER JOIN pTable ON eTable.Item=pTable.Item
WHERE pTable.FromDate=(SELECT Max(FromDate) FROM pTable as T WHERE FromDate<=eTable.eDate AND item=eTable.item)

 

[russiver]

Galaxiom,

Checked out your code

That is a correlated subquery. I would normally do this kind of thing with a self join.

SELECT T1.ReadDate, ReadValue AS [2014], T2.ReadDate AS [2013]
FROM myTable as T1
INNER JOIN myTable AS T2
ON T2.ReadDate=DateAdd('y',-1,T1.ReadDate)

and it produces the following error:

The specified field ReadValue could refer to more than one table listed in the FROM clause of your SQL statement.

Any ideas what is causing this?

 

[Galaxiom]

It needed the source table specified because the field could be in either table.
I also changed it to get the right field for 2013 and added an order by clause.

SELECT T1.ReadDate, T1.ReadValue AS [2014], T2.ReadValue AS [2013]
FROM myTable as T1
INNER JOIN myTable AS T2
ON T2.ReadDate=DateAdd('y',-1,T1.ReadDate)
ORDER BY T1.ReadDate

 

[russiver]

Thanks, that's fixed it.