Join table to itself for aggregate function

[Robert]

I need help with the SQL syntax of a query. All I want to do is retrieve
the corresponding value of Val for the Last DateAndTime grouped by TabIndex.
Of course, you cannot do this in a simple aggregate query because it wont
let me have Val unless I group on it, but I cannot group on Val. So I have
to join the table to itself but I cannot get it to work.

FloatTable

Floatid
TabIndex
DateAndTime
Val

Can anyone provide the correct syntax to join FloatTable to itself and
retrieve Val for the last (most recent) DateAndTime grouped by TabIndex?


This does not work:

SELECT Val
FROM FloatTable AS A1 INNER JOIN [SELECT max(DateAndTime) AS
MaxOfDateAndTime, TagIndex FROM FloatTable WHERE (DateAndTime Like "*" &
"12/24/2008" & "*" AND Status<>"U") GROUP BY TagIndex ] AS B1 ON
a1.dateandtime=b1.MaxOfDateAndTime AND a1.tagindex=b1.tagindex;

Not sure if my constants have anything to do with the problem.

Rob

 

[Bob Barrows]

I need help with the SQL syntax of a query. All I want to do is
retrieve the corresponding value of Val

Is that really the name of your field? If so, change it now. "Val" is a
reserved keyword, being the name of a VBA function. If you won't/can't
change it, you are going to need to remember to surround it with brackets []
whenever you use it in sql statements.

for the Last DateAndTime
grouped by TabIndex. Of course, you cannot do this in a simple
aggregate query because it wont let me have Val unless I group on it,
but I cannot group on Val. So I have to join the table to itself but
I cannot get it to work.
FloatTable

Floatid
TabIndex
DateAndTime
Val

Can anyone provide the correct syntax to join FloatTable to itself and
retrieve Val for the last (most recent) DateAndTime grouped by
TabIndex?

This does not work:

What happens? Does your computer crash? Do you get the wrong results? Do you
get an error message? What is the error message?

SELECT Val
FROM FloatTable AS A1 INNER JOIN [SELECT max(DateAndTime) AS
MaxOfDateAndTime, TagIndex FROM FloatTable WHERE (DateAndTime Like
"*" & "12/24/2008" & "*" AND Status<>"U") GROUP BY TagIndex ] AS B1
ON a1.dateandtime=b1.MaxOfDateAndTime AND a1.tagindex=b1.tagindex;

Not sure if my constants have anything to do with the problem.

Well, if that DateAndTime field is a Date/Time field, then I can tell you
that of course your constants are part of your problem. Date/Times are not
strings and cannot be used in a Like comparison. If DateAndTime is a Text
field, then disregard that. It really helps to let us know the datatypes of
the fields you are trying to filter.

I'm not sure what you are trying to get. If there are multiple tabindex
values, the query as you have built it will return a list of "Val" values
with no context. It would help showing us a few rows of sample data in
tabular format, followed by the result you wish to retrieve, also in tabular
format if you wish to retrieve multiple rows from that set of sample data.

 

[John Spencer]

It is unclear from your message where you want to apply the criteria.
Are you trying to get the max dateandtime where the status is "U" or do
you want only records returned where the status is "u".

PERHAPS what you want is
SELECT F.TabIndex, F.DateandTime, F.Val
FROM FloatTable as F INNER JOIN
(SELECT FA.TabIndex, Max(Fa.FloatDateAndTime) LastTime
FROM FloatTable as Fa
GROUP BY Fa.TabIndex) as FB
ON F.TabIndex = FB.TabIndex
and F.DateAndTime = FB.LastTime
WHERE F.DateAndTime >=#12/24/2008# and <#12/25/2008#
AND F.Status = "U"

OR perhaps
SELECT F.TabIndex, F.DateandTime, F.Val
FROM FloatTable as F INNER JOIN
(SELECT FA.TabIndex, Max(Fa.FloatDateAndTime) LastTime
FROM FloatTable as Fa
WHERE Fa.Status = "U"
AND F.DateAndTime >=#12/24/2008# and <#12/25/2008#
GROUP BY Fa.TabIndex) as FB
ON F.TabIndex = FB.TabIndex
and F.DateAndTime = FB.LastTime

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Robert]

Thanks, I'll give it a try.

It is unclear from your message where you want to apply the criteria. Are
you trying to get the max dateandtime where the status is "U" or do you
want only records returned where the status is "u".

PERHAPS what you want is
SELECT F.TabIndex, F.DateandTime, F.Val
FROM FloatTable as F INNER JOIN
(SELECT FA.TabIndex, Max(Fa.FloatDateAndTime) LastTime
FROM FloatTable as Fa
GROUP BY Fa.TabIndex) as FB
ON F.TabIndex = FB.TabIndex
and F.DateAndTime = FB.LastTime
WHERE F.DateAndTime >=#12/24/2008# and <#12/25/2008#
AND F.Status = "U"

OR perhaps
SELECT F.TabIndex, F.DateandTime, F.Val
FROM FloatTable as F INNER JOIN
(SELECT FA.TabIndex, Max(Fa.FloatDateAndTime) LastTime
FROM FloatTable as Fa
WHERE Fa.Status = "U"
AND F.DateAndTime >=#12/24/2008# and <#12/25/2008#
GROUP BY Fa.TabIndex) as FB
ON F.TabIndex = FB.TabIndex
and F.DateAndTime = FB.LastTime

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I need help with the SQL syntax of a query. All I want to do is retrieve
the corresponding value of Val for the Last DateAndTime grouped by
TabIndex. Of course, you cannot do this in a simple aggregate query
because it wont let me have Val unless I group on it, but I cannot group
on Val. So I have to join the table to itself but I cannot get it to
work.

FloatTable

Floatid
TabIndex
DateAndTime
Val

Can anyone provide the correct syntax to join FloatTable to itself and
retrieve Val for the last (most recent) DateAndTime grouped by TabIndex?


This does not work:

SELECT Val
FROM FloatTable AS A1 INNER JOIN [SELECT max(DateAndTime) AS
MaxOfDateAndTime, TagIndex FROM FloatTable WHERE (DateAndTime Like "*"
& "12/24/2008" & "*" AND Status<>"U") GROUP BY TagIndex ] AS B1 ON
a1.dateandtime=b1.MaxOfDateAndTime AND a1.tagindex=b1.tagindex;

Not sure if my constants have anything to do with the problem.

Rob

 

[Robert]

Thank you.

I need help with the SQL syntax of a query. All I want to do is
retrieve the corresponding value of Val

Is that really the name of your field? If so, change it now. "Val" is a
reserved keyword, being the name of a VBA function. If you won't/can't
change it, you are going to need to remember to surround it with brackets
[] whenever you use it in sql statements.

for the Last DateAndTime
grouped by TabIndex. Of course, you cannot do this in a simple
aggregate query because it wont let me have Val unless I group on it,
but I cannot group on Val. So I have to join the table to itself but
I cannot get it to work.
FloatTable

Floatid
TabIndex
DateAndTime
Val

Can anyone provide the correct syntax to join FloatTable to itself and
retrieve Val for the last (most recent) DateAndTime grouped by
TabIndex?

This does not work:

What happens? Does your computer crash? Do you get the wrong results? Do
you get an error message? What is the error message?

SELECT Val
FROM FloatTable AS A1 INNER JOIN [SELECT max(DateAndTime) AS
MaxOfDateAndTime, TagIndex FROM FloatTable WHERE (DateAndTime Like
"*" & "12/24/2008" & "*" AND Status<>"U") GROUP BY TagIndex ] AS B1
ON a1.dateandtime=b1.MaxOfDateAndTime AND a1.tagindex=b1.tagindex;

Not sure if my constants have anything to do with the problem.

Well, if that DateAndTime field is a Date/Time field, then I can tell you
that of course your constants are part of your problem. Date/Times are not
strings and cannot be used in a Like comparison. If DateAndTime is a Text
field, then disregard that. It really helps to let us know the datatypes
of the fields you are trying to filter.

I'm not sure what you are trying to get. If there are multiple tabindex
values, the query as you have built it will return a list of "Val" values
with no context. It would help showing us a few rows of sample data in
tabular format, followed by the result you wish to retrieve, also in
tabular format if you wish to retrieve multiple rows from that set of
sample data.



--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"