How to show percent variation over time

[Sidnei B Cordeiro]

Can somebody help me with this subject? My client tracks monthly exchange
for Euro and UK Pound, and relates the values to its purchases. He wants to
know the % variation of the value he pays for a item, lets say in six or
four months periods, and compare it to the currency variation. I have two
tables that looks like this:

tbl_Exchange
Date Euro UK Pound
01/01/04 75 50
02/01/04 77 52
03/01/04 78 51
04/01/04 79 53

tbl_Purchase
Item# Date US$
1010 01/01/04 20
1010 02/01/04 21
1010 03/01/04 22
1010 04/01/04 23

What my client wants to see, in a graphic chart, is that while the price for
item 1010 had increased 15%, Euro had varied 5,3% and Pound had varied 6%.
The client also wants to report variable range of time (quarterly, four, six
months or even one, two years).
I think using a multidimensional array would be a solution, but I can't
figure how to do this. Directly in the query, passing the number of month as
parameters, is also dificult, because I can't query the previous value (and
this would be a group query).
Can somebody help me? Can this be done in query? Or a PivotTable/PivotChart
would be a better choice?

 

[Damon Heron]

I'm thinking this would be so much easier to do in Excel or similar
spreadsheet. There is no real relation between the Exchange table and the
Purchase table... Why not just use a flat file spreadsheet, and the
excellent graphing capabilities of Excel, to show what you want? Just a
thought...... If this is data that has been gathered in Access for some
time, you can still export it to Excel and manipulate it there.

Damon

 

[Dale Fye]

Sidnei,

Are the date fields always going to match between the two tables? BTW, I
recommend against using the name [Date] as a field name, since it is an
Access reserved word, and can cause problems with queries. I assume that
the you have a form or something where the client determines the period over
which he wants to view this information.

When you say he wants to report over a variable range, I assume that what
you are talking about is that he wants to use some date in the past (3, 4,
6, 12, 24 months) as the baseline against which to compare all of the other
values, then create columns in the report that show the Cost
increase/Decrease as well as the change in Euro and Pound over the same
period. So, start out with a textbox or a combo box on the form indicating
the period to be analyzed.

Let me try to give you a starting point in the limited time I have
available.

SELECT E.ExchangeDate
, E.Euro AS Euro_Exch_Rate
, EB.Euro AS Euro_Exch_Base
, ([E].[Euro]-[EB].[Euro])/[EB].[Euro] AS PctChange
, "Euro" as ColSource
FROM (SELECT E1.ExchangeDate, E1.Euro
FROM tbl_Exchange E1
WHERE E1.ExchangeDate = DateAdd("m", -1 * me.txt_Period,
DateSerial(Year(Date()), Month(Date()), 1))) AS EB, tbl_Exchange AS E
WHERE E.ExchangeDate>[EB].[ExchangeDate]
ORDER BY E.ExchangeDate;

This will give you a result that looks like the following, for the data you
provided (with a couple of other months thrown in).

ExchangeDate Euro_Exch_Rate Euro_Exch_Base PctChange ColSource
2/1/04 77 75 2.67%
Euro
3/1/04 78 75 4.00%
Euro
4/1/04 79 75 5.33%
Euro
5/1/04 80 75 6.67%
Euro
6/1/04 81 75 8.00%
Euro

You could expand this by making it a Union query to get the same information
for the Pound and the Purchase price (make sure you change the ColSource
text). Then, use this query as the source of a cross tab query that would
give you something like:

Date Euro_Change Pound_Change Price_Change
2/1/04 2.67% X% Y%
3/1/04 4.00% A% B%
4/1/04 5.33% C% D%
....

You could then use access to graph this, or export it to Excel.

HTH
Dale

 

[Sidnei B Cordeiro]

Well, Damon, thank you for you advice. I also think the same as you and
probably will do the graphics in Excel. But before sending data to Excel, I
need to 'prepare' it in Access.
The data is imported from a Progress database server via ODBC. I could
create an ODBC datasource and a MS Query in Excel, but it would not be
practical to the final user.
Also, I apologise for not have being so clear in my poor writen english:
When I said my client wants 'a variable period' I was meaning a "variabel
interval of time". The range of data will always the same: the last two year
before the system date. But the intervals will vary and can be 2 months, 3
months, 4 months or 6 months, input by the user.

 

[Sidnei B Cordeiro]

Dale,

thank you for your advice. It worked as you said, but I'll have to do some
tweaks.
As I need to calculate the percent variation in the INTERVAL of time that
the user will setup, the "base" value will vary from query to query. If the
the interval is 3 months, your example will turn to:

ExchDate Exch_Rate Exch_Base PctChange PctAc ColSource
1/1/04 75 72 4.16% 4.16%
Euro
4/1/04 79 75 5.33% 9.72%
Euro
7/1/04 81 79 2.53% 12.50%
Euro
10/1/04 84 81 3.70% 16.66% Euro

Sidnei,

Are the date fields always going to match between the two tables? BTW, I
recommend against using the name [Date] as a field name, since it is an
Access reserved word, and can cause problems with queries. I assume that
the you have a form or something where the client determines the period over
which he wants to view this information.

When you say he wants to report over a variable range, I assume that what
you are talking about is that he wants to use some date in the past (3, 4,
6, 12, 24 months) as the baseline against which to compare all of the other
values, then create columns in the report that show the Cost
increase/Decrease as well as the change in Euro and Pound over the same
period. So, start out with a textbox or a combo box on the form indicating
the period to be analyzed.

Let me try to give you a starting point in the limited time I have
available.

SELECT E.ExchangeDate
, E.Euro AS Euro_Exch_Rate
, EB.Euro AS Euro_Exch_Base
, ([E].[Euro]-[EB].[Euro])/[EB].[Euro] AS PctChange
, "Euro" as ColSource
FROM (SELECT E1.ExchangeDate, E1.Euro
FROM tbl_Exchange E1
WHERE E1.ExchangeDate = DateAdd("m", -1 * me.txt_Period,
DateSerial(Year(Date()), Month(Date()), 1))) AS EB, tbl_Exchange AS E
WHERE E.ExchangeDate>[EB].[ExchangeDate]
ORDER BY E.ExchangeDate;

This will give you a result that looks like the following, for the data you
provided (with a couple of other months thrown in).

ExchangeDate Euro_Exch_Rate Euro_Exch_Base PctChange ColSource
2/1/04 77 75 2.67%
Euro
3/1/04 78 75 4.00%
Euro
4/1/04 79 75 5.33%
Euro
5/1/04 80 75 6.67%
Euro
6/1/04 81 75 8.00%
Euro

You could expand this by making it a Union query to get the same information
for the Pound and the Purchase price (make sure you change the ColSource
text). Then, use this query as the source of a cross tab query that would
give you something like:

Date Euro_Change Pound_Change Price_Change
2/1/04 2.67% X% Y%
3/1/04 4.00% A% B%
4/1/04 5.33% C% D%
...

You could then use access to graph this, or export it to Excel.

HTH
Dale

Can somebody help me with this subject? My client tracks monthly exchange
for Euro and UK Pound, and relates the values to its purchases. He wants to
know the % variation of the value he pays for a item, lets say in six or
four months periods, and compare it to the currency variation. I have two
tables that looks like this:

tbl_Exchange
Date Euro UK Pound
01/01/04 75 50
02/01/04 77 52
03/01/04 78 51
04/01/04 79 53

tbl_Purchase
Item# Date US$
1010 01/01/04 20
1010 02/01/04 21
1010 03/01/04 22
1010 04/01/04 23

What my client wants to see, in a graphic chart, is that while the price for
item 1010 had increased 15%, Euro had varied 5,3% and Pound had varied 6%.
The client also wants to report variable range of time (quarterly, four, six
months or even one, two years).
I think using a multidimensional array would be a solution, but I can't
figure how to do this. Directly in the query, passing the number of

month

as
parameters, is also dificult, because I can't query the previous value (and
this would be a group query).
Can somebody help me? Can this be done in query? Or a PivotTable/PivotChart
would be a better choice?