If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SELECT function in Query
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! |
#2
|
|||
|
|||
SELECT function in Query
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! . |
#3
|
|||
|
|||
SELECT function in Query
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! . . |
#4
|
|||
|
|||
SELECT function in Query
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. -- Steve Schapel, Microsoft Access MVP alexparks wrote: 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! |
#5
|
|||
|
|||
SELECT function in Query
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 "alexparks" wrote 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! . . |
#6
|
|||
|
|||
SELECT function in Query
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! . . . |
#7
|
|||
|
|||
SELECT function in Query
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 Walter wrote: 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 |
#8
|
|||
|
|||
SELECT function in Query
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 "alexparks" wrote 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! . . . |
#9
|
|||
|
|||
SELECT function in Query
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 Walter wrote: 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 . |
#10
|
|||
|
|||
SELECT function in Query
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! . . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
UK postcode issue | Lapchien | Running & Setting Up Queries | 6 | June 11th, 2004 09:06 PM |
Using Split function in a query. | Randal | Running & Setting Up Queries | 2 | June 11th, 2004 04:22 PM |
query field reference help | -dch | Running & Setting Up Queries | 4 | June 2nd, 2004 07:30 PM |
Using a custom function in a query | BobRoyAce | Running & Setting Up Queries | 5 | May 27th, 2004 07:54 AM |