Calculating YTD

[victor1981]

Hi everyone ,

I have an urgent report to make in Ms Access and consider me a beginner.

The problem is that I have data in an excel sheet which i have linked in the Access.It is a Pharma data and I have to calculate YTD sales for some Companies.

Its a 37months data and the months are categorized as M1,M2....M37 where M37 is the latest month. so the calulation for ytd would look like as

Ytd for m1, Ytd for m2, Ytd for m3 .... till M12 ,Ytd for m13
m1 m1+m2 m1+m2+m3 m13..


and as the new year starts with M13 it will again resume from the first point when it reaches M25 and would again start from M37 if you consider M1 to be January

I could have hard coded this formulae in MsAccess using normal sum but as it is always a 37 months data and the months move ahead and M1 changes from January to feburary and so the whole coding fails.

Can anyone help me to do this Its really important
Please revert if if you need any more explanation on my part

[Delerna]

If I understand you correctly I must say I don't like it. However I fully understand having to create a solution using the currently used data stores that users insist on keeping.

The way I see it you have a spread sheet where your 37 months are in columns.
This is not ideal for the query you want so you first need to create a query that will convert them to rows. Also since the 37 months are rolling you also need to tag the rows with the correct year and month. Once you have that query the YTD query becomes a simple aggregate query.

[code=sql]
SELECT Now()-day(now())+1 AS Dte, YourLinkedTable .M37 as Qty FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-1,Now()-day(now())+1) AS Dte, YourLinkedTable .M36 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-2,Now()-day(now())+1) AS Dte, YourLinkedTable .M35 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-3,Now()-day(now())+1) AS Dte, YourLinkedTable .M34 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-4,Now()-day(now())+1) AS Dte, YourLinkedTable .M33 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-5,Now()-day(now())+1) AS Dte, YourLinkedTable .M32 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-6,Now()-day(now())+1) AS Dte, YourLinkedTable .M31 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-7,Now()-day(now())+1) AS Dte, YourLinkedTable .M30 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-8,Now()-day(now())+1) AS Dte, YourLinkedTable .M29 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-9,Now()-day(now())+1) AS Dte, YourLinkedTable .M28 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-10,Now()-day(now())+1) AS Dte, YourLinkedTable .M27 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-11,Now()-day(now())+1) AS Dte, YourLinkedTable .M26 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-12,Now()-day(now())+1) AS Dte, YourLinkedTable .M25 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-13,Now()-day(now())+1) AS Dte, YourLinkedTable .M24 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-14,Now()-day(now())+1) AS Dte, YourLinkedTable .M23 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-15,Now()-day(now())+1) AS Dte, YourLinkedTable .M22 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-16,Now()-day(now())+1) AS Dte, YourLinkedTable .M21 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-17,Now()-day(now())+1) AS Dte, YourLinkedTable .M20 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-18,Now()-day(now())+1) AS Dte, YourLinkedTable .M19 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-19,Now()-day(now())+1) AS Dte, YourLinkedTable .M18 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-20,Now()-day(now())+1) AS Dte, YourLinkedTable .M17 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-21,Now()-day(now())+1) AS Dte, YourLinkedTable .M16 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-22,Now()-day(now())+1) AS Dte, YourLinkedTable .M15 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-23,Now()-day(now())+1) AS Dte, YourLinkedTable .M14 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-24,Now()-day(now())+1) AS Dte, YourLinkedTable .M13 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-25,Now()-day(now())+1) AS Dte, YourLinkedTable .M12 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-26,Now()-day(now())+1) AS Dte, YourLinkedTable .M11 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-27,Now()-day(now())+1) AS Dte, YourLinkedTable .M10 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-28,Now()-day(now())+1) AS Dte, YourLinkedTable .M9 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",29, Now()-day(now())+1) AS Dte, YourLinkedTable .M8 FROM YourLinkedTable where company="The Company"
UNION ALL
SELECT DateAdd("m",-30,Now()-day(now())+1) AS Dte, YourLinkedTable .M7 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-31,Now()-day(now())+1) AS Dte, YourLinkedTable .M6 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-32,Now()-day(now())+1) AS Dte, YourLinkedTable .M5 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-33,Now()-day(now())+1) AS Dte, YourLinkedTable .M4 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-34,Now()-day(now())+1) AS Dte, YourLinkedTable .M3 FROM YourLinkedTable where company="The Company"
union all
SELECT DateAdd("m",-35,Now()-day(now())+1) AS Dte, YourLinkedTable .M2 FROM YourLinkedTable where company="The Company"
UNION ALL SELECT DateAdd("m",-36,Now()-day(now())+1) AS Dte, YourLinkedTable .M1 FROM YourLinkedTable where company="The Company";
[/code]

Now you see why I dont like it, but not using properly designed tables leads to queries like this. Also you will need one of these for each company. If I sat down and thought about it for a bit there is probably a better way but since this is urgent it should work.

Anyway, now you just need an agregate query on that query
I'll assume you call the above query qryDatedDataFor TheCompany

[Code=sql]
SELECT year(Dte),sum(Q ty) as YTD
FROM qryDatedDataFor TheCompany
GROUP BY year(Dte)
[/code]

You can create a union query of the above from each of the company queries in order to get the YTD results for each company.
Well thats one solution, not very pretty but it should work for you, that is if I understood your post correctly.
Hope it works for you

[victor1981]

Sir, first of all Thanks for your reply and I am sorry I got you in this mess.

But there are few questions I need to ask:

Q1 Do I have to replace "YourLinkedTabl e" in your query by the name of the Query from which i am gettting the total months data.(I am actually retrieving this data from another query in which I sumup the monthly sales of each company as the names are repeating)

Q2 Can we use Running sum expression in it?

Also can I send you a mock data?So that it gets easier for us.

I am sorry if I am acting like an idiot.I have been trying to find out a soln to this problem but I havnt been able too.

[Delerna]

No problems, glad to be of assistance. Thats why I do this.

Q1) I wrote the big query with the assumption that it would be using the linked table that is actually a spreadsheet. And I also had the assumption that the months were horizontal (columns) and the companies where vertical(rows)
like this
......Company.. .M1...M2...M3.. .M4...M5....... .etc
The Company...5.... ...6 ...86....44.... 31
Company Two..12.....34. ..24....37....7 8

If that is so, or as you say there is another query that sums up the data but still looks like that
then yes YourLinkedTable needs to be changed to the name of the linked spreadsheet or the query, whichever applies

Q2) Yes you can get a running sum with modification to the second query
And If the solution I have here isn't quite right, then by all means post mock up data and table structures and we will find a solution

[victor1981]

This is absolutely how my Data looks like, but there are certain issues,There are around 173 companies in my database and in your above query I will have to replace "The Company" by the name of a respective company from my database.

Also the next step in this report is to get the YTD (on the similar lines) for the products which are around 1700 in number.

I just had one idea and that can we somehow categorize M1 to M12 as num1 and M13 to M24 as num2 and M25 to M36 as num3 and M37 as num4 (supposedly if M1=Jan and so M37 becomes Jan too) in an excel sheet and linkit in the access and then we can add on the base of these categorizations and everytime a new month arrives we can just change these categorizations ie. M1 to M11 as num1 as now M1 will be Feb and so M36 to M37 as 4.

I hope I am not sounding Crazy :).Its just that I have these ideas but I dont have the programming knowledge to convert them into soutions.

"Thank you so much Delerna for the time you are investing in this."

[Delerna]

OK so the spreadsheet now looks like this

......Company.. ..Product...M1. ..M2...M3...M4. ..M5........etc
The Company....PROD 1...5.......6 ...86....44.... 31
The Company....PROD 2..12......4 ...43....22.... 20
Company Two...PROD4..12 .....34...24... .37....78

and your summing query looks like this

......Company.. ....M1...M2.... M3....M4...M5.. ......etc
The Company.....17. ....10 ..129....66.... 51
Company Two....12.....3 4....24.....37. ...78

Is that correct?

and we wany to generate the YTD from the summing Query?
We should be able to remove the where clause from the union query.

[victor1981]

Exactly thats how I am doing this first I sumup and eliminate repetition and then we calculate the YTD from the monthly sales for both Products and Companies.


Meanwhile I did what you said and I changed the "YourLinkedTabl e" to my query name which is "0_Company_Mont hly_Total_Sum"

Also as you said I removed the where clause so it looks like this

SELECT DateAdd("m",-33,Now()-day(now())+1) AS Dte, 0_Company_Month ly_Total_Sum.M4 FROM 0_Company_Month ly_Total_Sum
union ALL
SELECT DateAdd("m",-34,Now()-day(now())+1) AS Dte, 0_Company_Month ly_Total_Sum.M3 FROM 0_Company_Month ly_Total_Sum
union ALL
SELECT DateAdd("m",-35,Now()-day(now())+1) AS Dte, 0_Company_Month ly_Total_Sum.M2 FROM 0_Company_Month ly_Total_Sum
UNION ALL SELECT DateAdd("m",-36,Now()-day(now())+1) AS Dte, 0_Company_Month ly_Total_Sum.M1 FROM 0_Company_Month ly_Total_Sum;


I copied and pasted it in the SQL view of a new query and tried running it but it is showing an error which is "Syntax error in query expression
' 0_Company_Month ly_Total_Sum.M3 7' "

[Delerna]

OK, let me go and try this in the mockup database I made last night.
I will be back in a bit

[Delerna]

This works for me. I have assumed field names Company and Product. You will need to change them if yours are different
[code=sql]
SELECT Now()-day(now())+1 AS Dte,Company,Pro duct, M37 as Qty FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-1,Now()-day(now())+1) AS Dte,Company,Pro duct, M36 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-2,Now()-day(now())+1) AS Dte,Company,Pro duct, M35 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-3,Now()-day(now())+1) AS Dte,Company,Pro duct, M34 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-4,Now()-day(now())+1) AS Dte,Company,Pro duct,M33 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-5,Now()-day(now())+1) AS Dte,Company,Pro duct, M32 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-6,Now()-day(now())+1) AS Dte,Company,Pro duct, M31 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-7,Now()-day(now())+1) AS Dte,Company,Pro duct,M30 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-8,Now()-day(now())+1) AS Dte,Company,Pro duct, M29 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-9,Now()-day(now())+1) AS Dte,Company,Pro duct, M28 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-10,Now()-day(now())+1) AS Dte,Company,Pro duct, M27 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-11,Now()-day(now())+1) AS Dte,Company,Pro duct, M26 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-12,Now()-day(now())+1) AS Dte,Company,Pro duct, M25 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-13,Now()-day(now())+1) AS Dte,Company,Pro duct, M24 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-14,Now()-day(now())+1) AS Dte,Company,Pro duct, M23 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-15,Now()-day(now())+1) AS Dte,Company,Pro duct,M22 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-16,Now()-day(now())+1) AS Dte,Company,Pro duct, M21 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-17,Now()-day(now())+1) AS Dte,Company,Pro duct,M20 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-18,Now()-day(now())+1) AS Dte,Company,Pro duct, M19 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-19,Now()-day(now())+1) AS Dte,Company,Pro duct, M18 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-20,Now()-day(now())+1) AS Dte,Company,Pro duct, M17 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-21,Now()-day(now())+1) AS Dte,Company,Pro duct,M16 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-22,Now()-day(now())+1) AS Dte,Company,Pro duct,M15 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-23,Now()-day(now())+1) AS Dte,Company,Pro duct, M14 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-24,Now()-day(now())+1) AS Dte,Company,Pro duct, M13 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-25,Now()-day(now())+1) AS Dte,Company,Pro duct, M12 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-26,Now()-day(now())+1) AS Dte,Company,Pro duct, M11 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-27,Now()-day(now())+1) AS Dte,Company,Pro duct, M10 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-28,Now()-day(now())+1) AS Dte,Company,Pro duct, M9 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",29, Now()-day(now())+1) AS Dte,Company,Pro duct,M8 FROM 0_Company_Month ly_Total_Sum
UNION ALL
SELECT DateAdd("m",-30,Now()-day(now())+1) AS Dte,Company,Pro duct, M7 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-31,Now()-day(now())+1) AS Dte,Company,Pro duct,M6 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-32,Now()-day(now())+1) AS Dte,Company,Pro duct, M5 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-33,Now()-day(now())+1) AS Dte,Company,Pro duct,M4 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-34,Now()-day(now())+1) AS Dte,Company,Pro duct,M3 FROM 0_Company_Month ly_Total_Sum
union all
SELECT DateAdd("m",-35,Now()-day(now())+1) AS Dte,Company,Pro duct, M2 FROM 0_Company_Month ly_Total_Sum
UNION ALL SELECT DateAdd("m",-36,Now()-day(now())+1) AS Dte,Company,Pro duct, M1 FROM 0_Company_Month ly_Total_Sum;
[/code]

[Delerna]

I just had one idea and that can we somehow categorize M1 to M12 as num1 and M13 to M24 as num2 and M25 to M36 as num3 and M37 as num4 (supposedly if M1=Jan and so M37 becomes Jan too) in an excel sheet and linkit in the access and then we can add on the base of these categorizations and everytime a new month arrives we can just change these categorizations ie. M1 to M11 as num1 as now M1 will be Feb and so M36 to M37 as 4.

By the way, this could be done but the if statements to handle it would be horrendous

[Delerna]

The YTD query will become
[code=sql]
SELECT year(Dte),Compa ny,Product,sum( Qty) AS YTD
FROM qryDatedDataFor TheCompany
GROUP BY year(Dte),Compa ny,Product
[/code]

[victor1981]

I checked the query,It worked but still the months are not adding up as they should.and the dates are not macthing as the years are adding upto 2010 also the months are getting started from april2005 rather then feb2005.

Thanks&Regards

[Delerna]

Hang on
Mth37 is the current month, correct?
This means that today Mth37 is April 2008.

Therefore
Mth37=Apr 2008
Mth36=Mar 2008
Mth35=Feb 2008
Mth34=Jan 2008

Mth33=Dec 2007
Mth32=Nov 2007
Mth31=Oct 2007
Mth30=Sep 2007
Mth29=Aug 2007
Mth28=Jul 2007
Mth27=Jun 2007
Mth26=May 2007
Mth25=Apr 2007
Mth24=Mar 2007
Mth23=Feb 2007
Mth22=Jan 2007

Mth21=Dec 2006
Mth20=Nov 2006
Mth19=Oct 2006
Mth18=Sep 2006
Mth17=Aug 2006
Mth16=Jul 2006
Mth15=Jun 2006
Mth14=May 2006
Mth13=Apr 2006
Mth12=Mar 2006
Mth11=Feb 2006
Mth10=Jan 2006

Mth9=Dec 2005
Mth8=Nov 2005
Mth7=Oct 2005
Mth6=Sep 2005
Mth5=Aug 2005
Mth4=Jul 2005
Mth3=Jun 2005
Mth2=May 2005
Mth1=Apr 2005
Which is what I get in my mock up.
Or am I missing something?

[victor1981]

Nope you are not missing anything its me who has missed it,I apologise for that its just that we get data with a lag of 1 to 1 and half months so in march 2008 I have to make a report based on the feb 2008 data.

Also 2010 was coming because "-" was missing in the 59th statement of the query.

But finally the months got alright and are capturing the right data now as I just manupilated the date function which you gave so that it starts from March.

The only issue left is that the final Ytd query is giving me an Yearly breakdown but I need the Monthly breakdown in YTD

Thanks&Regards

[Delerna]

Thats Great
Can you post a mock up of how you want the final YTD query results to look and I will have a go at writing a query to achieve that when I get home tonight. That is if you haven't already achieved it by that time.