计算年初至今 [英] Calculating YTD
问题描述
大家好，
问题在于我有一个excel表中的数据，我在Access中链接。它是一个Pharma数据，我必须计算一些公司的YTD销售额。
它是一个37个月的数据和月份分类为M1，M2 .... M37，其中M37是最近一个月。所以ytd的计算结果看起来像是m $，mtd为m1，Ytd为m2，Ytd为m3 ....直到M12，Ytd为m13
m1 m1 + m2 m1 + m2 + m3 m13 ..
新的一年从M13开始，它将从第一个点恢复到M25时再次恢复如果你认为M1是1月再次从M37开始
我可以使用正常金额在MsAccess中对这些公式进行硬编码，但因为它始终是37个月的数据而且这些月份继续前进M1从1月到2月变化，所以整个编码都失败了。
任何人都可以帮我这么做真的很重要
如果你需要，请还原关于我的更多解释
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
推荐答案
如果我理解正确，我必须说我不喜欢它。但是我完全理解必须使用用户坚持保留的当前使用的数据存储来创建解决方案。
我看到它的方式你有一个电子表格，你的37个月在列中。
这对于您想要的查询并不理想，因此您首先需要创建一个将它们转换为行的查询。此外，由于37个月正在滚动，您还需要使用正确的年份和月份标记行。一旦你有了这个查询，YTD查询就变成了一个简单的聚合查询。
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.
先生，首先，感谢您的回复，我很抱歉，我让您陷入困境。
但是我需要问几个问题：
Q1我是否必须更换YourLinkedTable？在您的查询中，我从查询的名称中获取总月数据。（我实际上从另一个查询中检索此数据，其中我总结每个公司的月销售额，因为名称正在重复）
Q2我们可以在其中使用Running sum表达式吗？
我也可以给你发送一个模拟数据吗？这对我们来说更容易。
我很抱歉，如果我表现得像个白痴。我一直试图找到解决这个问题的方法，但我也能够解决这个问题。
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.
没问题，很乐意提供帮助。这就是为什么我这样做。
Q1）我写了一个大查询，假设它将使用实际上是电子表格的链表。而且我还假设这些月份是横向的（列）和垂直（行）的公司
这样的
......公司...... M1 ...... M2 ...... M3 ...... M4 ...... M5 ........等
公司... 5 ....... 6 .. .86 .... 44 .... 31
公司二...... 12 ..... 34 ... 24 .... 37 .... 78
如果是这样，或者如你所说，还有另一个查询总结了数据，但仍然看起来像是
那么是的，你需要将YourLinkedTable更改为链接电子表格或查询，以适用者为准
Q2）是的，您可以通过修改第二个查询得到一笔运行金额
如果解决方案我在这里是不是很正确，然后通过各种方式发布模拟数据和表结构，我们将找到解决方案
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
这篇关于计算年初至今的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
