MAX Function returns incorrect value

[suemunds]

Hi All,
I'm working on a database to track our program's performance under different grants. Services performed for each grant would be entered on a quarterly basis. I am tracking the number completed for each service during the quarterly period.

I want to create a report that shows progress against benchmarks depending on the most recent quarter completed. Rather than fooling with dates, I put a field in the table where the data is entered for the number of the quarter in which the service was performed. The field, 'Quarter', holds numerical values 1-4 corresponding with the quarter. The table holds entries from different projects, distinguished by the field 'GrantID'.

There will be more than one service entered for each GrantID.

I would like to be able to identify the most recent quarter in which services were performed for each GrantID.

I have tried using the MAX function in a query but it seems to be adding "1" to the highest quarter number in the field for each GrantID. The code is:

SELECT DevEntryQ.GrantID, Max(DevEntryQ.Quarter) AS MaxOfQuarter
FROM DevEntryQ
GROUP BY DevEntryQ.GrantID;

With this query, if the highest quarter number entered in the table for GrantID1 is 3, the query returns "4".

In Excel, I would use an array formula: = {MAX(IF(Table1[GrantID]=GrantID, Table1[Quarter])}.

Any help is appreciated. I want to use the most recent quarter to retrieve that quarter's benchmarks and show the progress toward the benchmark rather than the overall progress toward the goal for that grant and service.

 

[smig]

What is the DevEntryQ for ?

 

[suemunds]

DevEntryQ is the table.

I think I figured it out! The primary key for my Quarters table started autonumbering at 2 instead of 1 and I believe that is the number that was being retrieved by the MAX function.
So I'll delete that as the primary key and just use the quarter numbers instead. That makes more sense anyway.