value max every year

  • Thread starter Thread starter giorgio
  • Start date Start date
G

giorgio

I wish to try in maximum value of a field for every year and have used the
expression:

SELECT Max(tablename.fieldname) AS MaxOffieldname
FROM tablename
GROUP BY Year([datafield]);

but I don't have some reference and so I must view all the records,
therefore have tried this expression :

SELECT tablename.datafield, tablename.fieldname
FROM tablename
WHERE tablename.fieldname in (SELECT Max(tablename.fieldname) AS
MaxOffieldname
FROM tablename GROUP BY Year([datafield]))

but if two values are identical , give back them equally also not being the
maximum value
Is There other ways to have them?

ex 2001 max 1000,2002 max 1500,2003 max 1350,2004 max 1728 but i have 1000
too.
i need 2001 or(something other in my table like ID) 1000,2002 1500,2003
1350,2004 1728
not 1000, 1500, 1350, 1728
not 2001 1000,2002 1500,2003 1350,2004 1000 1728
thanks
 
Does:

SELECT
Year([datafield]) AS [Year],
Max(tablename.fieldname) AS MaxOffieldname
FROM tablename
GROUP BY Year([datafield]);

give you what you want?
 
It's ok too. But i need something more specific like datafield (dd/mm/yyyy)
or ID.

Brian Camire said:
Does:

SELECT
Year([datafield]) AS [Year],
Max(tablename.fieldname) AS MaxOffieldname
FROM tablename
GROUP BY Year([datafield]);

give you what you want?


giorgio said:
I wish to try in maximum value of a field for every year and have used the
expression:

SELECT Max(tablename.fieldname) AS MaxOffieldname
FROM tablename
GROUP BY Year([datafield]);

but I don't have some reference and so I must view all the records,
therefore have tried this expression :

SELECT tablename.datafield, tablename.fieldname
FROM tablename
WHERE tablename.fieldname in (SELECT Max(tablename.fieldname) AS
MaxOffieldname
FROM tablename GROUP BY Year([datafield]))

but if two values are identical , give back them equally also not being the
maximum value
Is There other ways to have them?

ex 2001 max 1000,2002 max 1500,2003 max 1350,2004 max 1728 but i have 1000
too.
i need 2001 or(something other in my table like ID) 1000,2002 1500,2003
1350,2004 1728
not 1000, 1500, 1350, 1728
not 2001 1000,2002 1500,2003 1350,2004 1000 1728
thanks
Click to expand...
Click to expand...
 
I'm not sure exactly what you mean, but if you have a table named
"tablename", with a Date/Time field named "datafield" and another field
named "fieldname", the query I posted will return one record for each
distinct year represented by the values in "datafield". These records will
give the year, and the maximum of the values of the "fieldname" field from
the records where "datafield" falls in that year.

For example, if "tablename" looks like this:

datafield, fieldname
1/1/2001, 500
2/1/2001, 1000
3/1/2001, 700
1/1/2002, 800
2/1/2002, 300
3/1/2002, 1500
1/1/2003, 1350
2/1/2003, 1100
3/1/2003, 1000
1/1/2004, 1728
2/1/2004, 1728
3/1/2004, 1500

the query will return something like this:

Year, MaxOffieldname
2001, 1000
2002, 1500
2003, 1350
2004, 1728

If your table has other fields, and you want to see the values of those
fields from the record in each year with the maximum value of "fieldname",
you might want to look at:

http://www.mvps.org/access/queries/qry0020.htm

giorgio said:
It's ok too. But i need something more specific like datafield (dd/mm/yyyy)
or ID.

Brian Camire said:
Does:

SELECT
Year([datafield]) AS [Year],
Max(tablename.fieldname) AS MaxOffieldname
FROM tablename
GROUP BY Year([datafield]);

give you what you want?


giorgio said:
I wish to try in maximum value of a field for every year and have used the
expression:

SELECT Max(tablename.fieldname) AS MaxOffieldname
FROM tablename
GROUP BY Year([datafield]);

but I don't have some reference and so I must view all the records,
therefore have tried this expression :

SELECT tablename.datafield, tablename.fieldname
FROM tablename
WHERE tablename.fieldname in (SELECT Max(tablename.fieldname) AS
MaxOffieldname
FROM tablename GROUP BY Year([datafield]))

but if two values are identical , give back them equally also not
Click to expand...
being
the
maximum value
Is There other ways to have them?

ex 2001 max 1000,2002 max 1500,2003 max 1350,2004 max 1728 but i have 1000
too.
i need 2001 or(something other in my table like ID) 1000,2002 1500,2003
1350,2004 1728
not 1000, 1500, 1350, 1728
not 2001 1000,2002 1500,2003 1350,2004 1000 1728
thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top