[dasReaper]

I'm making a database in Access for a ficticious company for my final project in my Business Database class. The query needs to sum the PayableAmount in the Claims table for each employee then subtract it from the MaxAnnBenAmt in the Plans table thats related to employee. The Employee has a status which corisponds with ClassElidgability for each plan.

The query i have written so far that gives a syntax error is:
SELECT Claims.EmployeeId, SUM((SUM(Claims.PayableAmount)) - (SUM(Plans.MaxAnnBenAmt))
FROM Employees
INNER JOIN Claims on Employees.EmployeeId = Claims.EmployeeId
INNER JOIN Plans on Claims.PlanName = Plans.PlanName
GROUP BY Claims.EmployeeId

Here is the ERD for the database: Just saw the ERD is not up to date. Claims also has EmployeeID as a FK, DateofService, and PlanName as a FK.

[SpacE82]

I think you need to give the sum function/column a name.

So try something like:
SELECT Claims.EmployeeId, (SUM((SUM(Claims.PayableAmount)) - (SUM(Plans.MaxAnnBenAmt))) net_payable

and you might have to add the "net_payable" column to the group by

[Freakazoid]

why are you trying to do a sum on sums? I believe that's your issue.

Why can't you just subtract the 2 sums?

[Freakazoid]

so specifically:
SELECT Claims.EmployeeId, SUM(Claims.PayableAmount) - SUM(Plans.MaxAnnBenAmt)
FROM Employees
INNER JOIN Claims on Employees.EmployeeId = Claims.EmployeeId
INNER JOIN Plans on Claims.PlanName = Plans.PlanName
GROUP BY Claims.EmployeeId

[SpacE82]

but doesnt it still need a name, i think thats where the syntax error is coming in

[Freakazoid]

Originally Posted by SpacE82

but doesnt it still need a name, i think thats where the syntax error is coming in

I'm REALLY rusty on access, but in the 'real' database languages you don't need one (but you should have one).

I also have no idea how the Access handles bad errors, but you can't do sums on sums, so that could be getting read out as a 'syntax' error

[SpacE82]

Originally Posted by Freakazoid

I'm REALLY rusty on access, but in the 'real' database languages you don't need one (but you should have one).

I also have no idea how the Access handles bad errors, but you can't do sums on sums, so that could be getting read out as a 'syntax' error

Cuz when I write query in SQL Server, it will cause a syntax error if there is no name. maybe its different when writing SQL in access

[Freakazoid]

Originally Posted by SpacE82

Cuz when I write query in SQL Server, it will cause a syntax error if there is no name. maybe its different when writing SQL in access

you sure about that? What version database are you using?

I just ran this:
select sum (fe_charmax)+ SUM(fe_data_type) from tblfields group by fe_id

With no problems

[SpacE82]

I'm using SQL Server 2008. I just tried removing the names of a sum function and it didnt run saying "No column name was specified" and when I added the name it ran

[escobar929]

Originally Posted by Freakazoid

so specifically:
SELECT Claims.EmployeeId, SUM(Claims.PayableAmount) - SUM(Plans.MaxAnnBenAmt)
FROM Employees
INNER JOIN Claims on Employees.EmployeeId = Claims.EmployeeId
INNER JOIN Plans on Claims.PlanName = Plans.PlanName
GROUP BY Claims.EmployeeId

havent done SQL in forever but this looks right to me

[Freakazoid]

Originally Posted by SpacE82

I'm using SQL Server 2008. I just tried removing the names of a sum function and it didnt run saying "No column name was specified" and when I added the name it ran

very interesting, I'm running the same as well. Maybe it's a setting somewhere? I'm going to poke around., I've never had to give an alias to an aggregate function before...
You're saying you get an error if you do something as stupid as:
select SUM(1+1) from sys.all_views
?


Well OP, your initial issue is definitely the sums of sums. secondary might be the naming convention. between the 2 you should be golden

[SpacE82]

Originally Posted by Freakazoid

very interesting, I'm running the same as well. Maybe it's a setting somewhere? I'm going to poke around., I've never had to give an alias to an aggregate function before...
You're saying you get an error if you do something as stupid as:
select SUM(1+1) from sys.all_views
?


Well OP, your initial issue is definitely the sums of sums. secondary might be the naming convention. between the 2 you should be golden

Weird, because that works. Maybe it has something to do with joins?

[Freakazoid]

Originally Posted by SpacE82

Weird, because that works. Maybe it has something to do with joins?

I threw in a join for good measure and no problems, what's the query you're trying to run?

[SpacE82]

It was just a sum of two columns. Maybe its something to do with the tables in the databases here at work.

[Freakazoid]

Originally Posted by SpacE82

It was just a sum of two columns. Maybe its something to do with the tables in the databases here at work.

I'm extremely curious now haha. Gotta be something to do with the columns.

You're sure the columns are defined as ints right?

[SpacE82]

Originally Posted by Freakazoid

I'm extremely curious now haha. Gotta be something to do with the columns.

You're sure the columns are defined as ints right?

Haha i know, its weird. I'm pretty sure they are ints, I havent changed their format.

[Freakazoid]

Originally Posted by SpacE82

Haha i know, its weird. I'm pretty sure they are ints, I havent changed their format.

looking online it looks like your error would be happening if you were creating a view, etc. which makes sense. but not for a simple select statement

It has to be a setting somewhere.

regardless it's bad practice to not have an alias

[SpacE82]

yeah, i would put names anyways cuz it makes it much cleaner and easier to work with

[Freakazoid]

Originally Posted by SpacE82

yeah, i would put names anyways cuz it makes it much cleaner and easier to work with

hell in it's current state I don't think it'd even be useable by access (or anything). when it DOES run in sql for me, it has no column name. Thus there's no way to reference it.... I'm hoping the OP already thought about that

[SpacE82]

Originally Posted by Freakazoid

hell in it's current state I don't think it'd even be useable by access (or anything). when it DOES run in sql for me, it has no column name. Thus there's no way to reference it.... I'm hoping the OP already thought about that

Exactly. That's why i put names anyways. I hate having columns with no names.

[dasReaper]

Thanks for the help guys. I finally got it to work.

SQL code was:
SELECT Claims.EmployeeID, Plans.ClassEligibility, SUM((Plans.MaxAnnBenAmt) - (Claims.PayableAmount)) AS Remaining_Benefit
FROM (Employers
INNER JOIN (Employees
INNER JOIN Claims ON Employees.EmployeeID = Claims.EmployeeID) ON Employers.EmployerID = Employees.EmployerID)
INNER JOIN Plans ON (Plans.PlanName = Claims.PlanName) AND (Employers.EmployerID = Plans.EmployerID)
GROUP BY Claims.EmployeeID, Plans.ClassEligibility;