Trouble with Fetching Data for Report

[mjseim]

I've tried and tried and tried to get a DLookUp to solve this problem but Im having no luck. I'm thinking a query in the record source or a cleaver filter of my subreport may solve it but I wouldn't know how... here's the problem.

I have a report based off a table called ASSOCIATIONS.
The ASSOCIATIONS table has a reference to another table called PHASES since there is is more than one phase per association.

These tables are linked based upon a field called "HOAID"
PHASES has two fields called "Phase" and "Date"

In my report I need to be able to retrieve the maximum "Phase" from PHASES and the "Date" associated with that "Phase".

-----------

My attempt at a solution was a subreport based off of the "HOAID" link. Then, I had an =MAX([Phase]) equation in the report footer of the subreport. I was unable to get the "Date" field after that.


If anyone has a way of getting this accomplished I'd sure appreciate it.

Take care.

 

[mjseim]

Here's another idea... since I always want the max phase perhaps a descending order on my form and a filter to only show the first record would work.

I tried created a control =1 with running sum and then applying a filter to the form to only show that field when it's equal to 1 but that didn't work.

 

[mjseim]

Aggregate Queries keep coming up in my searches but I'm having no luck there either!

Must this really be so darn difficult to report only ONE RECORD BASED OFF OF A MAXIMUM IN A SUBREPORT! Arrggg.