Help with SUM syntax

[Guest]

In executing the following query:

SELECT CurrentData.[VacRate], EmpData.[HireDate], SUM(TimeOff.[Hours]) AS
HoursTaken
FROM CurrentData, EmpData, TimeOff
WHERE CurrentData.[FileNum] = filenum* AND EmpData.[FileNum] = filenum* AND
TimeOff.[FileNum] = filenum*

I get the following message:
"You tried to execute a query that does not include the specified expression
'VacRate' as part of an aggregate function".

I don't want 'VacRate' in the SUM function, only all the Hours for a given
employee.

Thanks in advance.

 

[Guest]

At the minimum you need a Group By clause; however, there's more problems
than that.

What is filenum* ? Hopefully it's a primary key in at least one table and
identifies a particular employee.

What are the primary and foriegn keys in the three tables and how are they
joined?

 

[Guest]

Clay,

Don't know where you got the syntax in your WHERE clause, but that aint
gonna work. Personally, I'm surprised you didn't get an invalid syntax
error. I'm also a little confused with what you are trying to accomplish
with this query. If you post back with your table structure and a
description of what you are trying to accomplish, we might be able to help
you more.

At a minimum, I think you need to do the following:

If FileNum is the field that is common across these fields, then you need to
create joins between these fields in the query grid.

Then, you need to use a Group By clause (click the symbol that looks like a
M turned on its left side to create an aggregate query) if you expect to use
any of the aggregate functions (SUM, COUNT, ...) in your query. Once you
have done that, Select "GroupBy" in the totals of the query grid for the
VacRate, and HireDate fields and SUM in the Totals row for the Hours field.

HTH
Dale

 

[Guest]

I'm so sorry - the * was supposed to indicate a "footnote" which I forgot to
include.

filenum* = a variable from VBA code. It is a primary key in EmpData and a
foreign key in both CurrentData and TimeOff. It forms the relationship
between EmpData and the other tables.

The string construction looks like this:

sSQL = "SELECT CurrentData.[VacRate], EmpData.[HireDate],
SUM(TimeOff.[Hours])" & _
" FROM CurrentData, EmpData, TimeOff" & _
" WHERE TimeOff.FileNum = '" & filenum & "' AND CurrentData.FileNum =
'" & filenum & _
"' AND EmpData.FileNum = '" & filenum & "'"
--
Adios,
Clay Harryman

At the minimum you need a Group By clause; however, there's more problems
than that.

What is filenum* ? Hopefully it's a primary key in at least one table and
identifies a particular employee.

What are the primary and foriegn keys in the three tables and how are they
joined?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In executing the following query:

SELECT CurrentData.[VacRate], EmpData.[HireDate], SUM(TimeOff.[Hours]) AS
HoursTaken
FROM CurrentData, EmpData, TimeOff
WHERE CurrentData.[FileNum] = filenum* AND EmpData.[FileNum] = filenum* AND
TimeOff.[FileNum] = filenum*

I get the following message:
"You tried to execute a query that does not include the specified expression
'VacRate' as part of an aggregate function".

I don't want 'VacRate' in the SUM function, only all the Hours for a given
employee.

Thanks in advance.

 

[Guest]

Sorry 'bout the syntax issue. I forgot to clear that up in my OP.

I would like to know how many hours of vacation is taken by the employee
(filenum) in question. See my other post for details about filenum.

Joins - I'm not sure I have those set up. I have actually created and
populated this database completely using ADO through Excel (my employer is
not going to authorize my using Access for this project...).

I'm pulling from my studies of SQL in the early 90's to make this work with
the tools I've been given. Yes, I'm being paid by the hour.
--
Adios,
Clay Harryman

Clay,

Don't know where you got the syntax in your WHERE clause, but that aint
gonna work. Personally, I'm surprised you didn't get an invalid syntax
error. I'm also a little confused with what you are trying to accomplish
with this query. If you post back with your table structure and a
description of what you are trying to accomplish, we might be able to help
you more.

At a minimum, I think you need to do the following:

If FileNum is the field that is common across these fields, then you need to
create joins between these fields in the query grid.

Then, you need to use a Group By clause (click the symbol that looks like a
M turned on its left side to create an aggregate query) if you expect to use
any of the aggregate functions (SUM, COUNT, ...) in your query. Once you
have done that, Select "GroupBy" in the totals of the query grid for the
VacRate, and HireDate fields and SUM in the Totals row for the Hours field.

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.

In executing the following query:

SELECT CurrentData.[VacRate], EmpData.[HireDate], SUM(TimeOff.[Hours]) AS
HoursTaken
FROM CurrentData, EmpData, TimeOff
WHERE CurrentData.[FileNum] = filenum* AND EmpData.[FileNum] = filenum* AND
TimeOff.[FileNum] = filenum*

I get the following message:
"You tried to execute a query that does not include the specified expression
'VacRate' as part of an aggregate function".

I don't want 'VacRate' in the SUM function, only all the Hours for a given
employee.

Thanks in advance.

 

[Guest]

Run the following in a new query using the SQL design view. See if it returns
the proper records.

SELECT EmpData.[FileNum],
CurrentData.[VacRate],
EmpData.[HireDate],
TimeOff.[Hours])
FROM CurrentData,
EmpData,
TimeOff
WHERE EmpData.[FileNum] = CurrentData.[FileNum]
AND EmpData.[FileNum] = TimeOff.[FileNum]
AND EmpData.[FileNum] = [Enter the Employee FileNum]
ORDER BY EmpData.[FileNum] ;

Next while in Query Design view, go up to View, Totals on the Menu. Make
sure that the first three columns say "Group By" and set TimeOff.Hours to Sum.

Run again.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm so sorry - the * was supposed to indicate a "footnote" which I forgot to
include.

filenum* = a variable from VBA code. It is a primary key in EmpData and a
foreign key in both CurrentData and TimeOff. It forms the relationship
between EmpData and the other tables.

The string construction looks like this:

sSQL = "SELECT CurrentData.[VacRate], EmpData.[HireDate],
SUM(TimeOff.[Hours])" & _
" FROM CurrentData, EmpData, TimeOff" & _
" WHERE TimeOff.FileNum = '" & filenum & "' AND CurrentData.FileNum =
'" & filenum & _
"' AND EmpData.FileNum = '" & filenum & "'"
--
Adios,
Clay Harryman

At the minimum you need a Group By clause; however, there's more problems
than that.

What is filenum* ? Hopefully it's a primary key in at least one table and
identifies a particular employee.

What are the primary and foriegn keys in the three tables and how are they
joined?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In executing the following query:

SELECT CurrentData.[VacRate], EmpData.[HireDate], SUM(TimeOff.[Hours]) AS
HoursTaken
FROM CurrentData, EmpData, TimeOff
WHERE CurrentData.[FileNum] = filenum* AND EmpData.[FileNum] = filenum* AND
TimeOff.[FileNum] = filenum*

I get the following message:
"You tried to execute a query that does not include the specified expression
'VacRate' as part of an aggregate function".

I don't want 'VacRate' in the SUM function, only all the Hours for a given
employee.

Thanks in advance.

 

[Guest]

You can't execute a select SQL (Select ,,,, From TableNAme), a select SQL can
be execued through a query only, execute can be used on (update, delete ,
insert SQL)

Start with the basics
Create a query that join all three tables by the FileNum field, and then
change it to a GroupBy query with sum on the Hours fields.
When that accomplished and all the records are displayed you can move to the
next stage of creating the filter

In a module create a GlobalVariable
Global filenum as string

Create a Function that return that value

Function Getfilenum ()
Getfilenum = filenum
End Function

In the query add the filter
Where filenum = Getfilenum ()

Before you open the query assign the value to the global variable created
Getfilenum = "SomeValue"
Docmd.OpenQuery "QueryName"

--
Good Luck
BS"D

I'm so sorry - the * was supposed to indicate a "footnote" which I forgot to
include.

filenum* = a variable from VBA code. It is a primary key in EmpData and a
foreign key in both CurrentData and TimeOff. It forms the relationship
between EmpData and the other tables.

The string construction looks like this:

sSQL = "SELECT CurrentData.[VacRate], EmpData.[HireDate],
SUM(TimeOff.[Hours])" & _
" FROM CurrentData, EmpData, TimeOff" & _
" WHERE TimeOff.FileNum = '" & filenum & "' AND CurrentData.FileNum =
'" & filenum & _
"' AND EmpData.FileNum = '" & filenum & "'"
--
Adios,
Clay Harryman

At the minimum you need a Group By clause; however, there's more problems
than that.

What is filenum* ? Hopefully it's a primary key in at least one table and
identifies a particular employee.

What are the primary and foriegn keys in the three tables and how are they
joined?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

In executing the following query:

SELECT CurrentData.[VacRate], EmpData.[HireDate], SUM(TimeOff.[Hours]) AS
HoursTaken
FROM CurrentData, EmpData, TimeOff
WHERE CurrentData.[FileNum] = filenum* AND EmpData.[FileNum] = filenum* AND
TimeOff.[FileNum] = filenum*

I get the following message:
"You tried to execute a query that does not include the specified expression
'VacRate' as part of an aggregate function".

I don't want 'VacRate' in the SUM function, only all the Hours for a given
employee.

Thanks in advance.

 

[John W. Vinson]

I get the following message:
"You tried to execute a query that does not include the specified expression
'VacRate' as part of an aggregate function".

I don't want 'VacRate' in the SUM function, only all the Hours for a given
employee.

What's the context? Is filenum a vba variable, the name of a form control, or
what? What do you intend the asterisks to do?

If you're searching for a text string, that text string must be delimited by
either " or ' quotemarks. Also, you're doing something VERY odd by including
three tables in a query, with no join, and applying a criterion (the same
crieterion yet) to all three tables. How are the tables related? What is the
Primary Key of each table? I strongly suspect you're not quite done
normalizing your table structure!

John W. Vinson [MVP]

 

[Guest]

Thank you. I'd forgotten how complex SQL could really be.

The query with three tables worked fine without the SUM.

The solution I decided to implement was simpler than what's presented here:
I'll select the other fields in one query and the SUM in another query.
That's working quite well.

I do appreciate all the help. I need to go back to school. A lot's changed
since 1994...
--
Adios,
Clay Harryman

Run the following in a new query using the SQL design view. See if it returns
the proper records.

SELECT EmpData.[FileNum],
CurrentData.[VacRate],
EmpData.[HireDate],
TimeOff.[Hours])
FROM CurrentData,
EmpData,
TimeOff
WHERE EmpData.[FileNum] = CurrentData.[FileNum]
AND EmpData.[FileNum] = TimeOff.[FileNum]
AND EmpData.[FileNum] = [Enter the Employee FileNum]
ORDER BY EmpData.[FileNum] ;

Next while in Query Design view, go up to View, Totals on the Menu. Make
sure that the first three columns say "Group By" and set TimeOff.Hours to Sum.

Run again.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm so sorry - the * was supposed to indicate a "footnote" which I forgot to
include.

filenum* = a variable from VBA code. It is a primary key in EmpData and a
foreign key in both CurrentData and TimeOff. It forms the relationship
between EmpData and the other tables.

The string construction looks like this:

sSQL = "SELECT CurrentData.[VacRate], EmpData.[HireDate],
SUM(TimeOff.[Hours])" & _
" FROM CurrentData, EmpData, TimeOff" & _
" WHERE TimeOff.FileNum = '" & filenum & "' AND CurrentData.FileNum =
'" & filenum & _
"' AND EmpData.FileNum = '" & filenum & "'"
--
Adios,
Clay Harryman

At the minimum you need a Group By clause; however, there's more problems
than that.

What is filenum* ? Hopefully it's a primary key in at least one table and
identifies a particular employee.

What are the primary and foriegn keys in the three tables and how are they
joined?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

In executing the following query:

SELECT CurrentData.[VacRate], EmpData.[HireDate], SUM(TimeOff.[Hours]) AS
HoursTaken
FROM CurrentData, EmpData, TimeOff
WHERE CurrentData.[FileNum] = filenum* AND EmpData.[FileNum] = filenum* AND
TimeOff.[FileNum] = filenum*

I get the following message:
"You tried to execute a query that does not include the specified expression
'VacRate' as part of an aggregate function".

I don't want 'VacRate' in the SUM function, only all the Hours for a given
employee.

Thanks in advance.