how to use sql min and division ms access in vb.net

Question

I want to do sql min and then do a division but the error appears "you tried to execute a query that does not include specified expression 'EMPID' as part of an aggregate function"

Thanks

SELECT tblEmployeeCriteria.EMPID,tblEmployee.NAME,
tblEmployeeCriteria.SKILL/MIN(tblEmployeeCriteria.SKILL)*100 
FROM tblEmployeeCriteria 
INNER JOIN tblemployee ON tblEmployeeCriteria.EMPID = tblemployee.EMPID

Database :

tblemployee

EMPID	NAME
10037	Tamendran
10046	Ponisan
10085	Vanu

tblEmployeeCriteria

EMPID	SKILL
10037	90
10046	80
10085	50

Desired Result :

EMPID	NAME	SKILL
10037	Tamendran	180
10046	Ponisan	170
10085	Vanu	100

EXAMPLE VALUE SKILL FROM TAMENDRAN : 90/50*100=180

Answer 1

You use the tblEmployeeCriteria in two separate roles: 1.to get the minimum SKILL value of the entire table and 2.to get the SKILL value for each row and compare with that minimum. You can achieve this by using this table twice; one example is to use a subquery to get the min SKILL value:

SELECT 
   EC.EMPID
 , E.NME
 , EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100 as SKILL
FROM 
  tblEmployeeCriteria EC

  INNER JOIN tblemployee E
  ON EC.EMPID = E.EMPID

You could also JOIN to the subquery but this is a CROSS JOIN MsAccess does not support as a keyword, so it becomes a bit weird (a mixture of modern and out-of-date FROM-JOIN syntax:

SELECT 
   EC.EMPID
 , E.NME
 , EC.SKILL/EC2.MINSKILL*100 as SKILL
FROM 
(  tblEmployeeCriteria EC
  INNER JOIN 
  tblemployee E
  ON EC.EMPID = E.EMPID
),(select MIN(SKILL) as MINSKILL from tblEmployeeCriteria) EC2