Sum conditond by date

A

Andrej

Registered User.
Local time
Today, 16:57
Joined
Oct 17, 2007
Messages
40
Hello&thx to everyone, who opens this page
My problem consist 2 tables, where values from table B have to be added to table A in function of date. Table A have a one value per month and table B have daily values. Problem consist adding a column with sum values from table B to the value of table A. Sum value is sum of 15 days values from date which is same in both tables.
anyone have some idea,thx?
 
I'm a little confused. How can table B have the same date as table A if you have daily values & thus different dates in table B? Can you provide the structure of each table and more details on how the two tables are related?

I think you will need to nest a totals query within another query but the problem is how to link the two queries.
 
I'm confusing person and my threads also, thx for reading and trying

Table A have one event per month and table B has events trough whole month(31 events). During the one month they coincide in one day. I want to make query where are coinciding dates and from table A put the value of that month, and from table B sum of values of events during the last 15 days.
 
solution for my problem also can be:
just from table B, make a query that has a column with a date and column with a last 15 day events sum:

date__________value____15 day events sum
01/01/2006 _____1,2 ________18
02/01/2006 _____0.8 ________17.6
.
.
.
15/01/2006 _____1.4____(1.2+0.8....+1.4)

any idea?
 
You will need to create 2 new queries and then join those two queries in a third query. Here are the queries:

You need a query to find out the corresponding period & year for the dates in table A. You will have to substitute your actual table and field names in the following queries.

query name: qryPeriodsofTblA
SELECT tblA.myTableADate, (Month([tblA.myTableADate])-1)*2+1+(Day([tblA.myTableADate])\16)& " " & year(myTableADate) AS Period
FROM tblA;

The calculated expression "Period" determines whether the date in table A falls during the first half (1st-15th) or the second half of the month. For example, the first half of January has a period = 1, the second half of January has a period = 2, the first half of February is period 3 and so forth. The year is included so your data can be used over several years since the periods will start from 1 for each year.


Next, you need a query that returns the sum for each period in table B; this uses the same calculated expression for period except I've substituted the date from table B

query name: qryPeriodDetail
SELECT Sum(tblB.myvalue) AS SumOfmyvalue, (Month([mytableBdate])-1)*2+1+(Day([mytableBDate])\16) & " " & year([mytableBDate]) AS Period
FROM tblB
GROUP BY (Month([mytableBdate])-1)*2+1+(Day([mytableBDate])\16) & " " & year([mytableBDate]);


Now join the 2 queries above in a third query by the expression field called period

SELECT qryPeriodsofTblA.myTableADate, qryPeriodDetail.SumOfmyvalue, qryPeriodDetail.Period
FROM qryPeriodsofTblA INNER JOIN qryPeriodDetail ON qryPeriodsofTblA.Period = qryPeriodDetail.Period;

This query will return the corresponding total from table B for each period in which the date of table A occurs.
 
that's great solution but doesn't solve my problem.
I need a sum of values of 15 days for each day of table B.
my problem could be solved just working with table B
to get a table as:
date________value____15 day events sum
01/01/2006 ___1,2 ________18 (value 15 days before +value 14 days b....
02/01/2006 ___0.8 ________17.6(value 15 days before +value 14 days b....
.
.
.
15/01/2006 _____1.4____(1.2+0.8....+1.4)


I really didn't know nothing about SQL and now I'm trying to do things on my own with looking your code.
I was trying the group date (15 days together)but I don't know hoe to do it:
GROUP BY ([DATE] & [DATE]-1 & [DATE]-2...&[DATE]-15 )

and thx for helping
 
Please let me know if this does what you want:

SELECT tblB.RecID, tblB.mytableBdate, tblB.myvalue, Dsum("myvalue","tblB", "tblB.mytableBdate between #" & dateadd('d',-15,[tblB.mytableBdate]) & "# and # " & tblB.mytableBdate & "#") As RunningSum
FROM tblB
 
it looks that's it,
but values are not that should be, they are much more bigger
when I look the code that you send me it looks perfect, but values are much more bigger and I can't find out what is calculated.


FECHA value RunningSum
  • 08/07/2005 0,8 25,6
  • 09/07/2005 0,8 59
  • 10/07/2005 1 108,2
  • 11/07/2005 0 112,4
  • 12/07/2005 0 171,2
  • 13/07/2005 0 2,6
  • 14/07/2005 0 2,6
  • 15/07/2005 0 2,6
  • 16/07/2005 0 2,6
  • 17/07/2005 0 2,6
  • 18/07/2005 0 2,6
  • 19/07/2005 0 2,6
  • 20/07/2005 0 2,6
  • 21/07/2005 0 2,6
  • 22/07/2005 0 2,6
  • 23/07/2005 0 23
  • 24/07/2005 0 56,4
  • 25/07/2005 0 105,6
  • 26/07/2005 0 109,8
  • 27/07/2005 0 168,6
  • 28/07/2005 0 0
  • 29/07/2005 0 0
  • 30/07/2005 0 0
  • 31/07/2005 0 0
  • 01/08/2005 20 2,6
  • 02/08/2005 3 2,6
  • 03/08/2005 0 2,6
  • 04/08/2005 0 2,6
 
It might have something to do with the date field or its format. Is the date field defined as a date/time datatype or text? To troubleshoot this I will probably need to see your actual table. Can you zip and post a database with just this table?
 
date is defined in the right format. Really don't know what could it be.
I will attached table and query
thx for helping
 

Attachments

I modified your query slightly as shown below
SELECT [LLUVIA PRUEBA].FECHA, [LLUVIA PRUEBA].[LLUVIA ACUMULADA DIARIA], DSum("[LLUVIA ACUMULADA DIARIA]","[LLUVIA PRUEBA]","[LLUVIA PRUEBA].[FECHA] between #" & DateAdd('d',-15,[LLUVIA PRUEBA].[FECHA]) & "# and # " & [LLUVIA PRUEBA].[FECHA] & "#") AS RunningSum
FROM [LLUVIA PRUEBA]
ORDER BY [LLUVIA PRUEBA].FECHA;

Here is some of the data. I believe this is returning what you wanted.


FECHA LLUVIA ACUMULADA DIARIA RunningSum
15/01/2007 0 0
16/01/2007 0 0
17/01/2007 0 0
18/01/2007 0 0
19/01/2007 0 0
20/01/2007 0 0
21/01/2007 0 0
22/01/2007 0 0
23/01/2007 0 0
24/01/2007 0 0
25/01/2007 0.4 0.4 (0.4 + previous 15=0 some not shown)
26/01/2007 86.199 86.599 (86.199+0.4+0...)
27/01/2007 4.4 90.999 (4.4+86.199+0.4+0...)
28/01/2007 1.2 92.199 (1.2+4.4+86.199+0.4+0...)
29/01/2007 4.6 96.799
30/01/2007 1.4 98.199
31/01/2007 1 99.199
01/02/2007 0 99.199
02/02/2007 0.2 99.399
03/02/2007 7.4 106.799
04/02/2007 5.8 112.599
05/02/2007 2.8 115.399
06/02/2007 0.2 115.599
07/02/2007 0 115.599
08/02/2007 1.2 116.799
09/02/2007 0.6 117.399
10/02/2007 0 116.999
11/02/2007 0 30.8
12/02/2007 0 26.4
13/02/2007 0 25.2
14/02/2007 0 20.6
15/02/2007 0 19.2
16/02/2007 0 18.2
17/02/2007 0 18.2
18/02/2007 0.2 18.2
19/02/2007 0 10.8
20/02/2007 2.2 7.2
21/02/2007 2.4 6.8
22/02/2007 0 6.6
23/02/2007 0 6.6
24/02/2007 0 5.4
25/02/2007 0.4 5.2
26/02/2007 0 5.2
27/02/2007 0 5.2
28/02/2007 0 5.2
01/03/2007 0 5.2
02/03/2007 0 5.2
03/03/2007 0 5.2
04/03/2007 0 5.2
05/03/2007 0 5.2
06/03/2007 0 5
07/03/2007 0 5
 
I really don't know, but I get the exactly same results like it was before.
I copied your code and put it in new query and I have the same results...
don't know why.
 
Last edited:
Aren't the results what you wanted? Each value in the running sum field is the current dates value + the sum of the 15 previous days' values.
 
when I was read your post I was shure that query will function perfectly, but it has exactly the same result as query before.
I will attach you the same file with two query to see that results are the same.
 

Attachments

There was no fundamental change to the query so I expected the same results. What is the problem with the data that is returned by the query? It meets what you had in your earlier post:

that's great solution but doesn't solve my problem.
I need a sum of values of 15 days for each day of table B.
my problem could be solved just working with table B
to get a table as:
date________value____15 day events sum
01/01/2006 ___1,2 ________18 (value 15 days before +value 14 days b....
02/01/2006 ___0.8 ________17.6(value 15 days before +value 14 days b....
.
.
.
15/01/2006 _____1.4____(1.2+0.8....+1.4)
Click to expand...
 
FECHA LLUVIA ACUMULADA DIARIA RunningSum
15/01/2007 0 0
16/01/2007 0 0
17/01/2007 0 99,199
18/01/2007 0 122,599
19/01/2007 0 161,599
20/01/2007 0 279,799
21/01/2007 0 296,399
22/01/2007 0 303,199
23/01/2007 0 303,199
24/01/2007 0 330,399
25/01/2007 0,4 410,599
26/01/2007 86,199 722,197
27/01/2007 4,4 637,398
28/01/2007 1,2 92,199
29/01/2007 4,6 96,799
30/01/2007 1,4 98,199
31/01/2007 1 99,199
01/02/2007 0 0
02/02/2007 0,2 99,399
03/02/2007 7,4 122,599
04/02/2007 5,8 181,999
05/02/2007 2,8 280,999
06/02/2007 0,2 296,399
07/02/2007 0 303,199
08/02/2007 1,2 303,199
09/02/2007 0,6 330,399
10/02/2007 0 410,399
11/02/2007 0 635,998
12/02/2007 0 632,998
13/02/2007 0 25,2
14/02/2007 0 20,6
15/02/2007 0 19,2
16/02/2007 0 117,399
17/02/2007 0 18,2
18/02/2007 0,2 5,2
19/02/2007 0 64,4
20/02/2007 2,2 163,4
21/02/2007 2,4 176,6
22/02/2007 0 181
23/02/2007 0 181
24/02/2007 0 208,2
25/02/2007 0,4 288,6
26/02/2007 0 599,998
27/02/2007 0 601,398
28/02/2007 0 5,2
01/03/2007 0 117,399
02/03/2007 0 18
03/03/2007 0 5,2
04/03/2007 0 64,6
05/03/2007 0 167,4
06/03/2007 0 178,8
07/03/2007 0 185,6

I put table with a same dates as you put it for example

I really don't get it. Is it possible that results change because of version of Access?
I'm using access 2007, Spanish version...
 
Something does not make sense. The running sum field cannot go from zero to 99,199 when the second field is zero. Also, the running sum cannot go from 99,199 to 122,599 when there is a zero in field 2

FECHA LLUVIA ACUMULADA DIARIA RunningSum
15/01/2007 0 0
16/01/2007 0 0
17/01/2007 0 99,199
18/01/2007 0 122,599

If I open the database you posted yesterday & run the query, this is the data I get for the same time period. It looks like Access changes the number format to use decimal points instead of commas, but the running sum looks OK. So I am not really sure what is going on.


FECHA LLUVIA ACUMULADA DIARIA RunningSum
15/01/2007 0 0
16/01/2007 0 0
17/01/2007 0 0
18/01/2007 0 0
19/01/2007 0 0
20/01/2007 0 0
21/01/2007 0 0
22/01/2007 0 0
23/01/2007 0 0
24/01/2007 0 0
25/01/2007 0.4 0.4
26/01/2007 86.199 86.599
27/01/2007 4.4 90.999
28/01/2007 1.2 92.199
29/01/2007 4.6 96.799
30/01/2007 1.4 98.199
31/01/2007 1 99.199
01/02/2007 0 99.199
02/02/2007 0.2 99.399
03/02/2007 7.4 106.799
04/02/2007 5.8 112.599
05/02/2007 2.8 115.399
06/02/2007 0.2 115.599
07/02/2007 0 115.599
08/02/2007 1.2 116.799
09/02/2007 0.6 117.399
10/02/2007 0 116.999
11/02/2007 0 30.8
12/02/2007 0 26.4
13/02/2007 0 25.2
14/02/2007 0 20.6
15/02/2007 0 19.2
16/02/2007 0 18.2
17/02/2007 0 18.2
18/02/2007 0.2 18.2
19/02/2007 0 10.8
20/02/2007 2.2 7.2
21/02/2007 2.4 6.8
22/02/2007 0 6.6
23/02/2007 0 6.6
24/02/2007 0 5.4
25/02/2007 0.4 5.2
26/02/2007 0 5.2
27/02/2007 0 5.2
28/02/2007 0 5.2
01/03/2007 0 5.2
02/03/2007 0 5.2
03/03/2007 0 5.2
04/03/2007 0 5.2
05/03/2007 0 5.2
06/03/2007 0 5
07/03/2007 0 5
 
It's probably point and coma. Spanish version use coma for decimal numbers and as I see English version use point. But this should not affect the code and the values that Access gives me doesn't have any sense. I really don't know what I can do to change this.
Thx for your time and help, finally you resolved the problem...to me this situation looks so absurd and no-real
 
You must log in or register to reply here.

Similar threads

mloucel
Solved Interesting QUERY problem
Replies
11
Views
739
mloucel
mloucel
C
Error 3828 when exporting queries to Excel
2
Replies
34
Views
2,041
ChrisMore
C
O
Query optimization: Why does Access use temporary index instead of index or rushmore for a JOIN query with SUM?
Replies
13
Views
1,173
Josef P.
J
S
Need to create an alias for the totals of a field in query
Replies
3
Views
887
sheckay
S
Ahmed.H.Qasim
Need for assistance using DSUM
Replies
13
Views
858
Ahmed.H.Qasim
Ahmed.H.Qasim

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom