[rgfirefly24]

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?

[rgfirefly24]

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.

[mnazera]

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

[mnazera]

Maybe, what "rgfirefl24" or "Ionut" have other idea, to calculate TotalMTD with function DSum ? Because only TotalMTD until now can't solve it.

[mnazera]

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

[mnazera]

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

[rgfirefly24]

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) 

[mnazera]

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

[rgfirefly24]

put it in the outer select not the inner select

[mnazera]

rgfirefly24, 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

[rgfirefly24]

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.

[mnazera]

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

[mnazera]

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