Max function in query by row, not column

[cricketbird]

I have a table that holds the dates of various approvals for documents.
Approval Dates

• DocumentID
• ApproverA_DateApproved
• ApproverB_DateApproved
• ApproverC_DateApproved
• LastUpdated

In a query, I'd like there to be a column with the date of the most recent approval (max date) for each document. Using the following formula in the Expression Builder gives me the error below.

Name:  Max( [Projects]![ApproverA_DateApproved],[Projects]![ApproverB_DateApproved],[Projects]![ApproverC_DateApproved])

"The expression you entered has a function containing the wrong number of arguments"

How can I get the most recent date a document was approved by any approver?

BTW - I initially thought I could just use the date the record was last updated. However, sometimes people remove their approval. This causes the LastUpdated date to be wrong (for this purpose), since it then reflects the date they removed their approval, not the most recent date that someone gave it.

 

[KenHigg]

Seem you could use a couple embedded IIF()s - ?

 

[plog]

You need to revise your structure. When you start to enumerate fields (ApproverA, ApproverB, etc.) it's time for a new table. If you had an approval table that looked like this:

Approvals:
~ [Approval_ID], autonumber primary key of table
~ [ID_Document], numeric foreign key to Documents table
~ [Approval_Person], person approving document (could be foreign key to Employee table)
~ [Approval_Date], date field to hold when this approval occured

If you have that table, generating the last approval for every document is trivial:

SELECT ID_Document, MAX(Approval_Date) AS LastApproval FROM Approvals GROUP BY ID_Document;

Structure your data properly and this is easy and future issues will be avoided completely.

 

[cricketbird]

Thanks Ken and plog. I think I will go with reorganizing my table structure. There are a couple of other issues that would be improved that way as well. Usually I'm pretty good about that, but this one seemed so straightforward with just a few criteria for each document that I opted for the simpler structure. Thank you!

CB