How to query for last data

[Nova]

I have 10 fields f1,f2......f10
and I want to give all fiields with condition of group by f1 and last data
of f2 (integer).
I try to create query by using group by(f1) and max(f2) but f3 to f10 I
don't know I should use what (min,max,expression,var, etc.) ?

 

[golfinray]

If you want the max of each of those, use max. If not just leave them on
group by.

 

[John Spencer]

Do you want the data in the remaining fields that match the Max value of F2?

SELECT [YourTable].*
FROM [YourTable]
WHERE F2 =
(SELECT Max(F2) FROM [YourTable] as TEMP WHERE Temp.F1 = [YourTable].F1)

If you don't know how to build this query in the SQL view and cannot figure it
out in query Design view, post back and I will try to explain how to do this.

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

 

[Nova]

Thanks
but

(SELECT Max(F2) FROM [YourTable] as TEMP WHERE Temp.F1 = [YourTable].F1)

I don't understand what is difference between TEMP (Field?) and Temp (Table?)

"John Spencer" เขียน:

Do you want the data in the remaining fields that match the Max value of F2?

SELECT [YourTable].*
FROM [YourTable]
WHERE F2 =
(SELECT Max(F2) FROM [YourTable] as TEMP WHERE Temp.F1 = [YourTable].F1)

If you don't know how to build this query in the SQL view and cannot figure it
out in query Design view, post back and I will try to explain how to do this.

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

I have 10 fields f1,f2......f10
and I want to give all fiields with condition of group by f1 and last data
of f2 (integer).
I try to create query by using group by(f1) and max(f2) but f3 to f10 I
don't know I should use what (min,max,expression,var, etc.) ?

.

 

[John Spencer]

You are using two instances of your one table. In order for SQL to know that
there are two instances being used and which instance of the table to use you
must assign a name to the second instance.

(BY two instances I mean you are pointing the one table twice, not that you
have a duplicate table).

So Temp is a name for the second instance.

To build this query in DESIGN view
== Add your table to the query
== Add the fields you want to see
== Under field F2 you would type the following into the criteria (all as one line)
=(SELECT Max([F2]) FROM [YourTable] as TEMP
WHERE Temp.[F1]=[YourTable].[F1])

What happens is that for every record in the table, the sub-query (in the
criteria) gets the Max value of F2 for the records that have the current value
of F1 in the main query. Then it decides whether or not that Max of F2 field
matches the current value of the F2 field in the main query.

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

 

[Nova]

Thanks John. It works

"John Spencer" เขียน:

You are using two instances of your one table. In order for SQL to know that
there are two instances being used and which instance of the table to use you
must assign a name to the second instance.

(BY two instances I mean you are pointing the one table twice, not that you
have a duplicate table).

So Temp is a name for the second instance.

To build this query in DESIGN view
== Add your table to the query
== Add the fields you want to see
== Under field F2 you would type the following into the criteria (all as one line)
=(SELECT Max([F2]) FROM [YourTable] as TEMP
WHERE Temp.[F1]=[YourTable].[F1])

What happens is that for every record in the table, the sub-query (in the
criteria) gets the Max value of F2 for the records that have the current value
of F1 in the main query. Then it decides whether or not that Max of F2 field
matches the current value of the F2 field in the main query.

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

Thanks
but

(SELECT Max(F2) FROM [YourTable] as TEMP WHERE Temp.F1 = [YourTable].F1)

I don't understand what is difference between TEMP (Field?) and Temp (Table?)

.