Can I use recordset with Dmax- function?

  • Thread starter Thread starter hanski
  • Start date Start date
H

hanski

hi

I have a little recorset:

name. "select * from dbase1, dbase2 where dbase1.number =
dbase2.number", Conn,adOpenDynamic, adLockOptimistic


I have a field called Salary in dbase2.

Can I use DMax or Max function to find out the highest salary in my
dbase2 where numbers are equal with dbase1?

Hannu
 
Try:

SELECT Max(Salary) AS MaxSalary FROM (SELECT * FROM dbase1 INNER JOIN dbase2
ON dbase1.[number] = dbase2.[number])

The resultant recordset should have 1 row, containing a single field named
MaxSalary.

(Of course, this assumes that only one of the two tables has a field named
Salary in it. If both tables have a Salary field, you'll have to replace the
* with a list of field names, and indicate which salary field you want to
use)
 
Thanks again.


Douglas J Steele kirjoitti:
Try:

SELECT Max(Salary) AS MaxSalary FROM (SELECT * FROM dbase1 INNER JOIN dbase2
ON dbase1.[number] = dbase2.[number])

The resultant recordset should have 1 row, containing a single field named
MaxSalary.

(Of course, this assumes that only one of the two tables has a field named
Salary in it. If both tables have a Salary field, you'll have to replace the
* with a list of field names, and indicate which salary field you want to
use)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


hanski said:
hi

I have a little recorset:

name. "select * from dbase1, dbase2 where dbase1.number =
dbase2.number", Conn,adOpenDynamic, adLockOptimistic


I have a field called Salary in dbase2.

Can I use DMax or Max function to find out the highest salary in my
dbase2 where numbers are equal with dbase1?

Hannu
Click to expand...
Click to expand...
 
Back
Top