Need Help Calculating Past 12 Months Sum

[KyleAK]

I am trying to create a basic query that will calculate a sum of sales
for the past 12 months (that will roll each month as the month
changes). I am having some trouble accomplishing this with our current
table structure:

PART DESCRIP JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC YEAR
0001 Part #1 0 25 80 14 17 0 0 0 0 0 0 0
2006
0001 Part #1 2 20 32 45 68 32 53 29 36 83 0 4
2005
0001 Part #1 9 14 46 72 53 27 57 32 54 23 19 14
2004

So, for example, since this is May 2006, the query should grab and
total sales for each PART from May 2005 through April 2006.

Any suggestions would be greatly appreciated! Thanks a million!

 

[Guest]

Change your table structure. For this data you need two tables - Part & Sales.
Part --
Part -
Description - text

Sales --
Part -
Sale - DateTime datatype
Quanity – number

Sales3
Part Sale Quanity
0001 1/1/2006 0
0001 1/2/2006 25
0001 1/3/2006 80
0001 1/4/2006 14
0001 1/5/2006 17
0001 1/6/2006 0
0001 1/7/2006 0
0001 1/8/2006 0

SELECT Sales3.Part, Sales3.Sale, Sales3.Quanity
FROM Sales3
WHERE (((Sales3.Sale) Between DateAdd("yyyy",-1,Date()-Day(Date())) And
Date()-Day(Date())));

 

[KyleAK]

Any suggestions on what to do if the table cannot be changed? We are
pulling this information in from a production application that resides

 

[John Vinson]

Any suggestions on what to do if the table cannot be changed? We are
pulling this information in from a production application that resides
on a SQL server. Thanks again!

Ugh. Your SQL DBA deserves some criticism! This structure

is dreadful.

What you can do is a "Normalizing Union" query. Assuming this table is
named Sales, you can create a query named uniAllSales:

SELECT [Part], [Descrip], DateSerial([Year], 1, 1) AS SaleDate, [JAN]
AS SaleAmount
FROM Sales
WHERE [JAN] IS NOT NULL
UNION ALL
SELECT [Part], [Descrip], DateSerial([Year], 2, 1) AS SaleDate, [FEB]
AS SaleAmount
FROM Sales
WHERE [FEB] IS NOT NULL
UNION ALL
SELECT [Part], [Descrip], DateSerial([Year], 3, 1) AS SaleDate, [MAR]
AS SaleAmount
FROM Sales
WHERE [MAR] IS NOT NULL
UNION ALL
<etc etc through the 12 fields>

You'll then be able to base your Totals query on *this* query:

SELECT [Part], [Descrip], Sum([SaleAmount]) AS TotalSales
FROM uniAllSales
WHERE SaleDate BETWEEN DateAdd("yyyy", -1, Date()) AND Date()
GROUP BY [Part], [Descrip];


John W. Vinson[MVP]

 

[KyleAK]

Thank you so much John. I'm going to mess around to try it out this
evening. The table structure is that of Microsoft Dynamics, if you
really want to know. =)

Okay, maybe I'm making this more difficult than it needs to be, but
what I'm trying to get to is:

((Sum of past 12 months) - (Sum of the 12 month period previous to the
last 12 months)) / (Sum of the 12 month period previous to the last 12
months)

Just a rolling average as a percentage change to the previous 12 month
period.

Okay, so here is my logic (obviously not in query format):

IF today is JUN then ((SUM [JAN]+[FEB]+[MAR]+[APR]+[MAY] WHERE
Year=YYYY and SUM [JUN]+[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC] Where
Year=YYYY-1) SUBTRACT (SUM [JAN]+[FEB]+[MAR]+[APR]+[MAY] WHERE
Year=YYYY-1 and SUM [JUN]+[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC] Where
Year=YYYY-2)) DIVIDED BY (SUM [JAN]+[FEB]+[MAR]+[APR]+[MAY] WHERE
Year=YYYY-1 and SUM [JUN]+[JUL]+[AUG]+[SEP]+[OCT]+[NOV]+[DEC] Where
Year=YYYY-2)

Am I out of my mind??? I just can't figure out how to get it with the
way the table is formatted.

 

[John Vinson]

I just can't figure out how to get it with the
way the table is formatted.

I can't either, not in any rational or managable way. That's why I
suggested the normalizing union query - which *will* work.

Your query criteria will be rather more complex, but to leave the
table structure alone will require twenty-four different queries, each
spanning two records...ouch!

John W. Vinson[MVP]

 

[KyleAK]

Okay, just tried to get the first section to run before I toss in the
other 11 sections:

SELECT dbo_Inventory.InvtID, dbo_Inventory.Descr,
dbo_Inventory.ClassID, dbo_Inventory.TranStatusCode,
dbo_InventoryADG.ProdLineID, DateSerial(dbo_Item2Hist.FiscYr, 1, 1) AS
SaleDate, dbo_Item2Hist.PTDQtySls00 AS SaleAmount
FROM (dbo_Inventory INNER JOIN dbo_InventoryADG ON dbo_Inventory.InvtID
= dbo_InventoryADG.InvtID) INNER JOIN dbo_Item2Hist ON
dbo_Inventory.InvtID = dbo_Item2Hist.InvtID
WHERE dbo_Item2Hist.PTDQtySls00 IS NOT NULL
UNION ALL;

When I try to run the query, Access is giving me an error: "Invalid SQL
statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or
'UPDATE'." Am I missing something? =(

Thanks again so much. If I can get this thing to work I'll be jumping
for joy. =)

 

[KyleAK]

Sorry, forgot to get rid of the last UNION ALL. Going to give the
second half of the query a shot tomorrow. THANK YOU SO MUCH AGAIN! =)

 

[KyleAK]

Okay, I got the second query to run great. I'm almost jumping for joy!

One problem: The query is adding sales for June 2006 to the total, and
not adding sales from June 2005 to the total. From my understanding,
since we are calculating a statistic (we don't want the number to
change every day), since this is June 2006, we would want to add totals
from June 2005 through May 2006. Is there a way to accomplish that? I
don't quite grasp the whole Date function yet.

And then, how would I calculate those totals for, say, June 2004
through May 2005 so that I can compare the the two side by side?

THANK YOU! =)

 

[John Vinson]

Okay, I got the second query to run great. I'm almost jumping for joy!

One problem: The query is adding sales for June 2006 to the total, and
not adding sales from June 2005 to the total. From my understanding,
since we are calculating a statistic (we don't want the number to
change every day), since this is June 2006, we would want to add totals
from June 2005 through May 2006. Is there a way to accomplish that? I
don't quite grasp the whole Date function yet.

Date() returns today's date, as of the moment the query is run. THat's
pretty simple...

The DateSerial() function takes three arguments: year, month, day. The
UNION query returns the first day of the month specified in the
fieldname - e.g. the field JUN in a record with YEAR = 2005 will be a
date/time value, #6/1/2005#.

And then, how would I calculate those totals for, say, June 2004
through May 2005 so that I can compare the the two side by side?

By using criteria selecting the dates that you want to see.

John W. Vinson[MVP]

 

[KyleAK]

Great, that makes sense. So I get how it is making the dates in the
first query, but I'm not quite understanding the DateAdd function in
the second query:

WHERE SaleDate BETWEEN DateAdd("yyyy", -1, Date()) AND Date()

In the DateAdd function, what does the yyyy mean? The -1 means one
month before the current, correct?

Thats where I'm seeing that it is summing July 2005 through June 2006,
but need to bump it back a month so that it sums June 2005 through May
2006. Do I need to turn the last Date() after the AND to a DateAdd
function as well?

Sorry if I'm so dense on this one. =(

 

[KyleAK]

Okay, think I got it if I use the expression:

Between DateAdd("m",-13,Date()) And DateAdd("m",-1,Date())

That way it won't matter what day of the month it is, it will always
take the previous month's totals all the way back. I did some tests
and it looks good so far. =)

Going to make two queries, one to calculate the past 12 months, and
another to calculate the previous past 12 months (2004 through 2005)
and then join them so that the figures can be seen side by side and to
make calculations on each one.

THIS IS TOO COOL. Thank you so much for all your help! I owe you a
coffee or beverage of your choice for this lesson in SQL "Dating" so to
speak. =)

 

[KyleAK]

My bad, should have been:
Between DateAdd("m",-12,Date()) And DateAdd("m",-1,Date())

And for the previous period:
Between DateAdd("m",-24,Date()) And DateAdd("m",-13,Date())

But this will only work correctly on the first day of the month...
since all of the dates it is looking at are on the 1st of each month.

DATE MONTHLYSALESTOTAL
6/1/2005 45
7/1/2005 8
8/1/2005 35

How would I get around that so that no matter what day of the month it
is, the figures will be correct?

 

[KyleAK]

How about this:

Between
DateSerial(Year(DateAdd("m",-12,Date())),Month(DateAdd("m",-12,Date())),1)
And
DateSerial(Year(DateAdd("m",-1,Date())),Month(DateAdd("m",-1,Date())),1)

Between
DateSerial(Year(DateAdd("m",-24,Date())),Month(DateAdd("m",-24,Date())),1)
And
DateSerial(Year(DateAdd("m",-13,Date())),Month(DateAdd("m",-13,Date())),1)

Seems a bit long winded, but I guess it works?