MAX and Grouping

[chrisdavis]

Hi All..I have been using Access for about 10 years now but something
occurred to me that when I'm trying to GROUP and MAX, it's pulling it
for different fields.

Example:

I have times by hour (field name CSTHR)

08:00am 50
09:00am 55
10:00am etc. 65

and I have a total calls per second.(field name TOTALCCS)

I'm trying to get the busiest hour of the day using MAX..If I take off
CSTHR out of the grouping it shows me the max for the query (i.e.,
65). I also want the correct time to show up (i.e., 10:00am) but if I
do FIRST, LAST, MIN, or MAX on the CSTHR column, it doesn't correlate
to the correct record..It shows a different time for that field
instead of the correct record I may want to add other fields in the
record like ID, DATE, etc. to show up on that line.

I need to have the results like this:

10:00am 65

and NOT

08:00am (if using first) and MAX of 65.

I have read other articles and some of them don't make sense and I
can't get this to work..Anybody helps me I would greatly appreciate it!

[Rich P]

Hi Chris,

Could you post some sample data: like

1)

fld1 fld2 fld3 fld4 ...
xyz 123 8am 15
yyz 445 8:15am 20
mtr 292 8:20am 17
...
2)
and then post some sample data of your actual query results - maybe
include the sql for the query

3)
and then post a sample of how you want the data to appear in the query
result

this will make it easier to visualize your problem (well, for me anyway)
and then find a solution.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[JHB]

Hello,
try, (repalce "Table2" with your table name.

SELECT Tabel2.CSTHR, Tabel2.TOTALCCS
FROM Tabel2
WHERE (((Tabel2.TOTAL CCS)=(SELECT Max(Tabel2.TOTA LCCS) AS fTOTALCCS FROM
Tabel2;)));

Regards
Jørn
"chrisdavis " <pi************ *@gmail.comskre v i en meddelelse
news:11******** **************@ u2g2000hsc.goog legroups.com...

Hi All..I have been using Access for about 10 years now but something
occurred to me that when I'm trying to GROUP and MAX, it's pulling it
for different fields.

Example:

I have times by hour (field name CSTHR)

08:00am 50
09:00am 55
10:00am etc. 65

and I have a total calls per second.(field name TOTALCCS)

I'm trying to get the busiest hour of the day using MAX..If I take off
CSTHR out of the grouping it shows me the max for the query (i.e.,
65). I also want the correct time to show up (i.e., 10:00am) but if I
do FIRST, LAST, MIN, or MAX on the CSTHR column, it doesn't correlate
to the correct record..It shows a different time for that field
instead of the correct record I may want to add other fields in the
record like ID, DATE, etc. to show up on that line.

I need to have the results like this:

10:00am 65

and NOT

08:00am (if using first) and MAX of 65.

I have read other articles and some of them don't make sense and I
can't get this to work..Anybody helps me I would greatly appreciate it!

[chrisdavis]

On Jun 27, 2:49 pm, "JHB" <[e-mail] ote:

Hello,
try, (repalce "Table2" with your table name.

SELECT Tabel2.CSTHR, Tabel2.TOTALCCS
FROM Tabel2
WHERE (((Tabel2.TOTAL CCS)=(SELECT Max(Tabel2.TOTA LCCS) AS fTOTALCCS FROM
Tabel2;)));

Regards
Jørn

"chrisdavis " <pianoplayerd.. [e-mail] v i en meddelelsenews: 11************* *********@u2g20 00hsc.googlegro ups.com...

Hi All..I have been using Access for about 10 years now but something
occurred to me that when I'm trying to GROUP and MAX, it's pulling it
for different fields.

Example:

I have times by hour (field name CSTHR)

08:00am 50
09:00am 55
10:00am etc. 65

and I have a total calls per second.(field name TOTALCCS)

I'm trying to get the busiest hour of the day using MAX..If I take off
CSTHR out of the grouping it shows me the max for the query (i.e.,
65). I also want the correct time to show up (i.e., 10:00am) but if I
do FIRST, LAST, MIN, or MAX on the CSTHR column, it doesn't correlate
to the correct record..It shows a different time for that field
instead of the correct record I may want to add other fields in the
record like ID, DATE, etc. to show up on that line.

I need to have the results like this:

10:00am 65

and NOT

08:00am (if using first) and MAX of 65.

I have read other articles and some of them don't make sense and I
can't get this to work..Anybody helps me I would greatly appreciate it!- Hide quoted text -

- Show quoted text -

Hi! The subselect you gave me worked; however how do I group by date
or whatever now? It's looking at the entire table and giving me the
max..I may want to see it by date, or ID, or by hour..cool beans!

[chrisdavis]

On Jun 27, 2:39 pm, Rich P <[e-mail] mwrote:

Hi Chris,

Could you post some sample data: like

1)

fld1 fld2 fld3 fld4 ...
xyz 123 8am 15
yyz 445 8:15am 20
mtr 292 8:20am 17
..

2)
and then post some sample data of your actual query results - maybe
include the sql for the query

3)
and then post a sample of how you want the data to appear in the query
result

this will make it easier to visualize your problem (well, for me anyway)
and then find a solution.

Rich

*** Sent via Developersdexht tp://www.developersd ex.com***

Hey Rich..thanks for replying to my ad..I got the subselect to work
for the max but it's looking at the entire table..how do I group by
fields to give me the max for each grouping?

[John Winterbottom]

Hi Chris

this may of help

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

chrisdavis wrote:

Hi All..I have been using Access for about 10 years now but something
occurred to me that when I'm trying to GROUP and MAX, it's pulling it
for different fields.

Example:

I have times by hour (field name CSTHR)

08:00am 50
09:00am 55
10:00am etc. 65

and I have a total calls per second.(field name TOTALCCS)

I'm trying to get the busiest hour of the day using MAX..If I take off
CSTHR out of the grouping it shows me the max for the query (i.e.,
65). I also want the correct time to show up (i.e., 10:00am) but if I
do FIRST, LAST, MIN, or MAX on the CSTHR column, it doesn't correlate
to the correct record..It shows a different time for that field
instead of the correct record I may want to add other fields in the
record like ID, DATE, etc. to show up on that line.

I need to have the results like this:

10:00am 65

and NOT

08:00am (if using first) and MAX of 65.

I have read other articles and some of them don't make sense and I
can't get this to work..Anybody helps me I would greatly appreciate it!

[chrisdavis]

On Jun 27, 3:05 pm, John Winterbottom <john...@rogers .cawrote:

Hi Chris

this may of help

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

chrisdavis wrote:

Hi All..I have been using Access for about 10 years now but something
occurred to me that when I'm trying to GROUP and MAX, it's pulling it
for different fields.

Example:

I have times by hour (field name CSTHR)

08:00am 50
09:00am 55
10:00am etc. 65

and I have a total calls per second.(field name TOTALCCS)

I'm trying to get the busiest hour of the day using MAX..If I take off
CSTHR out of the grouping it shows me the max for the query (i.e.,
65). I also want the correct time to show up (i.e., 10:00am) but if I
do FIRST, LAST, MIN, or MAX on the CSTHR column, it doesn't correlate
to the correct record..It shows a different time for that field
instead of the correct record I may want to add other fields in the
record like ID, DATE, etc. to show up on that line.

I need to have the results like this:

10:00am 65

and NOT

08:00am (if using first) and MAX of 65.

I have read other articles and some of them don't make sense and I
can't get this to work..Anybody helps me I would greatly appreciate it!- Hide quoted text -

- Show quoted text -

Hi John..I saw that article before and thanks for sending..I'm trying
to group by DATE or CSTHR or other groupings..The subselect is looking
through the entire table for the MAX and I want to GROUP by different
things..make sense?

[Mike Gramelspacher]

In article <11************ **********@w5g2 000hsg.googlegr oups.com>,
pi************* @gmail.com says...

On Jun 27, 2:49 pm, "JHB" <[e-mail] ote:

Hello,
try, (repalce "Table2" with your table name.

SELECT Tabel2.CSTHR, Tabel2.TOTALCCS
FROM Tabel2
WHERE (((Tabel2.TOTAL CCS)=(SELECT Max(Tabel2.TOTA LCCS) AS fTOTALCCS FROM
Tabel2;)));

Regards
Jørn

"chrisdavis " <pianoplayerd.. [e-mail] v i en meddelelsenews: 11************* *********@u2g20 00hsc.googlegro ups.com...

Hi All..I have been using Access for about 10 years now but something
occurred to me that when I'm trying to GROUP and MAX, it's pulling it
for different fields.

Example:

I have times by hour (field name CSTHR)

08:00am 50
09:00am 55
10:00am etc. 65

and I have a total calls per second.(field name TOTALCCS)

I'm trying to get the busiest hour of the day using MAX..If I take off
CSTHR out of the grouping it shows me the max for the query (i.e.,
65). I also want the correct time to show up (i.e., 10:00am) but if I
do FIRST, LAST, MIN, or MAX on the CSTHR column, it doesn't correlate
to the correct record..It shows a different time for that field
instead of the correct record I may want to add other fields in the
record like ID, DATE, etc. to show up on that line.

I need to have the results like this:

10:00am 65

and NOT

08:00am (if using first) and MAX of 65.

I have read other articles and some of them don't make sense and I
can't get this to work..Anybody helps me I would greatly appreciate it!- Hide quoted text -

- Show quoted text

not sure I am of any help, but

FORMAT(DateAdd( "h",DATEDiff("h ",0,[your datetime column]),0),"Medium Time")AS
[HOUR]

GROUP BY DateAdd("h",DAT EDiff("h",0,[your datetime column]),0)

[John Winterbottom]

Chris, post your simplified table structure, preferably as a CREATE
TABLE script with some sample data and the output you want.

chrisdavis wrote:

On Jun 27, 3:05 pm, John Winterbottom <john...@rogers .cawrote:

>Hi Chris

Hi John..I saw that article before and thanks for sending..I'm trying
to group by DATE or CSTHR or other groupings..The subselect is looking
through the entire table for the MAX and I want to GROUP by different
things..make sense?