Sum in Query

  • Thread starter Thread starter Lucinda
  • Start date Start date
L

Lucinda

Hello again,

I'm still finding may way here with Access ADP and SQL queries. I was going
along at a clip writing simple queries. My First one was to total Vacation
Balance in 2009:
SELECT LADOLFO.Combination.EMPLID, LADOLFO.Combination.NAME,
LADOLFO.[2009 Vacation Balance].[Vacation Balance]
FROM LADOLFO.[2009 Vacation Balance] CROSS JOIN
LADOLFO.Combination
Worked just fine. My Second was Total all Vacation Earned:
SELECT EMPLID, NAME, SUM([Vacation Earned]) AS Total
FROM LADOLFO.Combination
GROUP BY EMPLID, NAME
Worked great. And My last one Vacation Used:

SELECT EMPLID, NAME,
SUM([Vacation Hours Used 1] + [Vacation Hours Used 2]
+ [Vacation Hours Used 3] + [Vacation Hours Used 4] + [Vacation Hours Used 5]
+ [Vacation Hours Used 6]
+ [Vacation Hours Used 7] + [Vacation Hours Used 8] +
[Vacation Hours Used 9] + [Vacation Hours Used 10]) AS Total
FROM LADOLFO.Combination
GROUP BY EMPLID, NAME

My problem is that I want to combine these three Queries or Views into one.
I want it to kick back:
2009 Vacation Balance + Vacation Earned-Vacation used. This is my attempt
at it:
SELECT dbo.[2009_VAC].[Vacation Balance], dbo.VAC_EARNED.Total,
dbo.[Vacation used].Total AS EXPR1, Sum(dbo.[2009_VAC].[Vacation
Balance]+dbo.VAC_EARNED.Total-dbo.[Vacation used].Total AS EXPR1)AS Vac_Total
FROM dbo.VAC_EARNED INNER JOIN
dbo.[2009_VAC] ON dbo.VAC_EARNED.EMPLID =
dbo.[2009_VAC].EMPLID INNER JOIN
dbo.[Vacation used] ON dbo.VAC_EARNED.EMPLID =
dbo.[Vacation used].EMPLID

when I click on the SQL Check-- it does not work:
I receive an ADO Error: 'SUM' is not a recognized function name. Statement
could not be prepared.

Any help would be appreciated.
 
If you are using some sort of graphical designer for building your
queries -- don't! These graphical designers can only build very simple
statements and will likely fail for something like your current query.

Second, when you see things like [2009 Vacation Balance] and ([Vacation
Hours Used 1] ... ([Vacation Hours Used 10], it make your design very
suspicious about a total lack of normalisation. Before going any further,
you should grab a good book - or at least, any book - on database design
principles and read it.

Third, help yourself and God will help you: drop all these blank spaces and
[] as this will make your much easier to read. The Camel notation (using
EmpId or EmpID instead of EMPID, empid, empID or emp_id or [emp id]) is
considered by many people as the most easier form of reading. In your case,
you are using EmplID instead of EmpID but finishing the first abreviation
with an L is calling for trouble because of the possible confusion between l
and I (L in lower case and i in upper case) and personally, after years of
experience, writing it in full length (EmployeeID) is the best thing to do.

Using aliases for the name of the tables won't hurt you either as well as
not using a reserved word for many SDK such as « Name ».

For the rest, it's impossible for me to understand your queries - and in
particular your use of a CROSS JOIN in the first query because you didn't
explain it - so the only way that I can give you a solution would be to
rewrite these three queries into three subqueries and join them; something
like (untested) :

Select q1.EmpLID, q1.[Name], (q1.[Vacation Balance] + q2.Total - q3.Total)
as Vac_Total
From
(
SELECT LADOLFO.Combination.EMPLID, LADOLFO.Combination.NAME,
LADOLFO.[2009 Vacation Balance].[Vacation Balance]
FROM LADOLFO.[2009 Vacation Balance] CROSS JOIN
LADOLFO.Combination
) as q1

inner join
(
SELECT EMPLID, NAME, SUM([Vacation Earned]) AS Total
FROM LADOLFO.Combination
GROUP BY EMPLID, NAME

) as q2 on q1.EmpLID = q2.EmpLID

inner join
(
SELECT EMPLID, NAME,
SUM([Vacation Hours Used 1] + [Vacation Hours Used 2]
+ [Vacation Hours Used 3] + [Vacation Hours Used 4] + [Vacation Hours Used
5]
+ [Vacation Hours Used 6]
+ [Vacation Hours Used 7] + [Vacation Hours Used 8] +
[Vacation Hours Used 9] + [Vacation Hours Used 10]) AS Total
FROM LADOLFO.Combination
GROUP BY EMPLID, NAME

) as q3 on q1.EmpLID = q3.EmpLID

This is probably totally crappy but without seeing the design of your
database - and without a proper normalization, too - and without
understanding why you are using a Cross Join, it's the best thing that I can
came with.

Finally, don't forget to notice to repeating the value of NAME in the second
and third queries is totally useless here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Lucinda said:
Hello again,

I'm still finding may way here with Access ADP and SQL queries. I was
going
along at a clip writing simple queries. My First one was to total
Vacation
Balance in 2009:
SELECT LADOLFO.Combination.EMPLID, LADOLFO.Combination.NAME,
LADOLFO.[2009 Vacation Balance].[Vacation Balance]
FROM LADOLFO.[2009 Vacation Balance] CROSS JOIN
LADOLFO.Combination
Worked just fine. My Second was Total all Vacation Earned:
SELECT EMPLID, NAME, SUM([Vacation Earned]) AS Total
FROM LADOLFO.Combination
GROUP BY EMPLID, NAME
Worked great. And My last one Vacation Used:

SELECT EMPLID, NAME,
SUM([Vacation Hours Used 1] + [Vacation Hours Used 2]
+ [Vacation Hours Used 3] + [Vacation Hours Used 4] + [Vacation Hours Used
5]
+ [Vacation Hours Used 6]
+ [Vacation Hours Used 7] + [Vacation Hours Used 8]
+
[Vacation Hours Used 9] + [Vacation Hours Used 10]) AS Total
FROM LADOLFO.Combination
GROUP BY EMPLID, NAME

My problem is that I want to combine these three Queries or Views into
one.
I want it to kick back:
2009 Vacation Balance + Vacation Earned-Vacation used. This is my attempt
at it:
SELECT dbo.[2009_VAC].[Vacation Balance], dbo.VAC_EARNED.Total,
dbo.[Vacation used].Total AS EXPR1, Sum(dbo.[2009_VAC].[Vacation
Balance]+dbo.VAC_EARNED.Total-dbo.[Vacation used].Total AS EXPR1)AS
Vac_Total
FROM dbo.VAC_EARNED INNER JOIN
dbo.[2009_VAC] ON dbo.VAC_EARNED.EMPLID =
dbo.[2009_VAC].EMPLID INNER JOIN
dbo.[Vacation used] ON dbo.VAC_EARNED.EMPLID =
dbo.[Vacation used].EMPLID

when I click on the SQL Check-- it does not work:
I receive an ADO Error: 'SUM' is not a recognized function name. Statement
could not be prepared.

Any help would be appreciated.
Click to expand...
 
Back
Top