Calculate difference in Sales by comparing month yearwise

  • Thread starter Thread starter Max
  • Start date Start date
M

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
 
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
 
Back
Top