finding the max of a each 2nd group

Hi

Ill try and explain this.... I need to find the Max(Column 3) for each group in Column 1 for each group in column 2....

lets say i have a table of employess structured like this......[Name], [Month], [Values]

so lets assume these values


Paul | Jan | 9
Paul | Jan | 10
Paul | Feb | 4
Paul | Feb | 13
John | Jan | 1
John | Jan | 7
John | Jan | 4
John | Feb | 13

i want to find the max value in column 3 per person per month

output would be (or variation of )

Paul | Jan | 10
Paul | Feb | 13
John | Jan | 7
John | Feb | 13


I can get the max value for each person, or the max value for each month, but i cannot seem to use the same technique to get the max of each group.... any ideas?

Use Debra Dalgleish's MaxIfs tutorial

http://blog.contextures.com/archives...iple-criteria/

Do you think this method could be recreated using access sql, i found something which is similar but its using a subquery in the WHERE block and its quite slow, not only that i have to perform this operation several times on various columns and join the results at the end in a report

i have a sequence of 5 linked queries which all work together to get the final report, everything is cascaded so i just call the 1 sql statement, but the statement below is very slow and like i said im doing this 3 times for this particular report before compiling everything........

SELECT T1.Inventory_ID, T1.Bank_ID, Max(T1.Front_Gross) AS MaxOfFront_Gross
FROM A1_Banks INNER JOIN Qry_Reports_MaxGross_Stage_0_PreFilter AS T1 ON A1_Banks.ID = T1.Bank_ID
WHERE (((Exists (select 1 from Qry_Reports_MaxGross_Stage_0_PreFilter T2
                  where T1.Inventory_ID = T2.Inventory_ID
                    and T1.Bank_ID = T2.Bank_ID
                    and T1.Front_Gross < T2.Front_Gross))=False))
GROUP BY T1.Inventory_ID, T1.Bank_ID;

the only way i could recreate the sumif example would be either something similar to this or with a join that access (i dont think) will allow, mssql or mysql would handle this very easily but access is a pain in the butt.

any ideas?

mm last post didnt appear.........

i found a solution using a table of variations of the columns i want grouping and then building a table from there with the max values i wanted....


This built the variations list/table

SELECT Qry_Report_PreFilter.Inventory_ID, A1_Banks.ID AS Bank_ID
FROM A1_Banks, Qry_Report_PreFilter
GROUP BY Qry_Report_PreFilter.Inventory_ID, A1_Banks.ID;

This was the start of building up the data....

SELECT Qry_Report_Gross_1.Inventory_ID, Qry_Report_Gross_1.Bank_ID, Max(Qry_Report_PreFilter.Front_Gross) AS MaxOfFront_Gross
FROM Qry_Report_Gross_1 INNER JOIN Qry_Report_PreFilter ON (Qry_Report_Gross_1.Bank_ID = Qry_Report_PreFilter.Bank_ID) AND (Qry_Report_Gross_1.Inventory_ID = Qry_Report_PreFilter.Inventory_ID)
GROUP BY Qry_Report_Gross_1.Inventory_ID, Qry_Report_Gross_1.Bank_ID;

i added several similar queries which gathered various information and brought it all together to find the matching/optimal rows in the main table