Last day

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I've go a huge table with a date and value field. I would like to create a
query which returns only the value of the last day per month.

E.G. See below the result of this query and the data:

Query result:
31.01.2005 1142.35
28.02.2005 1176.7
31.03.2005 1151.28
29.04.2005 1123.64


Table values:
Date Value
04.01.2005 1161.53
05.01.2005 1148.83
06.01.2005 1142.25
07.01.2005 1142.08
10.01.2005 1144.14
11.01.2005 1141.11
12.01.2005 1145.9
13.01.2005 1138.8
14.01.2005 1141.07
17.01.2005 1143.16
18.01.2005 1146.44
19.01.2005 1141.45
20.01.2005 1130.98
21.01.2005 1128.21
24.01.2005 1128.37
25.01.2005 1128.65
26.01.2005 1137.96
27.01.2005 1137.93
28.01.2005 1133.39
31.01.2005 1142.35
01.02.2005 1147.97
02.02.2005 1152.68
03.02.2005 1147.03
04.02.2005 1157.96
07.02.2005 1156.22
08.02.2005 1154.02
09.02.2005 1149.07
10.02.2005 1156.06
11.02.2005 1163.29
14.02.2005 1168.61
15.02.2005 1172.31
16.02.2005 1169.64
17.02.2005 1167.52
18.02.2005 1169.15
21.02.2005 1169.35
22.02.2005 1162.93
23.02.2005 1160.73
24.02.2005 1165.42
25.02.2005 1177.13
28.02.2005 1176.7
01.03.2005 1179.96
02.03.2005 1177.94
03.03.2005 1178.78
04.03.2005 1192.42
07.03.2005 1192.3
08.03.2005 1193
09.03.2005 1185.56
10.03.2005 1183.33
11.03.2005 1181.57
14.03.2005 1180.79
15.03.2005 1178.01
16.03.2005 1172.15
17.03.2005 1170.85
18.03.2005 1169.19
21.03.2005 1162.07
22.03.2005 1157.41
23.03.2005 1147.59
24.03.2005 1146.42
25.03.2005 1146.94
28.03.2005 1145.72
30.03.2005 1148.44
31.03.2005 1151.28
01.04.2005 1148.26
04.04.2005 1142.55
05.04.2005 1149.26
06.04.2005 1152.76
07.04.2005 1159.65
08.04.2005 1153.75
11.04.2005 1155.67
12.04.2005 1154.17
13.04.2005 1149.46
14.04.2005 1137.5
15.04.2005 1123.91
18.04.2005 1117.1
19.04.2005 1126.56
20.04.2005 1118.58
21.04.2005 1129.81
22.04.2005 1129.85
25.04.2005 1134.12
26.04.2005 1126.46
27.04.2005 1123.71
28.04.2005 1114.97
29.04.2005 1123.64
02.05.2005 1125.76
03.05.2005 1127.67


Thank you very much for your help.

Kind regards,

Simon
 
Hi Ofer,

Thank you very much for your comment on this issue.

I try to create a query which would return the results in my example and not
a join between table and query.

Kind regards,

Simon Minder
 
Hi Van,

Thank you very much for your comment on this issue.

I try to create a query which would return the results in my example and not
a join between table and query.

Kind regards,

Simon Minder
 
Hi Simon,

I believe the following might be close
to what you were hoping for
(I called your table "TblSimon,"
changed field names to non-reserved names,
and changed your dates to "US-dates,"
but hopefully you can follow)

SELECT
t.ADate,
t.AValue
FROM TblSimon AS t
WHERE (((t.ADate)=
(SELECT Max(t1.ADate) AS MaxOfADate
FROM TblSimon AS t1
GROUP BY Year(t1.ADate), Month(t1.ADate)
HAVING
Year(t1.ADate) = Year(t.ADate)
AND
Month(t1.ADate) = Month(t.ADate))));

would return from your given data:

ADate AValue
1/31/2005 1142.35
2/28/2005 1176.7
3/31/2005 1151.28
4/29/2005 1123.64
5/3/2005 1127.67


good luck,

gary
 
I am not sure whether you didn't understand or you didn't even read the
article whose link I posted. It got nothing to do with a "join between
table and query" (whatever this means). All 4 techniques result in queries
that you can use to get what you need.

The article shows different techniques that you can get related Field(s)
from a Group By (Total) Query which is exactly what you need.

One strategy you can try is to get the max date out of each group of Records
group by month/year (hence a Group By query/SQL). This can be use as the
sub-query / SQL for a query / SQL to extract the MaxDatePerMonthYear AND
"Value" corresponding / related to each MaxDatePerMonthYear (hence the title
of the article)

AFAIK, any of the 4 techniques can be used to achieve what you need. All
you need is to put your thinking hat on ...
 
Hi Van,

I had a look at your the document on the web, but I was not able to apply
your the techniques to my problem. I guess this is due to my expericence with
Access and not due to your document.

However, I received a solution from Gary which works fine. Thank you anyway
for your support.

Kind regards,

Simon
 
OK. If you look at Gary's inner SELECT statement, this is the same as my
MaxDatePerMonthYear and the outer select statement gives MaxDatePerMonthYear
and the related value of Field "Value" which is the second part I mentioned.

Now if you look at method 2 in the article, you will see that is exactly how
the article applies the above technique.
 
Back
Top