SELECT function in Query

A

alexparks

I am entering this following command in a query and I am
getting the following warning:

"The syntax of the subquery in this expression in
incorrect. Check the subquery's syntax and enclose the
subquery in parentheses.

I am entering the following expresstion:

SELECT InvoiceID, SUM(HoursWorked*HourlyRate)
FROM tblBilling Invoice-WorkHours
GROUP BY InvoiceID

Can someone help me with the correct syntax?
Thanks!
 
T

Ted Allen

Hi Alex,

The query sql that you posted looks like the sql for a
full query, not a subquery. But, the error message that
you posted makes it sound like you may have posted the
sql as the field source in the query builder. If this is
the case, use the toolbar to switch from design view to
sql view, then delete whatever text you see and paste
your sql syntax there. But, also, it looks like your
table name is "tblBilling Invoice-WorkHours". If so, you
need to enclose it in square brackets such as [tblBilling
Invoice-WorkHours].

Post back if this doesn't resolve your problem.

-Ted Allen
 
A

alexparks

Hey Ted,
I tried what you suggested and it worked great, however it
does not perform the result I need.

I have a query that contains the following fields:

InvoiceID HoursWorked HourlyRate and a field source
containg the expression:
HourlyAmount:[HoursWorked]*[HourlyRate] which returns the
correct value.

What I need this query to do is total the HourlyAmounts per
InvoiceID.

So what I entered in the query is the following:
HourlyTotal: SELECT InvoiceID, SUM(HoursWorked*HourlyRate)
FROM tblBilling Invoice-Work Hours GROUP BY InvoiceID.

I hope this helps. As I stated in a previous newsgroup
message before this one, I am taking Access On-Line
sessions through ElementK to help me build a custom
Database for my company.

Thanks for your suggestions!
-----Original Message-----
Hi Alex,

The query sql that you posted looks like the sql for a
full query, not a subquery. But, the error message that
you posted makes it sound like you may have posted the
sql as the field source in the query builder. If this is
the case, use the toolbar to switch from design view to
sql view, then delete whatever text you see and paste
your sql syntax there. But, also, it looks like your
table name is "tblBilling Invoice-WorkHours". If so, you
need to enclose it in square brackets such as [tblBilling
Invoice-WorkHours].

Post back if this doesn't resolve your problem.

-Ted Allen
-----Original Message-----
I am entering this following command in a query and I am
getting the following warning:

"The syntax of the subquery in this expression in
incorrect. Check the subquery's syntax and enclose the
subquery in parentheses.

I am entering the following expresstion:

SELECT InvoiceID, SUM(HoursWorked*HourlyRate)
FROM tblBilling Invoice-WorkHours
GROUP BY InvoiceID

Can someone help me with the correct syntax?
Thanks!
.
Click to expand...
.
Click to expand...
 
S

Steve Schapel

Alex

Try this...
Get your original query in design view. Remove the HoursWorked and
HourlyRate columns from the query design grid, so you should just have
the InvoiceID and HourlyAmount columns. Make it a Totals Query (select
Totals from the View menu). In the grid, leave Group By in the Totals
row of the InvoiceID column, and put Sum in the Totals row of the
HourlyAmount column.

This will achieve your stated purpose, being "total the HourlyAmounts
per InvoiceID". If this does not meet your needs please post back with
explanation with examples of what you want.
 
G

Gary Walter

Hi Alex,

PMFBI...I am sure Ted and Steve are highly
competent to answer this, but, as I read this
thread, I think you might be confused with
how a subquery works....

You had a query like

SELECT
InvoiceID,
HoursWorked,
HourlyRate,
HoursWorked*HourlyRate As HourlyAmount
FROM
[tblBilling Invoice-Work Hours];

and, I believe, you thought it would be
great to also show in your query the
total hourly amount for each InvoiceID..
so you decided to use a subquery to
compute this in an additional field.

There are several things that are not easy
to understand at first with subqueries....

1) it needs to be enclosed in parentheses
2) it needs to provide one "value" for each
record of the main query
3) if the same source (table/query) is used
in a subquery, you will need to give the table
(in your case) an alias within the subquery

In your case, you will need a "correlated"
subquery. Try to imagine the process slowed
down as the query formulates each "line"
of your query result, record-by-record.

Your query is "saying"

get a record from my table,
return the InvoiceID, HoursWorked, HourlyRate
calculate the product of HoursWorked*HourlyRate
then, also, for this specific InvoiceID, find the total
hourly amount

If you were given a specific InvoiceID (say = xxxx),
you probably would have no trouble returning the
total hourly amount:

SELECT SUM(HoursWorked*HourlyRate)
FROM [tblBilling Invoice-Work Hours]
WHERE InvoiceID = xxxx;

to "correlate" the above query back to the main
query, we will have to give our subquery table an
alias ( say "t"), then in the WHERE clause say we
want our "alias" t.InvoiceID to be equal to the
"main query" [tblBilling Invoice-Work Hours].InvoiceID


So...when you type your subquery in a field row,
enclosing it in parentheses, returning only one value,
aliasing the source because it is used in the main
query, and "correlating" InvoiceID's, it will look like
(all on one line, though this will probably word-wrap):

HourlyTotal: (SELECT SUM(t.HoursWorked*t.HourlyRate)
FROM [tblBilling Invoice-Work Hours] As t
WHERE t.InvoiceID = [tblBilling Invoice-Work Hours].InvoiceID)

If you were to then go into SQL View, it might look like:

SELECT
InvoiceID,
HoursWorked,
HourlyRate,
HoursWorked*HourlyRate As HourlyAmount,
(SELECT
SUM(t.HoursWorked*t.HourlyRate)
FROM
[tblBilling Invoice-Work Hours] As t
WHERE
t.InvoiceID = [tblBilling Invoice-Work Hours].InvoiceID)
As HourlyTotal
FROM
[tblBilling Invoice-Work Hours];

Apologies again for butting in.

Please respond back if I have misunderstood.

Good luck,

Gary Walter






I tried what you suggested and it worked great, however it
does not perform the result I need.

I have a query that contains the following fields:

InvoiceID HoursWorked HourlyRate and a field source
containg the expression:
HourlyAmount:[HoursWorked]*[HourlyRate] which returns the
correct value.

What I need this query to do is total the HourlyAmounts per
InvoiceID.

So what I entered in the query is the following:
HourlyTotal: SELECT InvoiceID, SUM(HoursWorked*HourlyRate)
FROM tblBilling Invoice-Work Hours GROUP BY InvoiceID.

I hope this helps. As I stated in a previous newsgroup
message before this one, I am taking Access On-Line
sessions through ElementK to help me build a custom
Database for my company.

Thanks for your suggestions!
-----Original Message-----
Hi Alex,

The query sql that you posted looks like the sql for a
full query, not a subquery. But, the error message that
you posted makes it sound like you may have posted the
sql as the field source in the query builder. If this is
the case, use the toolbar to switch from design view to
sql view, then delete whatever text you see and paste
your sql syntax there. But, also, it looks like your
table name is "tblBilling Invoice-WorkHours". If so, you
need to enclose it in square brackets such as [tblBilling
Invoice-WorkHours].

Post back if this doesn't resolve your problem.

-Ted Allen
-----Original Message-----
I am entering this following command in a query and I am
getting the following warning:

"The syntax of the subquery in this expression in
incorrect. Check the subquery's syntax and enclose the
subquery in parentheses.

I am entering the following expresstion:

SELECT InvoiceID, SUM(HoursWorked*HourlyRate)
FROM tblBilling Invoice-WorkHours
GROUP BY InvoiceID

Can someone help me with the correct syntax?
Thanks!
.
Click to expand...
.
Click to expand...
Click to expand...
 
T

Ted Allen

Hi Alex,

Looks like my first answer was a little off, and you were
actually intending a subquery. But, Steve's answer was
right on if all you need is the total per invoice id, and
Gary's post was one of the best I have seen in explaining
how to put together subqueries, so I think you will
probably be in very good shape, but post back if you have
any questions.

I will add one thing to Gary's message regarding
subqueries. It doesn't apply in this case, but for
general info there are some cases where you can use them
to return more than one value, but not as a field
source. If using them to set criteria, you can return
more than one value and combine the subquery with the IN
or NOT IN clause. You would still only be selecting one
field in your query though.

HTH, Ted Allen
-----Original Message-----
Hey Ted,
I tried what you suggested and it worked great, however it
does not perform the result I need.

I have a query that contains the following fields:

InvoiceID HoursWorked HourlyRate and a field source
containg the expression:
HourlyAmount:[HoursWorked]*[HourlyRate] which returns the
correct value.

What I need this query to do is total the HourlyAmounts per
InvoiceID.

So what I entered in the query is the following:
HourlyTotal: SELECT InvoiceID, SUM (HoursWorked*HourlyRate)
FROM tblBilling Invoice-Work Hours GROUP BY InvoiceID.

I hope this helps. As I stated in a previous newsgroup
message before this one, I am taking Access On-Line
sessions through ElementK to help me build a custom
Database for my company.

Thanks for your suggestions!
-----Original Message-----
Hi Alex,

The query sql that you posted looks like the sql for a
full query, not a subquery. But, the error message that
you posted makes it sound like you may have posted the
sql as the field source in the query builder. If this is
the case, use the toolbar to switch from design view to
sql view, then delete whatever text you see and paste
your sql syntax there. But, also, it looks like your
table name is "tblBilling Invoice-WorkHours". If so, you
need to enclose it in square brackets such as [tblBilling
Invoice-WorkHours].

Post back if this doesn't resolve your problem.

-Ted Allen
-----Original Message-----
I am entering this following command in a query and I am
getting the following warning:

"The syntax of the subquery in this expression in
incorrect. Check the subquery's syntax and enclose the
subquery in parentheses.

I am entering the following expresstion:

SELECT InvoiceID, SUM(HoursWorked*HourlyRate)
FROM tblBilling Invoice-WorkHours
GROUP BY InvoiceID

Can someone help me with the correct syntax?
Thanks!
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
S

Steve Schapel

Beautifully done, Gary! :)

I was not sure whether Alex was trying to do something very simple in an
unnecessarily complicated way. But either way, your post is a superb
explanation.

--
Steve Schapel, Microsoft Access MVP


Gary said:
Hi Alex,

PMFBI...I am sure Ted and Steve are highly
competent to answer this, but, as I read this
thread, I think you might be confused with
how a subquery works....

You had a query like

SELECT
InvoiceID,
HoursWorked,
HourlyRate,
HoursWorked*HourlyRate As HourlyAmount
FROM
[tblBilling Invoice-Work Hours];

and, I believe, you thought it would be
great to also show in your query the
total hourly amount for each InvoiceID..
so you decided to use a subquery to
compute this in an additional field.

There are several things that are not easy
to understand at first with subqueries....

1) it needs to be enclosed in parentheses
2) it needs to provide one "value" for each
record of the main query
3) if the same source (table/query) is used
in a subquery, you will need to give the table
(in your case) an alias within the subquery

In your case, you will need a "correlated"
subquery. Try to imagine the process slowed
down as the query formulates each "line"
of your query result, record-by-record.

Your query is "saying"

get a record from my table,
return the InvoiceID, HoursWorked, HourlyRate
calculate the product of HoursWorked*HourlyRate
then, also, for this specific InvoiceID, find the total
hourly amount

If you were given a specific InvoiceID (say = xxxx),
you probably would have no trouble returning the
total hourly amount:

SELECT SUM(HoursWorked*HourlyRate)
FROM [tblBilling Invoice-Work Hours]
WHERE InvoiceID = xxxx;

to "correlate" the above query back to the main
query, we will have to give our subquery table an
alias ( say "t"), then in the WHERE clause say we
want our "alias" t.InvoiceID to be equal to the
"main query" [tblBilling Invoice-Work Hours].InvoiceID


So...when you type your subquery in a field row,
enclosing it in parentheses, returning only one value,
aliasing the source because it is used in the main
query, and "correlating" InvoiceID's, it will look like
(all on one line, though this will probably word-wrap):

HourlyTotal: (SELECT SUM(t.HoursWorked*t.HourlyRate)
FROM [tblBilling Invoice-Work Hours] As t
WHERE t.InvoiceID = [tblBilling Invoice-Work Hours].InvoiceID)

If you were to then go into SQL View, it might look like:

SELECT
InvoiceID,
HoursWorked,
HourlyRate,
HoursWorked*HourlyRate As HourlyAmount,
(SELECT
SUM(t.HoursWorked*t.HourlyRate)
FROM
[tblBilling Invoice-Work Hours] As t
WHERE
t.InvoiceID = [tblBilling Invoice-Work Hours].InvoiceID)
As HourlyTotal
FROM
[tblBilling Invoice-Work Hours];

Apologies again for butting in.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
 
A

alexparks

Gary,

Thanks very much for your excellent reply and instructions
to help with my obsurd question. I had no idea whether I
was trying to do something simple or whether it was even
possible with my novice database skills. All I knew is
what result I needed. I will attemp to perform your
suggested set of instructions. Thanks for sharing your
advanced skill and knowledge with a novice!

Alexandra Parks

-----Original Message-----
Hi Alex,

PMFBI...I am sure Ted and Steve are highly
competent to answer this, but, as I read this
thread, I think you might be confused with
how a subquery works....

You had a query like

SELECT
InvoiceID,
HoursWorked,
HourlyRate,
HoursWorked*HourlyRate As HourlyAmount
FROM
[tblBilling Invoice-Work Hours];

and, I believe, you thought it would be
great to also show in your query the
total hourly amount for each InvoiceID..
so you decided to use a subquery to
compute this in an additional field.

There are several things that are not easy
to understand at first with subqueries....

1) it needs to be enclosed in parentheses
2) it needs to provide one "value" for each
record of the main query
3) if the same source (table/query) is used
in a subquery, you will need to give the table
(in your case) an alias within the subquery

In your case, you will need a "correlated"
subquery. Try to imagine the process slowed
down as the query formulates each "line"
of your query result, record-by-record.

Your query is "saying"

get a record from my table,
return the InvoiceID, HoursWorked, HourlyRate
calculate the product of HoursWorked*HourlyRate
then, also, for this specific InvoiceID, find the total
hourly amount

If you were given a specific InvoiceID (say = xxxx),
you probably would have no trouble returning the
total hourly amount:

SELECT SUM(HoursWorked*HourlyRate)
FROM [tblBilling Invoice-Work Hours]
WHERE InvoiceID = xxxx;

to "correlate" the above query back to the main
query, we will have to give our subquery table an
alias ( say "t"), then in the WHERE clause say we
want our "alias" t.InvoiceID to be equal to the
"main query" [tblBilling Invoice-Work Hours].InvoiceID


So...when you type your subquery in a field row,
enclosing it in parentheses, returning only one value,
aliasing the source because it is used in the main
query, and "correlating" InvoiceID's, it will look like
(all on one line, though this will probably word-wrap):

HourlyTotal: (SELECT SUM(t.HoursWorked*t.HourlyRate)
FROM [tblBilling Invoice-Work Hours] As t
WHERE t.InvoiceID = [tblBilling Invoice-Work Hours].InvoiceID)

If you were to then go into SQL View, it might look like:

SELECT
InvoiceID,
HoursWorked,
HourlyRate,
HoursWorked*HourlyRate As HourlyAmount,
(SELECT
SUM(t.HoursWorked*t.HourlyRate)
FROM
[tblBilling Invoice-Work Hours] As t
WHERE
t.InvoiceID = [tblBilling Invoice-Work Hours].InvoiceID)
As HourlyTotal
FROM
[tblBilling Invoice-Work Hours];

Apologies again for butting in.

Please respond back if I have misunderstood.

Good luck,

Gary Walter






I tried what you suggested and it worked great, however it
does not perform the result I need.

I have a query that contains the following fields:

InvoiceID HoursWorked HourlyRate and a field source
containg the expression:
HourlyAmount:[HoursWorked]*[HourlyRate] which returns the
correct value.

What I need this query to do is total the HourlyAmounts per
InvoiceID.

So what I entered in the query is the following:
HourlyTotal: SELECT InvoiceID, SUM (HoursWorked*HourlyRate)
FROM tblBilling Invoice-Work Hours GROUP BY InvoiceID.

I hope this helps. As I stated in a previous newsgroup
message before this one, I am taking Access On-Line
sessions through ElementK to help me build a custom
Database for my company.

Thanks for your suggestions!
-----Original Message-----
Hi Alex,

The query sql that you posted looks like the sql for a
full query, not a subquery. But, the error message that
you posted makes it sound like you may have posted the
sql as the field source in the query builder. If this is
the case, use the toolbar to switch from design view to
sql view, then delete whatever text you see and paste
your sql syntax there. But, also, it looks like your
table name is "tblBilling Invoice-WorkHours". If so, you
need to enclose it in square brackets such as [tblBilling
Invoice-WorkHours].

Post back if this doesn't resolve your problem.

-Ted Allen
-----Original Message-----
I am entering this following command in a query and I am
getting the following warning:

"The syntax of the subquery in this expression in
incorrect. Check the subquery's syntax and enclose the
subquery in parentheses.

I am entering the following expresstion:

SELECT InvoiceID, SUM(HoursWorked*HourlyRate)
FROM tblBilling Invoice-WorkHours
GROUP BY InvoiceID

Can someone help me with the correct syntax?
Thanks!
.

.
Click to expand...
Click to expand...


.
Click to expand...
 
A

alexparks

Steve,

Thanks for your help. I will attemp to follow Gary's
set of instructions. As an IT Professional, I would not
be able to solve some of my daily challenges without
MS Newsgroups! Thanks again.

Alexandra Parks

-----Original Message-----
Beautifully done, Gary! :)

I was not sure whether Alex was trying to do something very simple in an
unnecessarily complicated way. But either way, your post is a superb
explanation.

--
Steve Schapel, Microsoft Access MVP


Gary said:
Hi Alex,

PMFBI...I am sure Ted and Steve are highly
competent to answer this, but, as I read this
thread, I think you might be confused with
how a subquery works....

You had a query like

SELECT
InvoiceID,
HoursWorked,
HourlyRate,
HoursWorked*HourlyRate As HourlyAmount
FROM
[tblBilling Invoice-Work Hours];

and, I believe, you thought it would be
great to also show in your query the
total hourly amount for each InvoiceID..
so you decided to use a subquery to
compute this in an additional field.

There are several things that are not easy
to understand at first with subqueries....

1) it needs to be enclosed in parentheses
2) it needs to provide one "value" for each
record of the main query
3) if the same source (table/query) is used
in a subquery, you will need to give the table
(in your case) an alias within the subquery

In your case, you will need a "correlated"
subquery. Try to imagine the process slowed
down as the query formulates each "line"
of your query result, record-by-record.

Your query is "saying"

get a record from my table,
return the InvoiceID, HoursWorked, HourlyRate
calculate the product of HoursWorked*HourlyRate
then, also, for this specific InvoiceID, find the total
hourly amount

If you were given a specific InvoiceID (say = xxxx),
you probably would have no trouble returning the
total hourly amount:

SELECT SUM(HoursWorked*HourlyRate)
FROM [tblBilling Invoice-Work Hours]
WHERE InvoiceID = xxxx;

to "correlate" the above query back to the main
query, we will have to give our subquery table an
alias ( say "t"), then in the WHERE clause say we
want our "alias" t.InvoiceID to be equal to the
"main query" [tblBilling Invoice-Work Hours].InvoiceID


So...when you type your subquery in a field row,
enclosing it in parentheses, returning only one value,
aliasing the source because it is used in the main
query, and "correlating" InvoiceID's, it will look like
(all on one line, though this will probably word-wrap):

HourlyTotal: (SELECT SUM(t.HoursWorked*t.HourlyRate)
FROM [tblBilling Invoice-Work Hours] As t
WHERE t.InvoiceID = [tblBilling Invoice-Work Hours].InvoiceID)

If you were to then go into SQL View, it might look like:

SELECT
InvoiceID,
HoursWorked,
HourlyRate,
HoursWorked*HourlyRate As HourlyAmount,
(SELECT
SUM(t.HoursWorked*t.HourlyRate)
FROM
[tblBilling Invoice-Work Hours] As t
WHERE
t.InvoiceID = [tblBilling Invoice-Work Hours].InvoiceID)
As HourlyTotal
FROM
[tblBilling Invoice-Work Hours];

Apologies again for butting in.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
.
Click to expand...
 
A

alexparks

Ted,

Thanks for your help. I will attemp Gary's excellent,
detailed instructions. I will definitly post back!

thanks again for your expertise advise!

Alexandra Parks

-----Original Message-----
Hi Alex,

Looks like my first answer was a little off, and you were
actually intending a subquery. But, Steve's answer was
right on if all you need is the total per invoice id, and
Gary's post was one of the best I have seen in explaining
how to put together subqueries, so I think you will
probably be in very good shape, but post back if you have
any questions.

I will add one thing to Gary's message regarding
subqueries. It doesn't apply in this case, but for
general info there are some cases where you can use them
to return more than one value, but not as a field
source. If using them to set criteria, you can return
more than one value and combine the subquery with the IN
or NOT IN clause. You would still only be selecting one
field in your query though.

HTH, Ted Allen
-----Original Message-----
Hey Ted,
I tried what you suggested and it worked great, however it
does not perform the result I need.

I have a query that contains the following fields:

InvoiceID HoursWorked HourlyRate and a field source
containg the expression:
HourlyAmount:[HoursWorked]*[HourlyRate] which returns the
correct value.

What I need this query to do is total the HourlyAmounts per
InvoiceID.

So what I entered in the query is the following:
HourlyTotal: SELECT InvoiceID, SUM (HoursWorked*HourlyRate)
FROM tblBilling Invoice-Work Hours GROUP BY InvoiceID.

I hope this helps. As I stated in a previous newsgroup
message before this one, I am taking Access On-Line
sessions through ElementK to help me build a custom
Database for my company.

Thanks for your suggestions!
-----Original Message-----
Hi Alex,

The query sql that you posted looks like the sql for a
full query, not a subquery. But, the error message that
you posted makes it sound like you may have posted the
sql as the field source in the query builder. If this is
the case, use the toolbar to switch from design view to
sql view, then delete whatever text you see and paste
your sql syntax there. But, also, it looks like your
table name is "tblBilling Invoice-WorkHours". If so, you
need to enclose it in square brackets such as [tblBilling
Invoice-WorkHours].

Post back if this doesn't resolve your problem.

-Ted Allen
-----Original Message-----
I am entering this following command in a query and I am
getting the following warning:

"The syntax of the subquery in this expression in
incorrect. Check the subquery's syntax and enclose the
subquery in parentheses.

I am entering the following expresstion:

SELECT InvoiceID, SUM(HoursWorked*HourlyRate)
FROM tblBilling Invoice-WorkHours
GROUP BY InvoiceID

Can someone help me with the correct syntax?
Thanks!
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Aggregate Function and Subqueries 3
Update query syntax: SUM and GROUP BY 3
please check the syntax of this query 1
Select Case Syntax 2
Query By Form, Master, SubForm, SubSubForm 1
syntax error in error of Total of field 9
Select Subquery Error 3
Select query works until I put in criteria 1

Top