sub query

[inungh]

I have s ub query in my calculation field like following

Select MyMainField /6 * (Select SubField from MySubTable where SubID
=1) from MyMainTable

I got syntax error for the query.

I beleive that MS Access support sub query in the calculations.

Anything I am missing here?

Your help is great appreciated,

 

[pietlinden]

I have s ub query in my calculation field like following

Select MyMainField /6 * (Select SubField from MySubTable where SubID
=1) from MyMainTable

I got syntax error for the query.

I beleive that MS Access support sub query in the calculations.

Anything I am missing here?

Your help is great appreciated,

No, not like that. You need a join, because the subquery can return
more than one record.

 

[Bob Barrows [MVP]]

No, not like that. You need a join, because the subquery can return
more than one record.

More correct:
You need a join, _if_ the subquery can return more than one record.

If that was the problem, the error would not be a "syntax error": the
error would say something more along the line that the subquery is not
allowed because it could return more than one record. I would probably
guarantee the subquery returns a single record anyways by using an
aggregate function:

Select Max(SubField) MySubTable where SubID=1


Anyways, what I see there looks correct, syntax-wise. Are you showing us
your actual sql? When you remove this subquery from the calculation does
the error go away?

 

[John Spencer]

Unfortunately, Access doesn't care if the query actually returns 1 or more
records. It errors based on the fact that the query CAN possibly return more
than one record.

You can get around that by using an aggregate query. The following should work.

Select MyMainField/6*(Select Max(SubField) FROM MySubTable where SubID=1)
FROM MyMainTable


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows [MVP]]

Unfortunately, Access doesn't care if the query actually returns 1 or
more records. It errors based on the fact that the query CAN
possibly return more than one record.

You can get around that by using an aggregate query. The following
should work.

Select MyMainField/6*(Select Max(SubField) FROM MySubTable where
SubID=1)
FROM MyMainTable

That's what I said, isn't it? But again, I think the OP is having a
different problem. The symptom is "syntax error", not "subquery not
allowed in this context"

 

[John Spencer]

Sorry, somehow I misread your posting.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[inungh]

Sorry, somehow I misread your posting.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================





- Show quoted text -

Thanks for helping,
My original query is very long.

It works if I have constant like 35 or 10.
It failed when I have sub query and got syntax error message.
I added max, but still get same error.

Thanks millions,

Here is my original query.

SELECT tblPIPEmployee_2.First_Name+" "+tblPIPEmployee_2.Last_Name AS
Supervisors, tblPIPEmployee_1.First_Name+"
"+tblPIPEmployee_1.Last_Name AS EDA, [SP GET CSR CSI EVALUATE].
[MaxOfRateNumber]/6*(select max(CSIWeight) from tblSystem where
systemId = 1)+[SP GET CSR SO EVALUATE].[MaxOfRateNumber]/6*0.35+[SP
GET IKB EVALUATE RATE RESULT].[MaxOfRateNumber]/6*0.15+[SP GET CSR AHT
EVALUATE].[minofRateNumber]/6*0.15+IIf([RFA RATE] Is Null,0.05,[SP GET
CSR RFA ACD EVALUATE].[RFA RATE]/6*0.05)+[SP GET CSR AVAIL EVALUATE].
[MaxofRateNumber]/6*0.15 AS [FINAL RESULT], tblPIPEmployee.First_Name
+" "+tblPIPEmployee.Last_Name AS Employee, tblPIPEmployee.EmployeeNO,
[SP GET IKB EVALUATE RATE RESULT].result AS IKB, [SP GET IKB EVALUATE
RATE RESULT].MaxOfRateNumber AS [IKB RATE], [SP GET IKB EVALUATE RATE
RESULT].MaxOfRateNumber/6*0.15 AS [IKB RESULT], [SP GET CSR SO
EVALUATE].SO, [SP GET CSR SO EVALUATE].MaxOfRateNumber AS [SO RATE],
[SP GET CSR SO EVALUATE].MaxOfRateNumber/6*0.35 AS [SO RESULT], [SP
GET CSR CSI EVALUATE].CSI, [SP GET CSR CSI EVALUATE].MaxOfRateNumber
AS [CSI RATE], [SP GET CSR CSI EVALUATE].MaxOfRateNumber/6*0.1 AS [CSI
RESULT], [SP GET CSR AHT EVALUATE].AvgOfAvgOfAHT AS AHT, [SP GET CSR
RFA ACD EVALUATE].[RFA RESULT] AS RFA, [SP GET CSR RFA ACD EVALUATE].
[RFA RATE], IIf([RFA RATE] Is Null,0.05,[RFA RATE]/6*0.05) AS [RFA
RESULT], [SP GET CSR AHT EVALUATE].MinOfRateNumber AS [AHT RATE], [SP
GET CSR AHT EVALUATE].minOfRateNumber/6*0.15 AS [AHT RESULT], [SP GET
CSR AVAIL EVALUATE].AVAIL AS AVL, [SP GET CSR AVAIL
EVALUATE].MaxOfRateNumber/6*0.15 AS [AVAIL RESULT], [SP GET CSR AVAIL
EVALUATE].MaxOfRateNumber AS [AVAIL RATE]
FROM tblPIPEmployee AS tblPIPEmployee_2 INNER JOIN (tblPIPEmployee AS
tblPIPEmployee_1 INNER JOIN ([SP GET CSR AVAIL EVALUATE] RIGHT JOIN
([SP GET CSR RFA ACD EVALUATE] RIGHT JOIN ([SP GET CSR AHT EVALUATE]
RIGHT JOIN ([SP GET CSR CSI EVALUATE] RIGHT JOIN ([SP GET CSR SO
EVALUATE] RIGHT JOIN (tblPIPEmployee LEFT JOIN [SP GET IKB EVALUATE
RATE RESULT] ON tblPIPEmployee.EmployeeNO = [SP GET IKB EVALUATE RATE
RESULT].EmployeeID) ON [SP GET CSR SO EVALUATE].LinkingID =
tblPIPEmployee.EmployeeNO) ON [SP GET CSR CSI EVALUATE].LinkingID =
tblPIPEmployee.EmployeeNO) ON [SP GET CSR AHT EVALUATE].EmployeeNO =
tblPIPEmployee.EmployeeNO) ON [SP GET CSR RFA ACD EVALUATE].EmployeeNO
= tblPIPEmployee.EmployeeNO) ON [SP GET CSR AVAIL EVALUATE].EmployeeNO
= tblPIPEmployee.EmployeeNO) ON tblPIPEmployee_1.Employee_ID =
tblPIPEmployee.EDA) ON tblPIPEmployee_2.Employee_ID =
tblPIPEmployee.Supervisors
WHERE (((tblPIPEmployee.Employee_ID)=[forms]![frmCSRRankingMain]!
[cmbEmployee]) AND ((tblPIPEmployee_1.Employee_ID)=[forms]!
[frmCSRRankingMain]![cmbEDA]) AND
((tblPIPEmployee_2.Employee_ID)=[forms]![frmCSRRankingMain]!
[cmbSupervisors]) AND ((tblPIPEmployee.Emp_Status)=1) AND
((tblPIPEmployee.Title)=4)) OR
(((tblPIPEmployee_1.Employee_ID)=[forms]![frmCSRRankingMain]![cmbEDA])
AND ((tblPIPEmployee_2.Employee_ID)=[forms]![frmCSRRankingMain]!
[cmbSupervisors]) AND ((tblPIPEmployee.Emp_Status)=1) AND
((tblPIPEmployee.Title)=4) AND (([forms]![frmCSRRankingMain]!
[cmbEmployee]) Is Null)) OR (((tblPIPEmployee.Employee_ID)=[forms]!
[frmCSRRankingMain]![cmbEmployee]) AND
((tblPIPEmployee_2.Employee_ID)=[forms]![frmCSRRankingMain]!
[cmbSupervisors]) AND ((tblPIPEmployee.Emp_Status)=1) AND
((tblPIPEmployee.Title)=4) AND (([forms]![frmCSRRankingMain]![cmbEDA])
Is Null)) OR (((tblPIPEmployee_2.Employee_ID)=[forms]!
[frmCSRRankingMain]![cmbSupervisors]) AND
((tblPIPEmployee.Emp_Status)=1) AND ((tblPIPEmployee.Title)=4) AND
(([forms]![frmCSRRankingMain]![cmbEmployee]) Is Null) AND (([forms]!
[frmCSRRankingMain]![cmbEDA]) Is Null)) OR
(((tblPIPEmployee.Employee_ID)=[forms]![frmCSRRankingMain]!
[cmbEmployee]) AND ((tblPIPEmployee_1.Employee_ID)=[forms]!
[frmCSRRankingMain]![cmbEDA]) AND ((tblPIPEmployee.Emp_Status)=1) AND
((tblPIPEmployee.Title)=4) AND (([forms]![frmCSRRankingMain]!
[cmbSupervisors]) Is Null)) OR
(((tblPIPEmployee_1.Employee_ID)=[forms]![frmCSRRankingMain]![cmbEDA])
AND ((tblPIPEmployee.Emp_Status)=1) AND ((tblPIPEmployee.Title)=4) AND
(([forms]![frmCSRRankingMain]![cmbEmployee]) Is Null) AND (([forms]!
[frmCSRRankingMain]![cmbSupervisors]) Is Null)) OR
(((tblPIPEmployee.Employee_ID)=[forms]![frmCSRRankingMain]!
[cmbEmployee]) AND ((tblPIPEmployee.Emp_Status)=1) AND
((tblPIPEmployee.Title)=4) AND (([forms]![frmCSRRankingMain]![cmbEDA])
Is Null) AND (([forms]![frmCSRRankingMain]![cmbSupervisors]) Is Null))
OR (((tblPIPEmployee.Emp_Status)=1) AND ((tblPIPEmployee.Title)=4) AND
(([forms]![frmCSRRankingMain]![cmbEmployee]) Is Null) AND (([forms]!
[frmCSRRankingMain]![cmbEDA]) Is Null) AND (([forms]!
[frmCSRRankingMain]![cmbSupervisors]) Is Null))
ORDER BY tblPIPEmployee_2.First_Name+" "+tblPIPEmployee_2.Last_Name,
tblPIPEmployee_1.First_Name+" "+tblPIPEmployee_1.Last_Name, [SP GET
CSR CSI EVALUATE].[MaxOfRateNumber]/6*(select max(CSIWeight) from
tblSystem where systemId = 1)+[SP GET CSR SO EVALUATE].
[MaxOfRateNumber]/6*0.35+[SP GET IKB EVALUATE RATE RESULT].
[MaxOfRateNumber]/6*0.15+[SP GET CSR AHT EVALUATE].[minofRateNumber]/
6*0.15+IIf([RFA RATE] Is Null,0.05,[SP GET CSR RFA ACD EVALUATE].[RFA
RATE]/6*0.05)+[SP GET CSR AVAIL EVALUATE].[MaxofRateNumber]/6*0.15;

 

[Bob Barrows [MVP]]

Thanks for helping,
My original query is very long.

It works if I have constant like 35 or 10.
It failed when I have sub query and got syntax error message.
I added max, but still get same error.

Thanks millions,

Here is my original query.

<snip holy sh*t! I guess I asked for that!>

OK, let's try a different tack:
strip everything out of the select clause of that query except the
expression that uses the subquery. I assume it would be this:

SELECT [SP GET CSR CSI EVALUATE].
[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS [FINAL RESULT]
FROM ...

Does the error still occur? if not, start adding stuff back in until the
error occurs

 

[inungh]

Thanks for helping,
My original query is very long.

It works if I have constant like 35 or 10.
It failed when I have sub query and got syntax error message.
I added max, but still get same error.

Thanks millions,

Here is my original query.

<snip holy sh*t! I guess I asked for that!>

OK, let's try a different tack:
strip everything out of the select clause of that query except the
expression that uses the subquery. I assume it would be this:

SELECT  [SP GET CSR CSI EVALUATE].
[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS [FINAL RESULT]
FROM ...

Does the error still occur? if not, start adding stuff back in until the
error occurs
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.- Hide quoted text -

- Show quoted text -

Thanks for helping.

I tried sub query alone is working.
I have constant like

SELECT [SP GET CSR CSI EVALUATE].

[MaxOfRateNumber]/6 * 0.1
AS [FINAL RESULT]
FROM ...

is working too.

When I have

SELECT [SP GET CSR CSI EVALUATE].

[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS [FINAL RESULT]
FROM ...

I got syntax error even I remove everything.
CSIWeight is a numeric field.
There is only one record in the tblsystem.

I just wnated to get data from table instead of hard code for the
future maintainence.

Thanks again,

 

[Bob Barrows [MVP]]

Anyways, what I see there looks correct, syntax-wise. Are you
showing us your actual sql? When you remove this subquery from
the calculation does the error go away?- Hide quoted text -

Thanks for helping,
My original query is very long.

It works if I have constant like 35 or 10.
It failed when I have sub query and got syntax error message.
I added max, but still get same error.

Thanks millions,

Here is my original query.

<snip holy sh*t! I guess I asked for that!>

OK, let's try a different tack:
strip everything out of the select clause of that query except the
expression that uses the subquery. I assume it would be this:

SELECT [SP GET CSR CSI EVALUATE].
[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS [FINAL RESULT]
FROM ...

Does the error still occur? if not, start adding stuff back in until
the error occurs
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.- Hide quoted text -

- Show quoted text -

Thanks for helping.

I tried sub query alone is working.
I have constant like

SELECT [SP GET CSR CSI EVALUATE].

[MaxOfRateNumber]/6 * 0.1
AS [FINAL RESULT]
FROM ...

is working too.

When I have

SELECT [SP GET CSR CSI EVALUATE].

[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS [FINAL RESULT]
FROM ...

I got syntax error even I remove everything.
CSIWeight is a numeric field.
There is only one record in the tblsystem.

I just wnated to get data from table instead of hard code for the
future maintainence.

Thanks again,

How about simply this:

SELECT [SP GET CSR CSI EVALUATE].[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS FINAL_RESULT
FROM [SP GET CSR CSI EVALUATE]

 

[inungh]

inungh wrote:
Anyways, what I see there looks correct, syntax-wise. Are you
showing us your actual sql? When you remove this subquery from
the calculation does the error go away?- Hide quoted text -
Thanks for helping,
My original query is very long.
It works if I have constant like 35 or 10.
It failed when I have sub query and got syntax error message.
I added max, but still get same error.
Thanks millions,
Here is my original query.
<snip holy sh*t! I guess I asked for that!>
OK, let's try a different tack:
strip everything out of the select clause of that query except the
expression that uses the subquery. I assume it would be this:
SELECT [SP GET CSR CSI EVALUATE].
[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS [FINAL RESULT]
FROM ...
Does the error still occur? if not, start adding stuff back in until
the error occurs
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.- Hide quoted text -
- Show quoted text -

Thanks for helping.

I tried sub query alone is working.
I have constant like

SELECT  [SP GET CSR CSI EVALUATE].

[MaxOfRateNumber]/6 * 0.1
AS [FINAL RESULT]
FROM ...

is working too.

When I have

SELECT  [SP GET CSR CSI EVALUATE].

[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS [FINAL RESULT]
FROM ...

I got syntax error even I remove everything.
CSIWeight is a numeric field.
There is only one record in the tblsystem.

I just wnated to get data from table instead of hard code for the
future maintainence.

Thanks again,

How about simply this:

SELECT  [SP GET CSR CSI EVALUATE].[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS FINAL_RESULT
FROM [SP GET CSR CSI EVALUATE]

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.- Hide quoted text -

- Show quoted text -

Thanks millions,
It is working, but I got error when I add the second one.
It seems that MS Access only accept one sub query in one field.
The whole query is working if I have all constant.

If one field only can have one sub query then are there any work
around?

Should I hard code all the paramters?


Your help is great appreciated,

 

[Bob Barrows [MVP]]

How about simply this:

SELECT [SP GET CSR CSI EVALUATE].[MaxOfRateNumber]/6 *
(select max(CSIWeight) from tblSystem where
systemId = 1)
AS FINAL_RESULT
FROM [SP GET CSR CSI EVALUATE]

Thanks millions,
It is working, but I got error when I add the second one.
It seems that MS Access only accept one sub query in one field.
The whole query is working if I have all constant.

I'm missing something. Show me the simple statement that is throwing the
error, and tell me what the error is.

 

[Bob Barrows [MVP]]

If one field only can have one sub query then are there any work
around?

I have just confirmed that more than one subquery can be used in an
expression in a query field, so that is not your problem. Show the
expression that fails.

 

[inungh]

inunghwrote:

I have just confirmed that more than one subquery can be used in an
expression in a query field, so that is not your problem. Show the
expression that fails.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Yes, thanks again,
I did a test that it can have more than one subquery in the same
field.

It seems the SQL over the limitation.

I took out everything and add one by one back. It seems working to add
one by one.

I just wonder are there any length limitation for SQL statement,
because this is a long SQL query.


Thanks millions again,

 

[inungh]

inunghwrote:

I have just confirmed that more than one subquery can be used in an
expression in a query field, so that is not your problem. Show the
expression that fails.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

I found the problem now.
It is order by.
I set order asending in the query design.
Access creates SQL all the subquery in the ORDER BY clause.
My question is does MS Access support sub query in Order by clause?

If not, are there any work around for my situations?
I use subquery to get paramter from system table which works.
I want to query to sort by this column.
any ideas?

Your help is great appreciated,

 

[Bob Barrows [MVP]]

I found the problem now.
It is order by.
I set order asending in the query design.
Access creates SQL all the subquery in the ORDER BY clause.
My question is does MS Access support sub query in Order by clause?

If not, are there any work around for my situations?
I use subquery to get paramter from system table which works.
I want to query to sort by this column.
any ideas?

Yes, there are size limits which you can find by searchin online help for
"specifications".

What I would do is save the query without the order by clause. Then create a
new query, using the saved query as its source. This will allow you to use
the field aliases in the order by rather than the expressions.

 

[inungh]

Yes, there are size limits which you can find by searchin online help for
"specifications".

What I would do is save the query without the order by clause. Then create a
new query, using the saved query as its source. This will allow you to use
the field aliases in the order by rather than the expressions.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"- Hide quoted text -

- Show quoted text -

Thanks millions for helping,