Mix Max and Average

[Edgarr8]

Hi guys,

I have a query with the followinf information
ID Days
1 4
2 10
3 16
4 12
5 7
6 10
7 20

I would like to create a report that shows me the max and its related ID, the Min and its related ID and just the Average, how can i do that??

I have a feeling its something simple that i'm missing please help.

 

[rainman89]

You will have to do subreports to get it all in one report. Create the queries and reports that find the things you are looking for and then add them all to one report

 

[Edgarr8]

Oh so i can do that, but how can i create a query that will show me the max # and also the recordrelated to it?

Im thinking i should have a where clause or a join or something like that?

 

[Brianwarnock]

Use Top 1 and sort days in the correct way
eg
SELECT TOP 1 id, days
FROM Tablename
ORDER BY days DESC;

will give the max days and id.

Brian

 

[highandwild]

I am not sure if the sample data you posted is representative of your complete dataset but you have duplicate values so this will have an effect on the results.

The average of the figures is 11.28 so which ID would you want to find?

The min and max on that set of data will provide 4 and 20 and therefore ID's of 1 and 7 respectively but do 4 and 20 appear elsewhere in your dataset ?

 

[Edgarr8]

Like you said as long as I dont have 2 numbers with the same amount of days it works by doing two different queries, one for max and one for min to get my ID. SO thank you it works for now.

 

[Brianwarnock]

For the future.
If you require to pull in all IDs for the Min and the Max you will need 3 queries.
1 with 2 fields for the Days with Min and Max selected on the Totals row.
This is then joined back to the oruginal table in 2 separate queries one on MinofDays to Days to get all the Ids for Min, and the other on MaxofDays.

Brian

 

[highandwild]

Just had a rethink.

If it is just the report that you are after then you could have an unbound report with a text box for each of the calculations.

It worked for me.