# How to calculate two value between different date and different reco

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

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

### #32 rgfirefly24

• D.I.C Lover

Reputation: 371
• Posts: 1,848
• 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?

Reputation:

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

Posted 12 January 2011 - 10:57 AM

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

### #34 rgfirefly24

• D.I.C Lover

Reputation: 371
• Posts: 1,848
• 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.

### #35 mnazera

Reputation: 0
• 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

### #36 mnazera

Reputation: 0
• 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.

### #37 mnazera

Reputation: 0
• 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.

### #38 mnazera

Reputation: 0
• 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

### #39 rgfirefly24

• D.I.C Lover

Reputation: 371
• Posts: 1,848
• 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)

```

### #40 mnazera

Reputation: 0
• 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

### #41 rgfirefly24

• D.I.C Lover

Reputation: 371
• Posts: 1,848
• 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

### #42 mnazera

Reputation: 0
• 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

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

### #43 rgfirefly24

• D.I.C Lover

Reputation: 371
• Posts: 1,848
• 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.

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

### #44 mnazera

Reputation: 0
• 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

### #45 mnazera

Reputation: 0
• 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.