How to calculate two value between different date and different reco

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • 4

49 Replies - 3745 Views - Last Post: 14 January 2011 - 05:28 PM Rate Topic: -----

#31 Guest_mnazera*


Reputation:

Re: How to calculate two value between different date and different reco

Posted 12 January 2011 - 07:36 AM

I am sorry, my problem maybe not clear and also become you to confuse.
Actually I can't solve by myself, about DSum function as like codes

DSum(" [H2ExPSATotal] ","SummaryH2ExPSA") AS TotalMTD,

So for clear my problem I post complete code, while I hope anyone maybe can help me, why my codes

DSum(" [H2ExPSATotal] ","SummaryH2ExPSA") AS TotalMTD,

not work as my expectation, I would like result the TotalMTD, add day per day, as per previus example posted, I read at other microsoft article, this problem should be put on Total : Expression, now actually I put to Total : Group By., because while I put Total : Expression , come error or conflict with codes :

[b]WHERE ((([Calc Shift 1-109].DateShift1)=[Calc Shift 2-109]![DateShift2] And ([Calc Shift 1-109].DateShift1)=[Calc Shift 3-109]![DateShift3]));

Last time I hope while I posted the complete SQL code, the reader can get complete information for my problem

But I thank very much for your response, because I can't find solver at any textbook as your give solution and tutorial, again, thank you very much

Thank
Was This Post Helpful? 0

#32 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 292
  • View blog
  • Posts: 1,531
  • Joined: 07-April 08

Re: How to calculate two value between different date and different reco

Posted 12 January 2011 - 10:32 AM

So let me see if i understand you correctly.

You have 3 tables: [Calc Shift 1 - 109],[Calc Shift 2 - 109],[Calc Shift 3 - 109].
Each table has the following columns: Date, Value
You then have a summary table( or is it a view) which you want to populate with Date, Value from table 1, Value from table 2, Value from table 3, Total for the Day, and Month to Date Total.

Am i correct in what I am assuming? Also is SummaryH2ExPSA a table or a view?
Was This Post Helpful? 0
  • +
  • -

#33 Guest_mnazera*


Reputation:

Re: How to calculate two value between different date and different reco

Posted 12 January 2011 - 10:57 AM

View Postrgfirefly24, on 12 January 2011 - 09:32 AM, said:

So let me see if i understand you correctly.

You have 3 tables: [Calc Shift 1 - 109],[Calc Shift 2 - 109],[Calc Shift 3 - 109].
Each table has the following columns: Date, Value
You then have a summary table( or is it a view) which you want to populate with Date, Value from table 1, Value from table 2, Value from table 3, Total for the Day, and Month to Date Total.

Am i correct in what I am assuming? Also is SummaryH2ExPSA a table or a view?



Yes, Your assuming is exactly correct, and SummaryH2ExPSA is a query

thank
Was This Post Helpful? 0

#34 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 292
  • View blog
  • Posts: 1,531
  • Joined: 07-April 08

Re: How to calculate two value between different date and different reco

Posted 12 January 2011 - 11:44 AM

I do not have an access database, so i can not test this against it. I do however know that this works against a Sql Server database.

CREATE TABLE #Summary
(
   Date DATETIME
   ,H2ExPSA1 INT
   ,H2ExPSA2 INT
   ,H2EXPSA3 INT
   ,DailyTotal INT
   ,TotalMTD INT
)

INSERT INTO #Summary
(Date,H2ExPSA1,H2ExPSA2,H2EXPSA3,DailyTotal)
SELECT	[Calc Shift 1-109].DateShift1
        ,[Calc Shift 1-109].H2ExPSA1
        ,[Calc Shift 2-109].H2ExPSA2
		,[Calc Shift 3-109].H2ExPSA3
		,([H2ExPSA1]+[H2ExPSA2]+[H2ExPSA3])
FROM 
		[Calc Shift 1-109],[Calc Shift 2-109],[Calc Shift 3-109]
WHERE 
		(
			(
				(
					[Calc Shift 1-109].DateShift1
				)=[Calc Shift 2-109]![DateShift2] 
				And 
				(
					[Calc Shift 1-109].DateShift1
				)=[Calc Shift 3-109]![DateShift3]
			)
		)
		

UPDATE #Summary
SET
	TotalMTD = d1.total
FROM 
	#Summary
INNER JOIN
(
	SELECT	T1.[Date]
			,SUM(T2.DailyTotal) as Total
FROM 
		#Summary T1
	CROSS JOIN
		#Summary T2
WHERE
	T2.Date <= T1.Date
GROUP BY 
	T1.Date
)d1 on #Summary.Date = d1.Date


SELECT * FROM #Summary




Run that and see if it gives you what your wanting.
Was This Post Helpful? 0
  • +
  • -

#35 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 03-January 11

Re: How to calculate two value between different date and different reco

Posted 12 January 2011 - 12:47 PM

Thank you very much rgfirefly24 for your best attention & solution.
Because I am never use SQL Server, I need to learn more how to convert your SQL server codes to Access.
Again, thank
Was This Post Helpful? 0
  • +
  • -

#36 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 03-January 11

Re: How to calculate two value between different date and different reco

Posted 12 January 2011 - 01:54 PM

Maybe, what "rgfirefl24" or "Ionut" have other idea, to calculate TotalMTD with function DSum ? Because only TotalMTD until now can't solve it.
Was This Post Helpful? 0
  • +
  • -

#37 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 03-January 11

Re: How to calculate two value between different date and different reco

Posted 12 January 2011 - 05:02 PM

just now I find command for RUNNING TOTAL like this :

SELECT fieldlist,

(SELECT Sum(valuefield) AS Total

FROM datasource

WHERE datasource.sortfield <= T1.sortfield) AS Total

FROM datasource AS T1

And I tried to my query :

SELECT ID, DateShift1 AS Expr2, T1.H2ExPSATotal, (SELECT Sum(H2ExPSATotal) As Total FROM SummaryH2ExPSA WHERE SummaryH2ExPSA.ID <=T1.ID) AS Total
FROM SummaryH2ExPSA AS T1;

Running Total was working properly, but I can't make Date as basis, just ID as basis.
I seem this code almost same with "rgfirefly24" posted. Again thank for you, base your codes now I have inspiration to learn more about this function
Was This Post Helpful? 0
  • +
  • -

#38 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 03-January 11

Re: How to calculate two value between different date and different reco

Posted 13 January 2011 - 03:43 AM

ok now I was success Running Total base Date by codes :

SELECT ID, DateShift1, H2ExPSATotal, (SELECT Sum(H2ExPSATotal) As Total FROM H2ExPSASummary WHERE H2ExPSASummary.DateShift1 <=T1.DateShift1) AS Total
FROM H2ExPSASummary AS T1;

Now I would like Total, reset to zero every Date 1 every month, can you help me again ?
Thank
Was This Post Helpful? 0
  • +
  • -

#39 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 292
  • View blog
  • Posts: 1,531
  • Joined: 07-April 08

Re: How to calculate two value between different date and different reco

Posted 13 January 2011 - 07:30 AM

Based on your query you will need to put a piece in your where clause that only grabs dates within a specific month. I'm assuming you run this query only for the month you are currently in, so try adding this to your where clause:

AND DateShift1 BETWEEN DateSerial(Year(Now), Month(Now), 1) AND DateSerial(Year(Now), Month(Now) + 1, 0) 


Was This Post Helpful? 0
  • +
  • -

#40 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 03-January 11

Re: How to calculate two value between different date and different reco

Posted 13 January 2011 - 08:07 AM

Thank, you mean like this :

SELECT T1.ID, T1.DateShift1, T1.H2ExPSATotal, (SELECT Sum(H2ExPSATotal) As Total FROM H2ExPSASummary WHERE H2ExPSASummary.DateShift1 <=T1.DateShift1) And [DateShift1] Between DateSerial(Year(Now()),Month(Now()),1) And DateSerial(Year(Now()),Month(Now())+1,0) AS Total
FROM H2ExPSASummary AS T1;

But the result become like this

ID Date H2ExPSATotal Total
2 1/1/2011 58584 -1
3 1/2/2011 49116 -1
5 1/3/2011 49184 -1
6 1/4/2011 48828 -1
7 1/5/2011 48796 -1
8 1/6/2011 48748 -1
9 1/7/2011 48996 -1
10 1/8/2011 44900 -1
11 1/9/2011 32764 -1
12 1/10/2011 55644 -1
13 1/11/2011 18360 -1
14 1/12/2011 30000 -1
15 1/13/2011 0 -1
16 1/14/2011 0 -1
17 1/15/2011 0 -1
18 1/16/2011 0 -1
19 1/17/2011 0 -1
20 1/18/2011 0 -1
21 1/19/2011 0 -1
22 1/20/2011 0 -1
23 1/21/2011 0 -1
24 1/22/2011 0 -1
25 1/23/2011 0 -1
26 1/24/2011 0 -1
27 1/25/2011 0 -1
28 1/26/2011 0 -1
29 1/27/2011 0 -1
30 1/28/2011 0 -1
31 1/29/2011 0 -1
32 1/30/2011 0 -1
33 1/31/2011 0 -1
34 2/1/2011 0 0
35 2/2/2011 0 0
Was This Post Helpful? 0
  • +
  • -

#41 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 292
  • View blog
  • Posts: 1,531
  • Joined: 07-April 08

Re: How to calculate two value between different date and different reco

Posted 13 January 2011 - 08:48 AM

put it in the outer select not the inner select
Was This Post Helpful? 0
  • +
  • -

#42 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 03-January 11

Re: How to calculate two value between different date and different reco

Posted 13 January 2011 - 10:50 AM

View Postrgfirefly24, on 13 January 2011 - 02:48 PM, said:

put it in the outer select not the inner select



Sorry, I am not too experience, where position OUTER or INNER positon of SELECT command ?
Again, I am sorry

Syntax

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, …]]} FROM tableexpression [, …] [IN externaldatabase] [WHERE… ] [GROUP BY… ] [HAVING… ] [ORDER BY… ] [WITH OWNERACCESS OPTION]


Thank
Was This Post Helpful? 0
  • +
  • -

#43 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 292
  • View blog
  • Posts: 1,531
  • Joined: 07-April 08

Re: How to calculate two value between different date and different reco

Posted 14 January 2011 - 07:52 AM

When you Do a Select statement inside of another select statement it is called an Inner select.

So in your code:

SELECT    T1.ID
          ,T1.DateShift1
          ,T1.H2ExPSATotal
          ,(--This part is the inner select
             SELECT    Sum(H2ExPSATotal) As Total 
             FROM H2ExPSASummary 
             WHERE H2ExPSASummary.DateShift1 <=T1.DateShift1
            ) AS Total
FROM H2ExPSASummary AS T1
WHERE [DateShift1] Between DateSerial(Year(Now()),Month(Now()),1) And DateSerial(Year(Now()),Month(Now())+1,0);



Copy the code above and try it out. You might have to remove the comment.
Was This Post Helpful? 0
  • +
  • -

#44 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 03-January 11

Re: How to calculate two value between different date and different reco

Posted 14 January 2011 - 08:45 AM

thak rgfirefly24,

I was tried like this :

SELECT ID, DateShift1, H2ExPSATotal, (SELECT Sum(H2ExPSATotal) As Total FROM H2ExPSASummary WHERE H2ExPSASummary.DateShift1 <=T1.DateShift1) AS Total
FROM H2ExPSASummary AS T1
WHERE [DateShift1] Between DateSerial(Year(Now()),Month(Now()),1) And DateSerial(Year(Now()),Month(Now())+1,0);

but the result only 1 month, and the next month no more view while i would like all record view, only at date 1 every month the MTD same with value in column H2ExPSATotal.

You have other idea to modity this OUTER SELECT Criteria

ID Date H2ExPSATotal Total
2 1/1/2011 58584 58584
3 1/2/2011 49116 107700
5 1/3/2011 49184 156884
6 1/4/2011 48828 205712
7 1/5/2011 48796 254508
8 1/6/2011 48748 303256
9 1/7/2011 48996 352252
10 1/8/2011 44900 397152
11 1/9/2011 32764 429916
12 1/10/2011 55644 485560
13 1/11/2011 18360 503920
14 1/12/2011 30000 533920
15 1/13/2011 0 533920
16 1/14/2011 0 533920
17 1/15/2011 0 533920
18 1/16/2011 0 533920
19 1/17/2011 0 533920
20 1/18/2011 0 533920
21 1/19/2011 0 533920
22 1/20/2011 0 533920
23 1/21/2011 0 533920
24 1/22/2011 0 533920
25 1/23/2011 0 533920
26 1/24/2011 0 533920
27 1/25/2011 0 533920
28 1/26/2011 0 533920
29 1/27/2011 0 533920
30 1/28/2011 0 533920
31 1/29/2011 0 533920
32 1/30/2011 0 533920
33 1/31/2011 0 533920
Was This Post Helpful? 0
  • +
  • -

#45 mnazera  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 03-January 11

Re: How to calculate two value between different date and different reco

Posted 14 January 2011 - 10:56 AM

First I am sorry for rgfirefly24, because previously information about my problem not complete,
so you only post me codes MTD month per month only, I understand and thanks for you, and hope you still help me to finalized this problem.
I hope you can help me, to give idea, how to make MTD continue every month, only value MTD at Date 1 return to value in column H2ExPSATotal.
Base your previous information, Now I more understand about Ms Access, like INNER OUTER SELECT or Subquery, for this, thank for you.

I still hope your help about this

Thank
Was This Post Helpful? 0
  • +
  • -

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • 4