Maximum Salary

[Robert T]

I'm having a hard time constructing what one would think were easy queries.
For example:

I have a table that list hundred of executives and their salaries. I want to
contstruct a query that finds the CEO was the largest salary. I can create a
query that is sorted by salary and makes him first on the list, but I would
much rather return a query only with the name of the CEO with the largest or
smallest salary.

 

[Robert T]

Hi Jerry:

Thank, that worked but I thought I could do that by constructing the QBE
correctly. I guess not.

Robert

 

[Tom Lake]

Hi Jerry:

Thank, that worked but I thought I could do that by constructing the QBE
correctly. I guess not.

Robert

You could make the criteria of the Salary field be the DMax() of Salary.

Tom Lake

 

[Robert T]

Hello Tom:

It took awhile to get the DMax function properly formatted, but eventually I
did and it worked!

However, why does DMAX() work while the Max Function, which I tried before,
doesn't work? The Max Function shows me ever record in the table while DMax
only displayed the CEO with the Maximum Salary [which is of course what I
wanted]. What am I missing?

Thanks,
Robert

 

[Tom Lake]

Hello Tom:

It took awhile to get the DMax function properly formatted, but eventually
I
did and it worked!

It should be something like this:

=DMax("[Salary]", "[CEO Table]")

However, why does DMAX() work while the Max Function, which I tried
before,
doesn't work? The Max Function shows me ever record in the table while
DMax
only displayed the CEO with the Maximum Salary [which is of course what I
wanted]. What am I missing?

I use Access 2007 and I can't even find the Max function in there anymore.

Tom Lake

 

[Gary Walter]

It took awhile to get the DMax function properly formatted, but eventually
I
did and it worked!

However, why does DMAX() work while the Max Function, which I tried
before,
doesn't work? The Max Function shows me ever record in the table while
DMax
only displayed the CEO with the Maximum Salary [which is of course what I
wanted]. What am I missing?

Hi Robert,

Are you talking about the aggregate MAX function?

Field: CEO Salary
Table: yurtable yurtable
Total: GROUP BY MAX
Sort:
Show:
Criteria:
or:

that MAX finds the max salary *over a group*...
and, if constructed as above where each group
is a distinct CEO, then you would get "every record."

With just one column in QBE, you could find MAX Salary
(but not who the CEO is)

Field: Salary
Table: yurtable
Total: MAX
Sort:
Show:
Criteria:
or:

to use "MAX" in that situation (although DMAX is perfectly
valid), you can use it in a subquery in the Criteria under Salary
which performs the same function as DMAX

Field: CEO Salary
Table: yurtable yurtable
Sort:
Show:
Criteria: (SELECT MAX(t.Salary) FROM yurtable AS t)
or:

Apologies for butting in (especially if I misunderstood)

good luck,

gary

 

[Robert T]

Hi Gary:

Please don't apologize for butting in, getting the best information is what
these discussion groups are all about.

I love your input and suggestions and I'm definitely going to try them out
and see how it works.

Thanks so much,
Robert

:

 

[Robert T]

Hi Gary:

(SELECT MAX(t.Salary) FROM yurtable AS t)

It worked like a charm!

Would you be kind enough to explain why there is a t. before the salary
field and then "as T" at the end of the expression?

Thanks,
Robert

:

 

[Gary Walter]

(SELECT MAX(t.Salary) FROM yurtable AS t)

It worked like a charm!

Would you be kind enough to explain why there is a t. before the salary
field and then "as T" at the end of the expression?

Hi Robert,

The "AS t" gives "yurtable" an *alias* in the subquery
so that the parser knows this is a separate
instance of "yurtable" (and won't confuse it
with "yurtable" in main query).

In a correlated subquery, it is possible that the main query
table is referenced in the subquery....

for example....

SELECT
yurtable.JobTitleField,
yurtable.Salary,
(SELECT MAX(t.Salary) FROM yurtable AS t
WHERE
t.JobTitleField = yurtable.JobTitleField) As MaxJobTitleSal
FROM
yurtable;

good luck,

gary

 

[Robert T]

Thanks Gary for your time and explanations.

Hi Robert,

The "AS t" gives "yurtable" an *alias* in the subquery
so that the parser knows this is a separate
instance of "yurtable" (and won't confuse it
with "yurtable" in main query).

In a correlated subquery, it is possible that the main query
table is referenced in the subquery....

for example....

SELECT
yurtable.JobTitleField,
yurtable.Salary,
(SELECT MAX(t.Salary) FROM yurtable AS t
WHERE
t.JobTitleField = yurtable.JobTitleField) As MaxJobTitleSal
FROM
yurtable;

good luck,

gary