Hello,

How are you today?

I am trying to generate a report which looks like this:

March
Country 2002 2003 Change % Difference
USA 20 30 10 50%
Australia 20 5 10 -75%
Total 40 35 20

April
Country 2002 2003 Change % Difference
USA 20 30 10 50%
Australia 20 10 10 -50%
Total 40 41 20

% Difference is calculated using: (2003figure-2002figure)/2002figure * 100

I am not sure if I can calculate change and %Difference using SQL, worse case I can use a report writer to calculate these.

I ran this query

SELECT Book1.COUNTRY, Count(Book1.ED_CARD) AS Total, DatePart('yyyy',[ARRIVAL_DATE],1,0) AS [Year], DatePart('m',[ARRIVAL_DATE],1,0) As Month
FROM Book1
WHERE (((DatePart('yyyy',[ARRIVAL_DATE],1,0))=[PreviousYear] Or (DatePart('yyyy',[ARRIVAL_DATE],1,0))=[CurrentYear]))
GROUP BY Book1.COUNTRY, DatePart('yyyy',[ARRIVAL_DATE],1,0), DatePart('m',[ARRIVAL_DATE],1,0);

where I can enter 2002 for PreviousYear and 2003 for CurrentYear at run time

which gives me a list:

Country Total Year Month
USA 20 2002 5
USA 30 2003 5
Australia 10 2003 6


1. Is it possible to generate my report in the format I specified?
2. Can I run the calculations which I have described using SQL?

Feel free to respond with any questions