Max, DMax etc

[Jack Sheet]

Hi all

I am having difficulty understanding under which circumstances I should be
using Max() as opposed to DMax() functions. Both seem to do the same thing
(find maximum value in a field), but DMax has additional arguments.

What I currently want sounds like neither of these, as I want to query the
maximum of several values in various fields for a specific record, rather
than the maximum of several records in a specific field. Is there a way of
doing this otherwise than by nesting IIF functions several deep?

I keep running into "Microsoft Office Access has encountered an error and
needs to close" error messages, and am coming around to the view that I may
need a UDF. I have never done one in Access before (but did some time ago
in Excel).

Currently I have fields in various tables:
T_Clients.Commencement
T_Clients.Cessation
T_Tasks.StartDate
T_Tasks.EndDate

There is a one (T_Clients.ID_Clients) to many (T_Tasks.ID_Clients)
relationship
T_Clients.ID_Clients and T_Tasks_ID_Tasks are primay keys.

In "pseudocode" my query, probably via a UDF, needs to return:

Min(Max(StartDate,Commencement)+12months,Min(EndDate,(Max(Cessation,StartDate))+9months

Commencement and Cessation may contain Null values, which are then to be
treated as #1900-01-01# for the purposes of any comparisons.

My first attempt at this was to create several queries, each based on the
previous query. Inefficient, perhaps, but I could trace the logic. I
started by creating queries that substituted #1900-01-01# for null values in
the Commencement and Cessation fields.

However when I got about 3 queries deep I ran into the "Access has
encountered an error and needs to
close" error, and no amount of playing around with it stopped that.

I don't know if relevant but I don't *really* have a T_Tasks.StartDate
field. I have a query that calculates the start date from the previous
EndDate, but I did not want to complicate the problem specs. That query
seems to work ok. If I get a solution to the above I hope to jigger it to
fit.

 

[Rick Brandt]

Hi all

I am having difficulty understanding under which circumstances I
should be using Max() as opposed to DMax() functions. Both seem to
do the same thing (find maximum value in a field), but DMax has
additional arguments.

The standard aggregate functions Sum() Min() Max(), etc., are "context
sensitive" as to the domain of records that they will aggregate. This is most
clearly shown in a report. Using Max() in a Report header/footer willl give you
the max value over the entire Resordset that the report uses. Using it in a
group header/footer will give the max value "per each group". If you have
nested group header/footers then the exact same Max() expression will give you a
max across each of the nested domains that each headet/footer section
represents. In a query these functions always work across all of the records
defines by the query's SELECT and WHERE clauses.

Domain aggregates (DSum(), DMin(), DMax(), etc.), are not context sensitive
since their second argument defines what domain they aggregate across. A Domain
function will return the same result regardless of where you place it in an
Access app. Essentially they execute their own internal query, while the
standard aggregate functions are parasites to whatever domain context they are
used in. Because of this Domain Aggregate functions incur much more overhead,
and should be avoided in queries although there are a few query circumstances
where they can be the only practical soluton.

What I currently want sounds like neither of these, as I want to
query the maximum of several values in various fields for a specific
record, rather than the maximum of several records in a specific
field. Is there a way of doing this otherwise than by nesting IIF
functions several deep?

All of the functions described thus far aggregate "across rows". If you need to
aggregate "across columns" this is more difficult and very often is a sign that
the database is not designed properly. You essentiually need an expressson that
compares the field values. The IIf() or Switch() function can be used, but you
most often have to nest them and it can get ugly to figure out. If more than
two or three fields are involved a custom VBA function is likely to be the best
way to handle it because at least then you can build something that has easily
decipherable logic in it.

I keep running into "Microsoft Office Access has encountered an error
and needs to close" error messages, and am coming around to the view
that I may need a UDF. I have never done one in Access before (but
did some time ago in Excel).

Currently I have fields in various tables:
T_Clients.Commencement
T_Clients.Cessation
T_Tasks.StartDate
T_Tasks.EndDate

There is a one (T_Clients.ID_Clients) to many (T_Tasks.ID_Clients)
relationship
T_Clients.ID_Clients and T_Tasks_ID_Tasks are primay keys.

In "pseudocode" my query, probably via a UDF, needs to return:

Min(Max(StartDate,Commencement)+12months,Min(EndDate,(Max(Cessation,StartDate))+9months

I can't really see what the desired goal is here, but this is definitely NOT how
Min() and Max() work. It looks like you want...

Min(arg1, arg2)

....to return the smaller of the two arguments. It won't do that. This
expression would...

IIf(arg1<arg2, arg1, arg2)

 

[BruceM]

Thanks for the thorough and clear explanation of the difference between the
functions.

 

[Jack Sheet]

<Snip - a lot of useful stuff, plus:> I can't really see what the desired
goal is here

Thanks for all of that information. I shall try to set out the desired
goal.
This is to create a select query that displays the earliest due date for
payment of corporation tax by a small UK company, for an accounting period,
according to the rules set out below.

A table T_Clients contains, among other things
A date of commencement of trade, [Commence]
A date of cessation of trade, [Cease]

A table T_Tasks contains, among other things
The date of the end of the accounting period, [EndDate]

A Query Q_Tasks based on T_Tasks contains, among other things
The date of the end of the accounting period, as above [EndDate]
The date of the end of the previous accounting period, derived, [StartDate]

The rules for the first due date of payment of corporation tax for a small
company in the UK, for a given accounting period are fairly straight forward
to understand in Excel-speak, but I am having difficulty raising a Select
query that displays that date in Access-speak. These rules take into
account the possibility of an accounting period not being for precisely 12
months in length, and for the possibility of it commencing to trade or
ceasing to trade within that period. In fact a company could start or cease
to trade several times, which gets more complicated, but I am assuming here
that there is at most one such event within the accounting period, and if
there are no such events then it is assumed to be trading throughout.

If the field names as above were treated as named constants in Excel then
the Excel function to derive the date would be as follows. This formula
makes use of Excel's EDATE() function in the analysis toolpack, where
EDATE(datevalue as date,months as integer) returns a date "months" months
later than "datevalue".

=Edate(Min(Edate(Max(StartDate,Commencement),12),Min(End,Max(Cessation,StartDate))),9)

Expressed in English, the earliest trading period within the accounting
period is identified. If that period is for more than 12 months, then the
due date is 21 months from the commencement of that period. If that period
is for 12 months or less, then the due date is 9 months from the end of that
period.

 

[Jack Sheet]

=Edate(Min(Edate(Max(StartDate,Commencement),12),Min(End,Max(Cessation,StartDate))),9)

Typo, should of course read

=Edate(Min(Edate(Max(StartDate,Commence),12),Min(EndDate,Max(Cease,StartDate))),9)

 

[Jack Sheet]

Typo, should of course read

=Edate(Min(Edate(Max(StartDate,Commence),12),Min(EndDate,Max(Cease,StartDate))),9)

Actually, that's a good point. Can I call an Excel function from within
Access simply to calculate this value and return it in a select query? That
may be the easiest solution if possible.