Max Aggregate incl Primary Key - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Max Aggregate incl Primary Key

Andy Wear

4 years ago

Hi, I have a service table for multiple suppliers. I want to create a query that shows the last service date for each supplier (Max aggregate) and then open that record (using open Args).

Can this be done without getting the 'not included in the aggregate expression...' error?

	Adam Schwanz @Reply 4 years ago
Can you just make a continuous form? Base it off the query and put a button to open the record?

	Andy Wear OP @Reply 4 years ago
Sorry if I've missed something obvious but the query can't include the Record ID (primary key) because it is unique and being used with an aggregate function. Is there a way of including it? Just to clarify, the list only contains the last date for each supplier.

	Adam Schwanz @Reply 4 years ago
You should be able to get the ID with DLookup since you have the maximum date and the supplier (assuming there is only going to be one record per supplier on that date.) Example (could be different depending on field type) `DLOOKUP("ID","Table","Supplier=" & Supplier & " and Date=#" & MaxDate & "#")` Then open the form to that

	Andy Wear OP @Reply 4 years ago
I didn't think of that, thanks I'll give it a try.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View