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.

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";
 

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 qryDatedDataForTheCompany

SELECT year(Dte),sum(Qty) as YTD
FROM qryDatedDataForTheCompany
GROUP BY year(Dte)
 

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 "YourLinkedTable" 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....78

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....PROD1...5.......6 ...86....44....31
The Company....PROD2..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.....34....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 "YourLinkedTable" to my query name which is "0_Company_Monthly_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_Monthly_Total_Sum.M4 FROM 0_Company_Monthly_Total_Sum
union ALL
SELECT DateAdd("m",-34,Now()-day(now())+1) AS Dte, 0_Company_Monthly_Total_Sum.M3 FROM 0_Company_Monthly_Total_Sum
union ALL
SELECT DateAdd("m",-35,Now()-day(now())+1) AS Dte, 0_Company_Monthly_Total_Sum.M2 FROM 0_Company_Monthly_Total_Sum
UNION ALL SELECT DateAdd("m",-36,Now()-day(now())+1) AS Dte, 0_Company_Monthly_Total_Sum.M1 FROM 0_Company_Monthly_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_Monthly_Total_Sum.M37' "

[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

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

[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

SELECT year(Dte),Company,Product,sum(Qty) AS YTD
FROM qryDatedDataForTheCompany
GROUP BY year(Dte),Company,Product
 

[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.

[victor1981]

Yup here is the structure

Monthly Figures
......Company......M1...M2....M3....M4...M5....... .etc
The Company......10....20 ....30.....40...50
Company Two.....12......24.....36....48..60

Final Ytd structure based on the above monthly data would be

......Company......ytdM1...ytdM2....ytdM3....ytdM4 ...ytdM5....... .etc
The Company........10........30 .........60.........100.......150
Company Two.......12........36...........72..........120.. ....180


I am trying Dsum on it (Method 1) but till now its somehow not worked
http://support.microsoft.com/kb/290136

This is the link in which they have given the usage but somehow when I wrote the same for the orders table it is not recognizing the field.

Also i don't want to create any alien function in VBA as I have to connect the data with excel in the end and it doesn't recognize the query which is calculated through an external function.

Thanks&Regards

[Delerna]

Didn't you want it by product also

[victor1981]

Yup but I wll have to show that in a different table separately,What we do in the report is give out top 10 companies and calculate there Monthly, Ytd and Mat sales.

Then we take out top20 Products sepeately and do the same for them Ranking is done on the basis of MAT sales.

[Delerna]

OK as a starting point I have written 3 queries 2 as initial steps and the third joins those to obtain the YTD as you require. I think.

The first query I called qryPrelim1. You can call it what you want but you will need to change the third query to match.

SELECT Year(Dte) AS Y, Month(dte) AS M, Dte, Company, Product, Qty
FROM qryDatedDataForTheCompany;
 

The second query I called qryPrelim2.

SELECT Year(Dte) AS Y, Month(dte) AS M, Dte, Company, Product, Qty
FROM qryDatedDataForTheCompany;
 

And the third uses those for the YTD

SELECT a.Company, a.Product, a.Dte, a.Qty, a.Qty+IIf(b.Qty Is Null,0,b.Qty) AS YTD
FROM qryPrelim1 AS a LEFT JOIN qryPrelim2 AS b ON a.Y = b.Y AND a.M = b.M AND a.Company = b.Company AND a.Product = b.Product
ORDER BY a.Company, a.Product, a.Dte;
 

All 3 could be written as a single query through the use of subqueries but access keeps changing the ( ) containing the subquery into [ ] everytime you edit the query so I thought this would be easier for you.


Take a look at that and modify it however you need to achieve what you want.
I will be around for a few more hours playing solitaire and answering questions here. If you need further assistance

[victor1981]

Its giving me this result
Company Dte............ ...Qty YTD In actual it should be
a 4/1/2005 3:51:02 PM ...10 20...........................10
a 5/1/2005 3:51:02 PM ...20 40...........................30
a 6/1/2005 3:51:02 PM ...30 60...........................60
a 7/1/2005 3:51:02 PM ...40 80...........................100
a 8/1/2005 3:51:02 PM ...50 100.........................200
a 9/1/2005 3:51:02 PM ...60 120.........................320
a 10/1/2005 3:51:02 PM...70 140.........................660
a 11/1/2005 3:51:02 PM...80 160.........................820
a 12/1/2005 3:51:02 PM...90 180.........................1000
a 1/1/2006 3:51:02 PM ...100 200.........................200(new YR )
a 2/1/2006 3:51:02 PM ..110 220.........................420
a 3/1/2006 3:51:02 PM ...120 240..........................660

Just a question Can we use Dsum where we can put a condition that it goes on adding month-wise till they are in the same year and then start again when a new year starts.

Thanks & Regards

[Delerna]

DSum....Thats an excel function isn't it?
I don't know, perhaps.

Here is the data from my mock up. As you can see it works.
Not sure what is going wrong with yours

Company Product Dte Qty YTD
Company2 Prod2 2/1/2005 10 10
Company2 Prod2 3/1/2005 20 30
Company2 Prod2 4/1/2005 30 50
Company2 Prod2 5/1/2005 40 70
Company2 Prod2 6/1/2005 50 90
Company2 Prod2 7/1/2005 60 110
Company2 Prod2 8/1/2005 70 130
Company2 Prod2 9/1/2005 80 150
Company2 Prod2 10/1/2005 90 170
Company2 Prod2 11/1/2005 100 190
Company2 Prod2 12/1/2005 110 210

[Delerna]

Hang on, no its not, My mistake and my appologies I must be getting tired.
I will have another quick look

[victor1981]

Oh! then you must take rest.

And Dsum is a function in MS access like Sumif in excel.But again I am very bad with the syntaxes in sql so i have yet to crack it but I believe it works well for running totals ,I just searched it on Google .Here is the link

http://support.microsoft.com/kb/290136

[Delerna]

OK, wow my mind went way out on a tangent with that one, Sorry about that.
try this. Its a single query this time

SELECT qryDatedDataForTheCompany.Dte, 
      qryDatedDataForTheCompany.Company,    
      qryDatedDataForTheCompany.Product, 
      qryDatedDataForTheCompany.Qty,
      (    select sum(Qty) as YTD 
           from qryDatedDataForTheCompany a 
           where Year(a.Dte)=year(qryDatedDataForTheCompany.Dte) 
             and qryDatedDataForTheCompany.company=a.Company 
             and a.Product=qryDatedDataForTheCompany.Product 
             and qryDatedDataForTheCompany.Dte>=a.Dte
       ) AS YTD
FROM qryDatedDataForTheCompany;
 

I hope it won't be too slow

[Delerna]

And here is a query that produces the same reult using DSum
Thanks for that, it was something I was unaware of

SELECT qryDatedDataForTheCompany.Company, qryDatedDataForTheCompany.Product, qryDatedDataForTheCompany.Dte, First(qryDatedDataForTheCompany.Qty) AS FirstOfQty, DSum("Qty","qryDatedDataForTheCompany","DatePart('m',[Dte])<=" & Month([Dte]) & " And DatePart('yyyy',[Dte])=" & Year([Dte]) & " And [company]='" & [company] & "' And [product]='" & [product] & "'") AS MTD
FROM qryDatedDataForTheCompany
GROUP BY qryDatedDataForTheCompany.Company, qryDatedDataForTheCompany.Product, qryDatedDataForTheCompany.Dte, DSum("Qty","qryDatedDataForTheCompany","DatePart('m',[Dte])<=" & Month([Dte]) & " And DatePart('yyyy',[Dte])=" & Year([Dte]) & " And [company]='" & [company] & "' And [product]='" & [product] & "'")
ORDER BY qryDatedDataForTheCompany.Company, qryDatedDataForTheCompany.Product, qryDatedDataForTheCompany.Dte;
 

I must say though that, at least on my computer, DSum is much slower than the previous query.

[Delerna]

This version of DSum is much faster but still not quite as fast
I had DSum as 'group by' instead of 'expression'.

SELECT qryDatedDataForTheCompany.Company, qryDatedDataForTheCompany.Product, qryDatedDataForTheCompany.Dte, First(qryDatedDataForTheCompany.Qty) AS FirstOfQty, DSum("Qty","qryDatedDataForTheCompany","DatePart('m',[Dte])<=" & Month([Dte]) & " And DatePart('yyyy',[Dte])=" & Year([Dte]) & " And [company]='" & [company] & "' And [product]='" & [product] & "'") AS MTD
FROM qryDatedDataForTheCompany
GROUP BY qryDatedDataForTheCompany.Company, qryDatedDataForTheCompany.Product, qryDatedDataForTheCompany.Dte
ORDER BY qryDatedDataForTheCompany.Company, qryDatedDataForTheCompany.Product, qryDatedDataForTheCompany.Dte;
 
 

[victor1981]

I don't know wats happening but when I run the final query,Its showing me this message "system resource exceeded".My system's RAM is already 1GB .Can u suggest what should I do.

Thanks&Regards

[Delerna]

A couple of things
1) Take a copy of the database and "compact and repair" it.
2) If that fails, try rebooting your computer.
3) If that fails, try creating a new fresh database and import the tables,queries etc from the database you were working with

good luck

by the way, did any of the queries work for you?

[victor1981]

Thank you so much the above queries are working fine but not the last one.I have already Run the compact and repair command and also rebooted my system but its not working.

Should I export all the tables and queries to a new Database?

Thanks&Regards

[Delerna]

It couldn't hurt and might even help

[victor1981]

What I did is ,I imported only few queries and formed a new database but sadly its still not workin.

[Delerna]

On the second query. The one with the DSum in group by. What happens if you design it and change it from 'Group By' to Expression'.

Thats what I did to get the third query that I pasted here. Maybe something is going wrong with all the copying and pasting from my machine through this site and on to your machine. I am saying that with a puzzled expression on my face.

[victor1981]

On the second query. The one with the DSum in group by. What happens if you design it and change it from 'Group By' to Expression'.

Thats what I did to get the third query that I pasted here. Maybe something is going wrong with all the copying and pasting from my machine through this site and on to your machine. I am saying that with a puzzled expression on my face.

I will check it again

Thanks&Regards