Aggregate function help

[Tania C]

Here is the problem. I'm dealing with one table. The data in this table consists of percentages in one field and times in another (all the times are unique). I am trying to get the query to return ONE record consisting of the maximum percentage, but also include the time.

In design view, I click on "total" and "Max" for the percentage field. What do I do for the "time" field? If I leave it as "group by", it returns every record, since each time is unique. Help!

~Tania

 

[Fuga]

In design view for the percentage field:
sort descending

Next to the summation sign in the toolbar where it says "all", put "1".

or, in SQL view:

SELECT TOP 1 yourtable.Percentagefield, yourtable.timefield
FROM yourtable
ORDER BY yourtable.Percentagefield DESC;

Fuga.

 

[Tania C]

Thanks Fuga!!!!!!


Most grateful,

Tania

 

[Fuga]

Glad to help.

Fuga.

 

[Tania C]

With the same query in mind, I have one other field that I would like to group on. There are only two variations of the data in that field, at this point. Let me explain it to you and maybe it will help.

Field one "server name" (hundreds of records, but only two servers at this time)
Field two "CPU Percentage" (number)
Field three "date/time" (I guess not totally unique, as each server will be snapshot at the same times.)

I am trying to get just the maximum CPU percentage, but need them for each server. By putting "1" or even "2" in the "Top Values"drop down, I'm not getting both server's maximum CPU percentage.


~Tania

 

[Tania C]

I need this information in the "server" header for a report. I can just link it to the specific server within the text box, right?

~Tania

 

[Fuga]

Actually, this might be a bit more tricky. I tried once to find the top value every day for some 500 days with 20 or so entries/day. I ended up with a query that crashed the computer and then I gave up.

But I think there´s an article on ms knowledge base. I´ll have a look.

Also, there´s a thread on the forum (that´s why I know there´s an article), but I don´t remember the topic just now.

Fuga.

 

[Fuga]

This might be it!
http://support.microsoft.com/default.aspx?scid=kb;en-us;153747&Product=acc97

Fuga.

 

[Tania C]

Thanks for your help. I just stepped back into Access after several years hiatus. I thought this was a simple query and perhaps was just was forgetting some basics. Dratz! Using my noggen more than I thought I had to.

~Tania

 

[Fuga]

Another one:

http://support.microsoft.com/default.aspx?scid=kb;en-us;132056&Product=acc97

Good luck!

Fuga.

 

[Tania C]

AWESOME!!!! It works!!!! I just used "Top 1" instead of "Top 3" in the statement. You rock!!!!


THANKS!

Tania