# Q: How to calculate maximum of three in combined queryRSS

Last post Feb 15, 2017 06:00 AM by Zhi Lv - MSFT

• kcc20080522

None

0 Points

4 Posts

### Q: How to calculate maximum of three in combined query

The image shows works that I have done in access. The query "overall usage review" count the number of usages in each month by using combined query.

After this step, I want to show the maximum three of usage.quantity among 12months and calculate with the formula ( E.g., "Total MAX three months usage"/3)

E.g.,

``````Warehouse Part Number  ........................................................
A          X01        9   16   7   14   10   5   9   11   6   3   11   5
A          X02       20   22   10  12   20  17  18   29  14  13   11   19
B          X01        8    7   3   26   17   6   3   2    5   10   8   14
B          X05        9   10   16   6   10   4  13   12   6    4   3   6``````

I want to result it as below...

``````Warehouse Part Number   Maximum three usage quantity    Results
A          X01                   41                   41/3
A          X02                   71                   71/3
B          X01                   57                    19
B          X05                   39                    13``````

Someone told me to use dynamic sql, but I dont know what it is... Pls tell me how to solve this problem in detail. The problem stuck in my mind for a very long time...

• Zhi Lv - MSF...

All-Star

44736 Points

6713 Posts

Microsoft

### Re: Q: How to calculate maximum of three in combined query

Feb 15, 2017 06:00 AM|Zhi Lv - MSFT|LINK

Hi kcc20080522,

Please try to calculate the result in behind code.

For example:

```DataTable dt = new DataTable();

dt.Rows.Add("A", "X01", "9", "16", "7", "14", "10", "5", "9", "11", "6", "3", "11", "5");
dt.Rows.Add("A", "X02", "20", "22","10", "12", "20", "17", "18", "29", "14", "13", "11", "19");
dt.Rows.Add("B", "X01", "8", "7", "3", "26", "17", "6 ", "3 ", "2", "5 ", "10", "8", "14");
dt.Rows.Add("B", "X05", "9", "10", "16", "6", "10 ", "4 ", "13", "12", "6 ", "4", "3", "6");

DataTable dt2 = new DataTable(); //the result

foreach (var r in dt.AsEnumerable())
{
List<int> usage = new List<int>();
foreach (var data in r.ItemArray)
{
var a = data.GetType().ToString();
if (data.GetType().ToString() == "System.Int32")
{
}
}
usage.Sort();
usage.Reverse();
var count = 0;
for (int i = 0; i < 3; i++)
{
count = usage[i] + count;
}
double avg = count*1.00 / 3;