one more problem...

Hi, I just stumbeled over another problem i hope you can help me with

Having the following tables:

Dept
-------
*Deptname
Owes


Emp
------
*Name
DeptName (foreign key: Dept.DeptName)


Car
------
*CarNr
OutTo (foreign key: Emp.Name)
Cost


The outout I want is:
DeptName, Liability (for now the liability is just the cars cost), Total (what the department owes + liability)
not including the it dept and depts owing less than/equal to 1400


My sql query looks like the following:
SELECT d.DeptName, c.Cost AS Liability, d.Owes + Liability AS Total
FROM Dept d, Emp e, Car c
WHERE d.DeptName = e.DeptName
AND e.Name = c.OutTo
AND d.DeptName <> 'IT'
AND d.Owes >= 1400


The problem is that I get one of the deptnames listed several times, because there's more than one person borrowing a car in that dept.

Do you understand the problem? I'm not sure how to explain it. Any suggestions on the solution?

And this is a problem because?? Include employee name so you get Department/Employee Name for each liability


OR

This for a total sum for each column by deptName

SELECT Dept.Deptname, Sum(Car.Cost) AS LIABILITY, Sum([Dept]![Owes]+[Car]![Cost]) AS Total
FROM (Dept INNER JOIN Emp ON Dept.Deptname = Emp.DeptName) INNER JOIN Car ON Emp.Name = Car.OutTo
GROUP BY Dept.Deptname
HAVING (((Dept.Deptname)<>"it") AND ((First(Dept.Owes))>1400));



S-

I want the infomation pr department. I tried your suggestion but I get the error: Too few parameters. Expected 1.

When I tried this:

SELECT d.DeptName, SUM(c.Cost) AS Liability, SUM(d.Owes + c.Cost) AS Total
FROM Dept d, Emp e, Car c
WHERE d.DeptName = e.DeptName
AND e.Name = c.OutTo
AND d.DeptName <> 'IT'
AND d.Owes >= 1400
GROUP BY Dept.Deptname, c.Cost, d.Owes

The problem is still there.. Any suggestions?

Which DB are you using. The SQL I sent you works great in Access.

S-

Hmm, I use Access as well. I tried the query using Access only and it worked but i still get the error on the server..

I'm learnig JSTL and it's on a tomcat 4.1 server. I don't think there's a problem with the connection because all the other queries I've made works perfectly fine on the same database.. Do you have an idea of what the problem might be?

I discoverd an other problem as well: If the sales department owes 5000 and their liability is 200 your suggestion gives the total of 10200 not 5200..

Solution:

SELECT Dept.Deptname, Sum(Car.Cost) AS LIABILITY, Dep.Owes + Liability AS Total
FROM (Dept INNER JOIN Emp ON Dept.Deptname = Emp.DeptName) INNER JOIN Car ON Emp.Name = Car.OutTo
GROUP BY Dept.Deptname
HAVING (((Dept.Deptname)<>"it") AND ((First(Dept.Owes))>1400));