A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

show top values



 
 
Thread Tools Display Modes
  #1  
Old December 30th, 2009, 01:40 AM posted to microsoft.public.access.queries
Ian
external usenet poster
 
Posts: 485
Default show top values

posted to SQL Queries English with no reponse -- sorry for the repeat

I have a single table with 5 fields:

READID (key, INT), ID (user identifier, INT), READDATE, TOD (time of day -
as INT 1, 2, 3, ...24), CONSUMPTION (measurement).

I'm looking to return the maximum measurement (CONSUMPTION) from each day
for each user with it's corresponding TOD but having problem when I include
the TOD in the query (it tries to show the max(consumption) for each TOD but
there's only one measurement for each TOD). Is there a way to show the max
consumption each day for each user but also show the corresponding TOD?

SQL statment and result below that
SELECT DISTINCT A.ID, A.DATEREAD, A.TOD, MAX(CONSUMPTION)
FROM DATANORM AS A
WHERE A.DATEREAD '2008/11/30'
GROUP BY A.ID, A.TOD, A.DATEREAD
--HAVING CONSUMPTION = MAX(CONSUMPTION)
ORDER BY ID,DATEREAD

Here is a sample of the current query returning every TOD
USER DATE TOD CONSUMPTION
1 2008-12-01 3 4.740000
1 2008-12-01 10 4.400000
1 2008-12-01 17 4.680000
1 2008-12-01 5 3.990000
1 2008-12-01 24 6.560000
1 2008-12-01 12 3.570000
1 2008-12-01 7 4.320000
1 2008-12-01 14 3.530000
1 2008-12-01 19 4.880000
1 2008-12-01 21 5.210000
1 2008-12-01 2 4.060000
1 2008-12-01 9 6.370000
1 2008-12-01 4 4.010000
1 2008-12-01 16 3.960000
1 2008-12-01 23 4.670000
1 2008-12-01 18 5.590000
1 2008-12-01 11 3.770000
1 2008-12-01 6 4.410000
1 2008-12-01 1 5.950000
1 2008-12-01 13 4.450000
1 2008-12-01 20 4.840000
1 2008-12-01 8 4.640000
1 2008-12-01 22 5.040000
1 2008-12-01 15 3.940000
1 2008-12-02 5 5.460000
1 2008-12-02 12 4.390000
1 2008-12-02 10 4.810000
1 2008-12-02 17 5.020000
1 2008-12-02 19 5.730000
1 2008-12-02 3 5.590000
1 2008-12-02 15 4.710000
1 2008-12-02 13 5.510000

  #2  
Old December 30th, 2009, 04:38 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default show top values

Use a sub-query in the FROM clause. The following may return more than one
record for any date if there is a tie for the max of Consumption on that date.

SELECT DataNorm.*
FROM DataNorm INNER JOIN
(SELECT DateRead, Max(Consumption) as TheMax
FROM DataNorm
GROUP BY DateRead)
ON DataNorm.DateRead = Temp.DateRead
AND DataNorm.Consumption = Temp.TheMax
WHERE DataNorm.DATEREAD '2008/11/30'
ORDER BY ID,DATEREAD

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

Ian wrote:
posted to SQL Queries English with no reponse -- sorry for the repeat

I have a single table with 5 fields:

READID (key, INT), ID (user identifier, INT), READDATE, TOD (time of day -
as INT 1, 2, 3, ...24), CONSUMPTION (measurement).

I'm looking to return the maximum measurement (CONSUMPTION) from each day
for each user with it's corresponding TOD but having problem when I include
the TOD in the query (it tries to show the max(consumption) for each TOD but
there's only one measurement for each TOD). Is there a way to show the max
consumption each day for each user but also show the corresponding TOD?

SQL statment and result below that
SELECT DISTINCT A.ID, A.DATEREAD, A.TOD, MAX(CONSUMPTION)
FROM DATANORM AS A
WHERE A.DATEREAD '2008/11/30'
GROUP BY A.ID, A.TOD, A.DATEREAD
--HAVING CONSUMPTION = MAX(CONSUMPTION)
ORDER BY ID,DATEREAD

Here is a sample of the current query returning every TOD
USER DATE TOD CONSUMPTION
1 2008-12-01 3 4.740000
1 2008-12-01 10 4.400000
1 2008-12-01 17 4.680000
1 2008-12-01 5 3.990000
1 2008-12-01 24 6.560000
1 2008-12-01 12 3.570000
1 2008-12-01 7 4.320000
1 2008-12-01 14 3.530000
1 2008-12-01 19 4.880000
1 2008-12-01 21 5.210000
1 2008-12-01 2 4.060000
1 2008-12-01 9 6.370000
1 2008-12-01 4 4.010000
1 2008-12-01 16 3.960000
1 2008-12-01 23 4.670000
1 2008-12-01 18 5.590000
1 2008-12-01 11 3.770000
1 2008-12-01 6 4.410000
1 2008-12-01 1 5.950000
1 2008-12-01 13 4.450000
1 2008-12-01 20 4.840000
1 2008-12-01 8 4.640000
1 2008-12-01 22 5.040000
1 2008-12-01 15 3.940000
1 2008-12-02 5 5.460000
1 2008-12-02 12 4.390000
1 2008-12-02 10 4.810000
1 2008-12-02 17 5.020000
1 2008-12-02 19 5.730000
1 2008-12-02 3 5.590000
1 2008-12-02 15 4.710000
1 2008-12-02 13 5.510000

  #3  
Old December 30th, 2009, 05:43 PM posted to microsoft.public.access.queries
Anne
external usenet poster
 
Posts: 445
Default show top values

You could combine the two fields for calculation. Format the fields to show
even lengths to make separation easier.
FormatCon: Format([consumption],"###0.000000")
FormatTod: Format([Tod],"00")
ConTod: [FormatCon] & "-" & [FormatTod]
new query max those three fields.
next query: Consumption: Left([maxofcontod],8), TOD: Right([maxofcontod],2)
You can then reformat the numbers back tohow you want them.


Actually for those

"Ian" wrote:

posted to SQL Queries English with no reponse -- sorry for the repeat

I have a single table with 5 fields:

READID (key, INT), ID (user identifier, INT), READDATE, TOD (time of day -
as INT 1, 2, 3, ...24), CONSUMPTION (measurement).

I'm looking to return the maximum measurement (CONSUMPTION) from each day
for each user with it's corresponding TOD but having problem when I include
the TOD in the query (it tries to show the max(consumption) for each TOD but
there's only one measurement for each TOD). Is there a way to show the max
consumption each day for each user but also show the corresponding TOD?

SQL statment and result below that
SELECT DISTINCT A.ID, A.DATEREAD, A.TOD, MAX(CONSUMPTION)
FROM DATANORM AS A
WHERE A.DATEREAD '2008/11/30'
GROUP BY A.ID, A.TOD, A.DATEREAD
--HAVING CONSUMPTION = MAX(CONSUMPTION)
ORDER BY ID,DATEREAD

Here is a sample of the current query returning every TOD
USER DATE TOD CONSUMPTION
1 2008-12-01 3 4.740000
1 2008-12-01 10 4.400000
1 2008-12-01 17 4.680000
1 2008-12-01 5 3.990000
1 2008-12-01 24 6.560000
1 2008-12-01 12 3.570000
1 2008-12-01 7 4.320000
1 2008-12-01 14 3.530000
1 2008-12-01 19 4.880000
1 2008-12-01 21 5.210000
1 2008-12-01 2 4.060000
1 2008-12-01 9 6.370000
1 2008-12-01 4 4.010000
1 2008-12-01 16 3.960000
1 2008-12-01 23 4.670000
1 2008-12-01 18 5.590000
1 2008-12-01 11 3.770000
1 2008-12-01 6 4.410000
1 2008-12-01 1 5.950000
1 2008-12-01 13 4.450000
1 2008-12-01 20 4.840000
1 2008-12-01 8 4.640000
1 2008-12-01 22 5.040000
1 2008-12-01 15 3.940000
1 2008-12-02 5 5.460000
1 2008-12-02 12 4.390000
1 2008-12-02 10 4.810000
1 2008-12-02 17 5.020000
1 2008-12-02 19 5.730000
1 2008-12-02 3 5.590000
1 2008-12-02 15 4.710000
1 2008-12-02 13 5.510000

  #4  
Old December 30th, 2009, 05:47 PM posted to microsoft.public.access.queries
Ian
external usenet poster
 
Posts: 485
Default show top values

I'm getting a sytax error with the ON clause (line 7). Is it in the correct
place?

"John Spencer" wrote:

Use a sub-query in the FROM clause. The following may return more than one
record for any date if there is a tie for the max of Consumption on that date.

SELECT DataNorm.*
FROM DataNorm INNER JOIN
(SELECT DateRead, Max(Consumption) as TheMax
FROM DataNorm
GROUP BY DateRead)
ON DataNorm.DateRead = Temp.DateRead
AND DataNorm.Consumption = Temp.TheMax
WHERE DataNorm.DATEREAD '2008/11/30'
ORDER BY ID,DATEREAD

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

Ian wrote:
posted to SQL Queries English with no reponse -- sorry for the repeat

I have a single table with 5 fields:

READID (key, INT), ID (user identifier, INT), READDATE, TOD (time of day -
as INT 1, 2, 3, ...24), CONSUMPTION (measurement).

I'm looking to return the maximum measurement (CONSUMPTION) from each day
for each user with it's corresponding TOD but having problem when I include
the TOD in the query (it tries to show the max(consumption) for each TOD but
there's only one measurement for each TOD). Is there a way to show the max
consumption each day for each user but also show the corresponding TOD?

SQL statment and result below that
SELECT DISTINCT A.ID, A.DATEREAD, A.TOD, MAX(CONSUMPTION)
FROM DATANORM AS A
WHERE A.DATEREAD '2008/11/30'
GROUP BY A.ID, A.TOD, A.DATEREAD
--HAVING CONSUMPTION = MAX(CONSUMPTION)
ORDER BY ID,DATEREAD

Here is a sample of the current query returning every TOD
USER DATE TOD CONSUMPTION
1 2008-12-01 3 4.740000
1 2008-12-01 10 4.400000
1 2008-12-01 17 4.680000
1 2008-12-01 5 3.990000
1 2008-12-01 24 6.560000
1 2008-12-01 12 3.570000
1 2008-12-01 7 4.320000
1 2008-12-01 14 3.530000
1 2008-12-01 19 4.880000
1 2008-12-01 21 5.210000
1 2008-12-01 2 4.060000
1 2008-12-01 9 6.370000
1 2008-12-01 4 4.010000
1 2008-12-01 16 3.960000
1 2008-12-01 23 4.670000
1 2008-12-01 18 5.590000
1 2008-12-01 11 3.770000
1 2008-12-01 6 4.410000
1 2008-12-01 1 5.950000
1 2008-12-01 13 4.450000
1 2008-12-01 20 4.840000
1 2008-12-01 8 4.640000
1 2008-12-01 22 5.040000
1 2008-12-01 15 3.940000
1 2008-12-02 5 5.460000
1 2008-12-02 12 4.390000
1 2008-12-02 10 4.810000
1 2008-12-02 17 5.020000
1 2008-12-02 19 5.730000
1 2008-12-02 3 5.590000
1 2008-12-02 15 4.710000
1 2008-12-02 13 5.510000

.

  #5  
Old December 30th, 2009, 06:08 PM posted to microsoft.public.access.queries
Ian
external usenet poster
 
Posts: 485
Default show top values

Think I figured it out -- did not specify the nested query as being called
Temp but it's not working quite right John -- it's showing the maxconsump per
day (e.g. 1 reading per day). I'd like it to show the max reading for each
user each day.


"John Spencer" wrote:

Use a sub-query in the FROM clause. The following may return more than one
record for any date if there is a tie for the max of Consumption on that date.

SELECT DataNorm.*
FROM DataNorm INNER JOIN
(SELECT DateRead, Max(Consumption) as TheMax
FROM DataNorm
GROUP BY DateRead)
ON DataNorm.DateRead = Temp.DateRead
AND DataNorm.Consumption = Temp.TheMax
WHERE DataNorm.DATEREAD '2008/11/30'
ORDER BY ID,DATEREAD

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

Ian wrote:
posted to SQL Queries English with no reponse -- sorry for the repeat

I have a single table with 5 fields:

READID (key, INT), ID (user identifier, INT), READDATE, TOD (time of day -
as INT 1, 2, 3, ...24), CONSUMPTION (measurement).

I'm looking to return the maximum measurement (CONSUMPTION) from each day
for each user with it's corresponding TOD but having problem when I include
the TOD in the query (it tries to show the max(consumption) for each TOD but
there's only one measurement for each TOD). Is there a way to show the max
consumption each day for each user but also show the corresponding TOD?

SQL statment and result below that
SELECT DISTINCT A.ID, A.DATEREAD, A.TOD, MAX(CONSUMPTION)
FROM DATANORM AS A
WHERE A.DATEREAD '2008/11/30'
GROUP BY A.ID, A.TOD, A.DATEREAD
--HAVING CONSUMPTION = MAX(CONSUMPTION)
ORDER BY ID,DATEREAD

Here is a sample of the current query returning every TOD
USER DATE TOD CONSUMPTION
1 2008-12-01 3 4.740000
1 2008-12-01 10 4.400000
1 2008-12-01 17 4.680000
1 2008-12-01 5 3.990000
1 2008-12-01 24 6.560000
1 2008-12-01 12 3.570000
1 2008-12-01 7 4.320000
1 2008-12-01 14 3.530000
1 2008-12-01 19 4.880000
1 2008-12-01 21 5.210000
1 2008-12-01 2 4.060000
1 2008-12-01 9 6.370000
1 2008-12-01 4 4.010000
1 2008-12-01 16 3.960000
1 2008-12-01 23 4.670000
1 2008-12-01 18 5.590000
1 2008-12-01 11 3.770000
1 2008-12-01 6 4.410000
1 2008-12-01 1 5.950000
1 2008-12-01 13 4.450000
1 2008-12-01 20 4.840000
1 2008-12-01 8 4.640000
1 2008-12-01 22 5.040000
1 2008-12-01 15 3.940000
1 2008-12-02 5 5.460000
1 2008-12-02 12 4.390000
1 2008-12-02 10 4.810000
1 2008-12-02 17 5.020000
1 2008-12-02 19 5.730000
1 2008-12-02 3 5.590000
1 2008-12-02 15 4.710000
1 2008-12-02 13 5.510000

.

  #6  
Old December 30th, 2009, 06:14 PM posted to microsoft.public.access.queries
Ian
external usenet poster
 
Posts: 485
Default show top values

SUCCESS!!!! THANK YOU SO MUCH JOHN.

SELECT DataNorm.*
FROM DataNorm INNER JOIN
(SELECT DateRead, ID, Max(Consumption) as TheMax
FROM DataNorm
GROUP BY DateRead, ID) Temp
ON DataNorm.DateRead = Temp.DateRead
AND DataNorm.Consumption = Temp.TheMax
AND DATANORM.ID = Temp.ID
WHERE DataNorm.DATEREAD '2008/11/30'
ORDER BY ID, DATEREAD


"John Spencer" wrote:

Use a sub-query in the FROM clause. The following may return more than one
record for any date if there is a tie for the max of Consumption on that date.

SELECT DataNorm.*
FROM DataNorm INNER JOIN
(SELECT DateRead, Max(Consumption) as TheMax
FROM DataNorm
GROUP BY DateRead)
ON DataNorm.DateRead = Temp.DateRead
AND DataNorm.Consumption = Temp.TheMax
WHERE DataNorm.DATEREAD '2008/11/30'
ORDER BY ID,DATEREAD

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

Ian wrote:
posted to SQL Queries English with no reponse -- sorry for the repeat

I have a single table with 5 fields:

READID (key, INT), ID (user identifier, INT), READDATE, TOD (time of day -
as INT 1, 2, 3, ...24), CONSUMPTION (measurement).

I'm looking to return the maximum measurement (CONSUMPTION) from each day
for each user with it's corresponding TOD but having problem when I include
the TOD in the query (it tries to show the max(consumption) for each TOD but
there's only one measurement for each TOD). Is there a way to show the max
consumption each day for each user but also show the corresponding TOD?

SQL statment and result below that
SELECT DISTINCT A.ID, A.DATEREAD, A.TOD, MAX(CONSUMPTION)
FROM DATANORM AS A
WHERE A.DATEREAD '2008/11/30'
GROUP BY A.ID, A.TOD, A.DATEREAD
--HAVING CONSUMPTION = MAX(CONSUMPTION)
ORDER BY ID,DATEREAD

Here is a sample of the current query returning every TOD
USER DATE TOD CONSUMPTION
1 2008-12-01 3 4.740000
1 2008-12-01 10 4.400000
1 2008-12-01 17 4.680000
1 2008-12-01 5 3.990000
1 2008-12-01 24 6.560000
1 2008-12-01 12 3.570000
1 2008-12-01 7 4.320000
1 2008-12-01 14 3.530000
1 2008-12-01 19 4.880000
1 2008-12-01 21 5.210000
1 2008-12-01 2 4.060000
1 2008-12-01 9 6.370000
1 2008-12-01 4 4.010000
1 2008-12-01 16 3.960000
1 2008-12-01 23 4.670000
1 2008-12-01 18 5.590000
1 2008-12-01 11 3.770000
1 2008-12-01 6 4.410000
1 2008-12-01 1 5.950000
1 2008-12-01 13 4.450000
1 2008-12-01 20 4.840000
1 2008-12-01 8 4.640000
1 2008-12-01 22 5.040000
1 2008-12-01 15 3.940000
1 2008-12-02 5 5.460000
1 2008-12-02 12 4.390000
1 2008-12-02 10 4.810000
1 2008-12-02 17 5.020000
1 2008-12-02 19 5.730000
1 2008-12-02 3 5.590000
1 2008-12-02 15 4.710000
1 2008-12-02 13 5.510000

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.