Calculate MTD and YTD in a query

[equalizer]

I need to calculate sales order MTD totals and YTD totals per month in
a query. The query will show the following fields:

Part#, Order Period (YYYYMM format), MTD Orders, YTD Orders

I am getting the information from our transaction-based information
system which stores all orders by date. I will need to group the
orders by Part# and period, then sum them by month and by year-to-
date. I need to see this information for the current month and year as
well as for all previous months and years. It's easy enough to run
totals per month, but the YTD is another story. Please help!

Thanks

 

[John Spencer]

Perhaps something like the following

SELECT [Part#]
, Format([Order Period],"yyyymm") as YM
, SUM (Amount) as Total
, (SELECT Sum(Amount)
FROM TheTable as Temp
WHERE Temp.[Part#] = TheTable.[Part#]
AND Temp.[Order Period] Between DateSerial(Year(TheTable.[Order
Period]),1,1)
and DateSerial(Year(TheTable.[Order Period]),Month(TheTable.[Order
Period])=1,0)) as YTD
FROM TheTable
GROUP BY [Part#]
, Format([Order Period],"yyyymm")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

Basically the same solution as John, but using JOIN rather than a sub-query:



SELECT [a.Part#],
Format(a.[Order Period], "yyyymm") AS YM,
SUM(a.amount) AS TotalForTheMonth,
SUM(b.amount) AS YTD

FROM theTable AS a INNER JOIN theTable AS b
ON (
a.[Part#]=b.[Part#]
AND b.[Order Period] <= a.[Order Period]
AND DateDiff("yyyy", a.[Order Period], b.[Order Period]) = 0
)

GROUP BY [Part#], Format([Order Period],"yyyymm")



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Another method is to use something like:
SELECT [Part#],
Sum(Abs(Format([OrderDate],"yyyymm") = [Order Period]) * [Amount]) as
PeriodTotal,
Sum(Abs(Format([OrderDate],"yyyymm") = Format(Date(),"yyyymm")) * [Amount])
as MTDTotal,
Sum(Abs(Year([OrderDate]) = Year(Date())) * [Amount]) as YTDTotal
FROM tblNoNameGiven
GROUP BY [Part#];

--
Duane Hookom
Microsoft Access MVP

Perhaps something like the following

SELECT [Part#]
, Format([Order Period],"yyyymm") as YM
, SUM (Amount) as Total
, (SELECT Sum(Amount)
FROM TheTable as Temp
WHERE Temp.[Part#] = TheTable.[Part#]
AND Temp.[Order Period] Between DateSerial(Year(TheTable.[Order
Period]),1,1)
and DateSerial(Year(TheTable.[Order Period]),Month(TheTable.[Order
Period])=1,0)) as YTD
FROM TheTable
GROUP BY [Part#]
, Format([Order Period],"yyyymm")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I need to calculate sales order MTD totals and YTD totals per month in
a query. The query will show the following fields:

Part#, Order Period (YYYYMM format), MTD Orders, YTD Orders

I am getting the information from our transaction-based information
system which stores all orders by date. I will need to group the
orders by Part# and period, then sum them by month and by year-to-
date. I need to see this information for the current month and year as
well as for all previous months and years. It's easy enough to run
totals per month, but the YTD is another story. Please help!

Thanks

 

[equalizer]

Perhaps something like the following

SELECT [Part#]
, Format([Order Period],"yyyymm") as YM
, SUM (Amount) as Total
, (SELECT Sum(Amount)
FROM TheTable as Temp
WHERE Temp.[Part#] = TheTable.[Part#]
AND Temp.[Order Period] Between DateSerial(Year(TheTable.[Order
Period]),1,1)
and DateSerial(Year(TheTable.[Order Period]),Month(TheTable.[Order
Period])=1,0)) as YTD
FROM TheTable
GROUP BY [Part#]
, Format([Order Period],"yyyymm")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I need to calculate sales order MTD totals and YTD totals per month in
a query. The query will show the following fields:

Part#, Order Period (YYYYMM format), MTD Orders, YTD Orders

I am getting the information from our transaction-based information
system which stores all orders by date. I will need to group the
orders by Part# and period, then sum them by month and by year-to-
date. I need to see this information for the current month and year as
well as for all previous months and years. It's easy enough to run
totals per month, but the YTD is another story. Please help!

Thanks- Hide quoted text -

- Show quoted text -

Thanks John. Unfortunately the best result I got showed the MTD totals
just fine but the YTD totals fields showed the same number for every
record. This is about as far as I've ever gotten. I need it to show
the year-to-date total for the period for that part#. So if a specific
part# had 10 orders in january of this year and 10 orders in february
then the MTD for both months would be 10 but the YTD for january would
be 10 and the YTD for february would be 20. Here is the SQL I used,
based on your recommendation:

SELECT [item], Format([invdte],"yyyymm") AS OrderPeriod, Sum([qtyord])
AS TotalDollars, (SELECT Sum([qtyord])
FROM artran_qry as Temp
WHERE Temp.[item] = artran_qry.[item]
AND Temp.[invdte] Between DateSerial(Year(artran_qry.[invdte]),1,1)
and DateSerial(Year(artran_qry.[invdte]),Month(artran_qry.[invdte])
+1,0)) AS YTD
FROM artran_qry
GROUP BY [item], Format([invdte],"yyyymm");

 

[equalizer]

Basically the same solution as John, but using JOIN rather than a sub-query:

SELECT [a.Part#],
Format(a.[Order Period], "yyyymm") AS YM,
SUM(a.amount) AS TotalForTheMonth,
SUM(b.amount) AS YTD

FROM theTable AS a INNER JOIN theTable AS b
ON (
a.[Part#]=b.[Part#]
AND b.[Order Period] <= a.[Order Period]
AND DateDiff("yyyy", a.[Order Period], b.[Order Period]) = 0
)

GROUP BY [Part#], Format([Order Period],"yyyymm")

Hoping it may help,
Vanderghast, Access MVP

I need to calculate sales order MTD totals and YTD totals per month in
a query. The query will show the following fields:

Part#, Order Period (YYYYMM format), MTD Orders, YTD Orders

I am getting the information from our transaction-based information
system which stores all orders by date. I will need to group the
orders by Part# and period, then sum them by month and by year-to-
date. I need to see this information for the current month and year as
well as for all previous months and years. It's easy enough to run
totals per month, but the YTD is another story. Please help!

Thanks- Hide quoted text -

- Show quoted text -

Thanks for the help Michel. I could actually never get this to work.
After running the query a status bar shows up at the bottom left of
the screen and never shows more than one green bar. I've waited for as
long as 5 minutes and have tried several times throughout the day.
Here is my SQL:

SELECT [a.item], Format(a.invdte,"yyyymm") AS OrderPeriod,
Sum(a.qtyord) AS TotalOrdered, Sum(b.qtyord) AS YTD
FROM artran_qry AS a INNER JOIN artran_qry AS b ON
(DateDiff("yyyy",a.invdte,b.invdte)=0) AND (b.invdte<=a.invdte) AND
(a.item=b.item)
GROUP BY [a.item], Format(a.invdte,"yyyymm");

 

[equalizer]

Another method is to use something like:
SELECT [Part#],
Sum(Abs(Format([OrderDate],"yyyymm") = [Order Period]) * [Amount]) as
PeriodTotal,
Sum(Abs(Format([OrderDate],"yyyymm") = Format(Date(),"yyyymm")) * [Amount])
as MTDTotal,
Sum(Abs(Year([OrderDate]) = Year(Date())) * [Amount]) as YTDTotal
FROM tblNoNameGiven
GROUP BY [Part#];

--
Duane Hookom
Microsoft Access MVP

Perhaps something like the following

SELECT [Part#]
, Format([Order Period],"yyyymm") as YM
, SUM (Amount) as Total
, (SELECT Sum(Amount)
FROM TheTable as Temp
WHERE Temp.[Part#] = TheTable.[Part#]
AND Temp.[Order Period] Between DateSerial(Year(TheTable.[Order
Period]),1,1)
and DateSerial(Year(TheTable.[Order Period]),Month(TheTable.[Order
Period])=1,0)) as YTD
FROM TheTable
GROUP BY [Part#]
, Format([Order Period],"yyyymm")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

- Show quoted text -

Thanks Duane. This keeps returning errors that I can't seem to
resolve. I get an error that says "You have tried to execute a query
that does not include the expression 'Format([invdte],"yyyymm")' as
part of an aggregate function. I've seen this error before but have
never known what it means and how to resolve it. Take a look at my
SQL:

SELECT [item], Format([invdte],"yyyymm") AS Period,
Sum(Abs(Format([invdte],"yyyymm")=Format(Date(),"yyyymm"))*[qtyord])
AS MTDTotal, Sum(Abs(Year([invdte])=Year(Date()))*[qtyord]) AS
YTDTotal
FROM artran_qry
GROUP BY [item];

 

[Michael Gramelspacher]

Here is a MTD and YTD example for Northwind in case it can be of any help.

PARAMETERS [Enter Date:] DateTime;
SELECT Orders.CustomerID,
SUM(IIF(DATEDIFF("m",DATEADD("m",DATEDIFF("m",0,[Enter Date:]),0),
Orders.ShippedDate) = 0,[Order
Subtotals].Subtotal,
0)) AS MTD_Sales,
SUM(IIF(DATEDIFF("yyyy",DATEADD("yyyy",DATEDIFF("yyyy",0,[Enter
Date:]),0),
Orders.ShippedDate) = 0,[Order
Subtotals].Subtotal,
0)) AS YTD_Sales
FROM Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.ShippedDate IS NOT NULL
AND Orders.ShippedDate <= [Enter Date:]
GROUP BY Orders.CustomerID;

 

[Michel Walsh]

You probably have large tables. Jet optimizer is rather lazy, and you have
to implicitly hint it by giving the fastest and most restrictive criteria
first, so a.partNo = b.partNo before the inequality and finally, the
computed expression (where no index can be of some use).

Thanks for the help Michel. I could actually never get this to work.
After running the query a status bar shows up at the bottom left of
the screen and never shows more than one green bar. I've waited for as
long as 5 minutes and have tried several times throughout the day.
Here is my SQL:

SELECT [a.item], Format(a.invdte,"yyyymm") AS OrderPeriod,
Sum(a.qtyord) AS TotalOrdered, Sum(b.qtyord) AS YTD
FROM artran_qry AS a INNER JOIN artran_qry AS b ON
(DateDiff("yyyy",a.invdte,b.invdte)=0) AND (b.invdte<=a.invdte) AND
(a.item=b.item)
GROUP BY [a.item], Format(a.invdte,"yyyymm");

I would try:


FROM artran_qry AS a INNER JOIN artran_qry AS b
ON ( (a.item=b.item)
AND (b.invdte<=a.invdte)
AND (DateDiff("yyyy",a.invdte,b.invdte)=0) )


and be sure there is an index on field item.



Hoping it may help,
Vanderghast, Access MVP