Subtraction Query

[ryguy7272]

I am trying to do a simple subtraction between two tables. My SQL is below:
SELECT [Wanda - Current Week].Advertiser AS [Current Week Advertiser],
[Wanda - Current Week].SalesRegion AS [Current Week Sales Region], Sum([Wanda
- Current Week].Q409) AS [Current Week Quarter], "13" AS [Current Week],
[Wanda - Prior Week].Advertiser AS [Prior Week Advertiser], [Wanda - Prior
Week].SalesRegion AS [Prior Week Sales Region], Sum([Wanda - Prior
Week].Q409) AS [Prior Week Quarter], "12" AS [Prior Week], Sum(nz([Wanda -
Current Week]![Q409],0)-nz([Wanda - Prior Week]![Q409],0)) AS [Weekly Change]
FROM [Wanda - Current Week] INNER JOIN [Wanda - Prior Week] ON [Wanda -
Current Week].Advertiser = [Wanda - Prior Week].Advertiser
GROUP BY [Wanda - Current Week].Advertiser, [Wanda - Current
Week].SalesRegion, "13", [Wanda - Prior Week].Advertiser, [Wanda - Prior
Week].SalesRegion, "12";

The problem occurs when I subtract the ‘Prior Week Quarter’ from the
‘Current Week Quarter’ and the Sales Region is not exactly the same; then I
get a number greater than zero or less than zero. The data is actually the
same; I had one table named week 13 and one table named week 12! When all
the records in the ‘Current Week Sales Region’ do not match up, I’m getting a
number (positive or negative) for the ‘Weekly Change’ calculation. There
should be zero, but I think some calculations are non-zero because I am using
‘Group By’. Let’s say I am looking at sales to ‘Nestle’ and last week the
sales were done in the ‘East’ and this week sales are done in the ‘West’. Is
there a simple way to overcome this issue? I know everything is from Nestle,
but the ‘East’ and ‘West’ Regions are screwing me up. I haven’t dealt with
it before, and offhand I can’t think of how to handle this in the QBE grid.



Thanks,
Ryan---

 

[Jeff Boyce]

Ryan

I may be reading too much into your SQL statement...

It looks like you have tables ("between two tables") named "Wanda - Current
Week" and "Wanda - Prior Week" (unless these are queries?). If these are
tables, you are trying to commit spreadsheet in Access.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

Ryan:

Firstly you'd be far better off having a single table rather than one table
per week. You can easily extract the rows for a particular week from a date
column or you can just include the year and week numbers in columns. At
present you are encoding data (the week) as a table name. In a relational
database data should be stored as values at column positions in rows in
tables, and in no other way.

Even with your current set-up, however, you should be able to do it by means
of a subquery to return the sums for the prior week, e.g.

SELECT Advertiser, SalesRegion, 13 AS [Current Week],
SUM([Q409]) AS [Current Week Quarter],
12 AS [Prior Week],
(SELECT SUM([Q409])
FROM [Wanda - Prior Week] AS W2
WHERE W2.Advertiser = W1.Advertiser
AND W2.SalesRegion = W1.SalesRegion)
AS [Prior Week Quarter],
[Current Week Quarter] - [Prior Week Quarter] AS [Weekly Change]
FROM [Wanda - Current Week] AS W1
GROUP BY Advertiser, SalesRegion;

This query will need redefining for every week of course. With a single
table you can have a generic query in which the year and week number are
input by the user as parameters at runtime. The query would be very similar
to the above, but would use two instances of the same table, with the outer
query and subquery being restricted to the weeks in question by means of the
parameters.

Ken Sheridan
Stafford, England

I am trying to do a simple subtraction between two tables. My SQL is below:
SELECT [Wanda - Current Week].Advertiser AS [Current Week Advertiser],
[Wanda - Current Week].SalesRegion AS [Current Week Sales Region], Sum([Wanda
- Current Week].Q409) AS [Current Week Quarter], "13" AS [Current Week],
[Wanda - Prior Week].Advertiser AS [Prior Week Advertiser], [Wanda - Prior
Week].SalesRegion AS [Prior Week Sales Region], Sum([Wanda - Prior
Week].Q409) AS [Prior Week Quarter], "12" AS [Prior Week], Sum(nz([Wanda -
Current Week]![Q409],0)-nz([Wanda - Prior Week]![Q409],0)) AS [Weekly Change]
FROM [Wanda - Current Week] INNER JOIN [Wanda - Prior Week] ON [Wanda -
Current Week].Advertiser = [Wanda - Prior Week].Advertiser
GROUP BY [Wanda - Current Week].Advertiser, [Wanda - Current
Week].SalesRegion, "13", [Wanda - Prior Week].Advertiser, [Wanda - Prior
Week].SalesRegion, "12";

The problem occurs when I subtract the ‘Prior Week Quarter’ from the
‘Current Week Quarter’ and the Sales Region is not exactly the same; then I
get a number greater than zero or less than zero. The data is actually the
same; I had one table named week 13 and one table named week 12! When all
the records in the ‘Current Week Sales Region’ do not match up, I’m getting a
number (positive or negative) for the ‘Weekly Change’ calculation. There
should be zero, but I think some calculations are non-zero because I am using
‘Group By’. Let’s say I am looking at sales to ‘Nestle’ and last week the
sales were done in the ‘East’ and this week sales are done in the ‘West’. Is
there a simple way to overcome this issue? I know everything is from Nestle,
but the ‘East’ and ‘West’ Regions are screwing me up. I haven’t dealt with
it before, and offhand I can’t think of how to handle this in the QBE grid.



Thanks,
Ryan---