How do I calculate the difference in values in my report or query

  • Thread starter Charles at Cambridge Food
  • Start date
C

Charles at Cambridge Food

I have a report that gives me the total turnover per department for a number
of stores in a supermarket chain on both a monthly and yearly basis. Can
anyone tell me how I could calculate the percentage differences from eg
Janauary 2005 and January 2006?

Store Name Month Year Butchery % Bakery % Supermarket % Fruit & Vegetables
% Liqour % Nett %
Andys January 2002 42.32% 17.76% 39.92% 0.00% 0.00% 100.00%
Andys January 2003 32.63% 16.25% 51.11% 0.00% 0.00% 100.00%
Andys January 2004 24.26% 9.49% 66.25% 0.00% 0.00% 100.00%
Andys January 2005 34.54% 8.76% 56.70% 0.00% 0.00% 100.00%
Andys January 2006 35.32% 8.38% 56.29% 0.00% 0.00% 100.00%
Andys February 2002 40.62% 17.32% 42.07% 0.00% 0.00% 100.00%
Andys February 2003 31.19% 14.98% 53.82% 0.00% 0.00% 100.00%
Andys February 2004 24.67% 9.43% 65.90% 0.00% 0.00% 100.00%
Andys February 2005 33.79% 8.22% 57.98% 0.00% 0.00% 100.00%
Andys February 2006 34.52% 7.79% 57.69% 0.00% 0.00% 100.00%
Andys March 2001 46.08% 7.09% 46.83% 0.00% 0.00% 100.00%
Andys March 2002 38.78% 17.93% 43.29% 0.00% 0.00% 100.00%
Andys March 2003 24.40% 12.47% 63.13% 0.00% 0.00% 100.00%
Andys March 2004 27.01% 9.88% 63.11% 0.00% 0.00% 100.00%
Andys March 2005 33.79% 8.13% 58.08% 0.00% 0.00% 100.00%
Andys March 2006 34.86% 7.56% 53.37% 4.21% 0.00% 100.00%
 
D

Duane Hookom

I created a small table "tblStoreTurnover" with fields of:
StoreName, Month, Year, Butchery, Bakery

I then created a query "qselYearPrev" of the table that adds 1 to the Year
field:
SELECT tblStoreTurnover.StoreName, tblStoreTurnover.Month,
[Year]+1 AS YearPrev, tblStoreTurnover.Butchery AS ButcherPrev,
tblStoreTurnover.Bakery AS BakeryPrev
FROM tblStoreTurnover;

Then create the final query that LEFT JOINs tblStoreTurnover to qselPrevYear
on StoreName, Month, and Year->YearPrev:
SELECT tblStoreTurnover.StoreName, tblStoreTurnover.Month,
tblStoreTurnover.Year, tblStoreTurnover.Butchery, tblStoreTurnover.Bakery,
qselYearPrev.YearPrev, qselYearPrev.ButcherPrev, qselYearPrev.BakeryPrev
FROM tblStoreTurnover LEFT JOIN qselYearPrev ON
(tblStoreTurnover.Year = qselYearPrev.YearPrev) AND
(tblStoreTurnover.Month = qselYearPrev.Month) AND
(tblStoreTurnover.StoreName = qselYearPrev.StoreName);

This last query will have all values with previous year values in a single
row.
--
Duane Hookom
MS Access MVP

"Charles at Cambridge Food" <Charles at Cambridge
(e-mail address removed)> wrote in message
news:[email protected]...
 
C

Charles at Cambridge Food

Thanks Duane, I will give it a try and let you know if it works for me.
Regards - Charles

Duane Hookom said:
I created a small table "tblStoreTurnover" with fields of:
StoreName, Month, Year, Butchery, Bakery

I then created a query "qselYearPrev" of the table that adds 1 to the Year
field:
SELECT tblStoreTurnover.StoreName, tblStoreTurnover.Month,
[Year]+1 AS YearPrev, tblStoreTurnover.Butchery AS ButcherPrev,
tblStoreTurnover.Bakery AS BakeryPrev
FROM tblStoreTurnover;

Then create the final query that LEFT JOINs tblStoreTurnover to qselPrevYear
on StoreName, Month, and Year->YearPrev:
SELECT tblStoreTurnover.StoreName, tblStoreTurnover.Month,
tblStoreTurnover.Year, tblStoreTurnover.Butchery, tblStoreTurnover.Bakery,
qselYearPrev.YearPrev, qselYearPrev.ButcherPrev, qselYearPrev.BakeryPrev
FROM tblStoreTurnover LEFT JOIN qselYearPrev ON
(tblStoreTurnover.Year = qselYearPrev.YearPrev) AND
(tblStoreTurnover.Month = qselYearPrev.Month) AND
(tblStoreTurnover.StoreName = qselYearPrev.StoreName);

This last query will have all values with previous year values in a single
row.
--
Duane Hookom
MS Access MVP

"Charles at Cambridge Food" <Charles at Cambridge
(e-mail address removed)> wrote in message
I have a report that gives me the total turnover per department for a
number
of stores in a supermarket chain on both a monthly and yearly basis. Can
anyone tell me how I could calculate the percentage differences from eg
Janauary 2005 and January 2006?

Store Name Month Year Butchery % Bakery % Supermarket % Fruit & Vegetables
% Liqour % Nett %
Andys January 2002 42.32% 17.76% 39.92% 0.00% 0.00% 100.00%
Andys January 2003 32.63% 16.25% 51.11% 0.00% 0.00% 100.00%
Andys January 2004 24.26% 9.49% 66.25% 0.00% 0.00% 100.00%
Andys January 2005 34.54% 8.76% 56.70% 0.00% 0.00% 100.00%
Andys January 2006 35.32% 8.38% 56.29% 0.00% 0.00% 100.00%
Andys February 2002 40.62% 17.32% 42.07% 0.00% 0.00% 100.00%
Andys February 2003 31.19% 14.98% 53.82% 0.00% 0.00% 100.00%
Andys February 2004 24.67% 9.43% 65.90% 0.00% 0.00% 100.00%
Andys February 2005 33.79% 8.22% 57.98% 0.00% 0.00% 100.00%
Andys February 2006 34.52% 7.79% 57.69% 0.00% 0.00% 100.00%
Andys March 2001 46.08% 7.09% 46.83% 0.00% 0.00% 100.00%
Andys March 2002 38.78% 17.93% 43.29% 0.00% 0.00% 100.00%
Andys March 2003 24.40% 12.47% 63.13% 0.00% 0.00% 100.00%
Andys March 2004 27.01% 9.88% 63.11% 0.00% 0.00% 100.00%
Andys March 2005 33.79% 8.13% 58.08% 0.00% 0.00% 100.00%
Andys March 2006 34.86% 7.56% 53.37% 4.21% 0.00% 100.00%
Click to expand...
Click to expand...
 
Top