Calculate difference in Sales by comparing month yearwise

[Max]

Hi, I am having dataset like this, Period field Type is 'Text'

Product-Period-Sales
X-2007/01-10000
X-2007/02-5000
X-2008/01-15000
X-2008/02-3000

I would like generate final output that will show the difference of sales by
comparing current year month (2008) with last year month. Please suggest me
query.

Thanx in advance

 

[Allen Browne]

You can use a subquery to get the value from the same month a year ago.

Firstly you will need to convert the text into a real date so can operate on
it. Create a query using your existing table, and type this expression into
the Field row in query design:
TheMonth: CDate([Period] & "/01")
Save the query as (say) Query1.
Close.

Now create another query using Query1 as an input 'table.'
The query will be like this:

SELECT Query1.*,
(SELECT Sales
FROM Query1 AS Dupe
WHERE Dupe.Product = Query1.Product
AND Dupe.TheMonth = DateAdd("m", -12, Query1.TheMonth))
FROM Query1;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html#AnotherRecord