Hello everyone,

Is it possible to apply the Dmax Function over all subset of records within a field? as the max function does when using the totals option in the query design?

The following is running:

DMax("[Choice]","Net Revenue3"," [FieldAcreID]=1")

The problem is I Have FieldAcreID 1-700 and I would like to apply the DMax to all, not just "[FieldAcreID]=1"

I tried using the Totals option in the query design. Although it works, it hasn't been helpful for me since I would like to include fields in the query that don't have a grouping criteria. This is because I'm trying to extract data from the record which matches the DMax result.

I have also been able to obtain the results I'm asking about using multiple queries. I'm trying to avoid using many queries to help speed things up.

Thank you for any help...

Have you tried:


DMax("[Choice]","Net Revenue3")

Thanks for your reply.

I've tried this but it doesn't do it.

This gives the max value over the entire range of values. I want the max value for each subset of records over the entire range (as in grouping).

For example, each subset has 10 values. I want the max of those ten values for subset 1, 2, 3, etc. not the max value of ALL observations.

Thanks.

You would need criteria then - Something to limit by that "subset" as you call it.

DMax("[Choice]","Net Revenue3", "[FieldMatchingSubset]=" & [FieldInQueryForSubset])

Thank you, this worked!

I ended up writing:

SELECT [NET REVENUE3].FieldAcreID, [NET REVENUE3].Crop, [NET REVENUE3].Choice, DMax("[Choice]","Net Revenue3"," [FieldAcreID]=" & [FieldAcreID]) AS MaxofChoice
FROM [NET REVENUE3];

Notice I don't have different fields in the third argument of DMax as you suggested so I'm not sure if this is exactly what you meant. It DOES give the result I wanted.

I'm disappointed that this slows things down though. I have some alternative queries (with same results) that are running faster.

In case you have more suggestions for me, these are my alternative queries:

First, I obtained the maximum for each "subset" by grouping as follows:

SELECT [NET REVENUE3].FieldAcreID, Max([NET REVENUE3].Choice) AS MaxOfChoice
FROM [NET REVENUE3]
GROUP BY [NET REVENUE3].FieldAcreID;

Second, I ran a query from multiple queries as follows:

SELECT [NET REVENUE3].FieldAcreID, [NET REVENUE3].Crop, [NET REVENUE3].Choice, MaxFiedAcreID.MaxOfChoice
FROM [NET REVENUE3] INNER JOIN MaxFiedAcreID ON [NET REVENUE3].FieldAcreID = MaxFiedAcreID.FieldAcreID
ORDER BY [NET REVENUE3].Index;

I was hoping that by doing it all in one step I would get faster results but it is actually slower!

Thanks again.

Yeah, domain aggregates are actually a slow process and if you can do without them, it is much better.

note that if yuo are trying to use this to get a value for display on a continuous form, then almost certainly you will not get adequate performance. I have tried to achieve this in many different ways, and as soon as the dataset becomes reasonably large, the performance deteriorates. its fine one at a time for a single form, but not very good for a continuous form.

Thank you,

I'm not using forms at all.. but just the multiple queries are slowing things down a bit.