Find difference for previous year

[chris89]

Hello ,
I have a query with the following fields:
Product , Value , Month , Year
So I want to find the difference in value for same month in previous year .

Month and Year fields were calculated with :
Month: Format([DateP];"mmm")
Year: Format([DateP];"yyyy")
So I can't figure out the datatypes of the results (on the filter drop-down list there is text filters so propably these are text , although [Year]-1 gave me result e.g. if Year = 2012
[Year]-1 would give 2011

Please give me some advice on this

Thank you in advance

 

[jdraw]

Can you post the sql of your query?

 

[spikepl]

Value, Month, Year are all reserved words and you should not use them for field names.

Functions Month, Year exist and yield integer values

MyMonth=Month(MyDate)
MyYear=Year(MyDate)

 

[chris89]

SELECT queryBudgetByMonth.Product, queryBudgetByMonth.ProductionByMonth,  queryBudgetByMonth.BudgetByMonth, queryBudgetByMonth.MonthBDG,  queryBudgetByMonth.YearBDG
FROM queryBudgetByMonth;

 

[chris89]

Anyone please?

 

[Brianwarnock]

Spike gave good advice regarding names and functions, I shall ignore it for simplicity
Call your query myquery.

Drag my query into the design grid twice and access calls the second myquery_1

Then Select myquery.product, myquery.month, (myquery_1.value-myquery.value) as difference
From myquery , myquery_1

Now switch to SQL view and add the Where clause

Where myquery.month=myquery_1 .month and myquery.year = myquery_1.year-1

Brian

 

[chris89]

Thank you very much !(Always wondering why having same table/query on a relationship )
I think I should add :
Where myquery.month=myquery_1 .month and myquery.year = myquery_1.year-1 AND myquery.product=myquery_1.product
but now I have doubled entries..

Why is this happening ?
I've encountered the same problem before when I linked two queries together!
Is there a way to fix this?

 

[chris89]

Thank you very much !(Always wondering why having same table/query on a relationship )
I think I should add :
Where myquery.month=myquery_1 .month and myquery.year = myquery_1.year-1 AND myquery.product=myquery_1.product
but now I have doubled entries..

Why is this happening ?
I've encountered the same problem before when I linked two queries together!
Is there a way to fix this?

Ok I think I figured out what the cause was..Products should be Grouped by in the query I queried from ! lol

 

[Brianwarnock]

Ah ! You spotted my deliberate mistake

I had intended to join the queries on product but guess I forgot,

Glad you sorted it, too many posters just come back and say it didn't work.

Brian

 

[chris89]

Ah ! You spotted my deliberate mistake

I had intended to join the queries on product but guess I forgot,

Glad you sorted it, too many posters just come back and say it didn't work.

Brian

Thanks again!