Subquery help

  • Thread starter Thread starter FBxiii
  • Start date Start date
F

FBxiii

Hi.

I am trying to run a query, but am getting a 'At most one record can be
returned by this subquery' error.

I have used subqueries before but have never seen this message.

The query should be bringing back multiple references between the specified
date ranges. Can anyone advise how to get round this? A further problem is
conflicting data-types within the database, but I have no control over this.

My query is:

SELECT UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE,
UKL_OWNER_REJECTED_METER_RD.ACTUAL_READ_DATE,
UKL_OWNER_REJECTED_METER_RD.METER_INDEX, UKL_OWNER_ORGANISATION.SHORT_CODE
FROM UKL_OWNER_REJECTED_METER_RD INNER JOIN UKL_OWNER_ORGANISATION ON
UKL_OWNER_REJECTED_METER_RD.ORG_ID = UKL_OWNER_ORGANISATION.ORG_ID
WHERE (((UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE)=

(SELECT Trim(Str([UKL_OWNER_METER_POINT]![MPO_REFERENCE])) FROM
UKL_OWNER_METER_POINT WHERE (((UKL_OWNER_METER_POINT.STATUS_CODE) Not In
("DE","EX")) AND ((UKL_OWNER_METER_POINT.CMPTTN_EFCTV_DATE) Between
#5/1/2008# And #8/31/2008#));)

));

Thanks,
Steve.
 
Instead of = as the comparison operator use IN.

SELECT UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE,
UKL_OWNER_REJECTED_METER_RD.ACTUAL_READ_DATE,
UKL_OWNER_REJECTED_METER_RD.METER_INDEX, UKL_OWNER_ORGANISATION.SHORT_CODE
FROM UKL_OWNER_REJECTED_METER_RD INNER JOIN UKL_OWNER_ORGANISATION ON
UKL_OWNER_REJECTED_METER_RD.ORG_ID = UKL_OWNER_ORGANISATION.ORG_ID
WHERE UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE IN
(SELECT Trim(Str([UKL_OWNER_METER_POINT]![MPO_REFERENCE]))
FROM UKL_OWNER_METER_POINT
WHERE UKL_OWNER_METER_POINT.STATUS_CODE Not In ("DE","EX")
AND UKL_OWNER_METER_POINT.CMPTTN_EFCTV_DATE Between
#5/1/2008# And #8/31/2008#)

You can use the equal operator if you are returning only one record (row). So
if you were returning Max or Min of the MPO_Reference, you could use the equal
operator. Access will reject the equal operator even if you know that only
one record would be returned based on the data in the table. Access knows
there is a possibility that more than one row (record) would be returned.

WHERE UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE =
(SELECT MAX(Trim(Str([UKL_OWNER_METER_POINT]![MPO_REFERENCE])))
FROM UKL_OWNER_METER_POINT
WHERE UKL_OWNER_METER_POINT.STATUS_CODE Not In ("DE","EX")
AND UKL_OWNER_METER_POINT.CMPTTN_EFCTV_DATE Between
#5/1/2008# And #8/31/2008#)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Unless it's just a typo, what you show below has too many semicolons. You
shouldn't need one in the subquery.

After that I'd try changing the = to an IN statement.
 
No, it wasn't a typo. I have removed it and got the same error so I am now
trying the In method (no error as yet).

I shall await in anticipation of a result and confirm the outcome :-)

Thanks for the prompt response.
Steve.


Jerry Whittle said:
Unless it's just a typo, what you show below has too many semicolons. You
shouldn't need one in the subquery.

After that I'd try changing the = to an IN statement.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

FBxiii said:
Hi.

I am trying to run a query, but am getting a 'At most one record can be
returned by this subquery' error.

I have used subqueries before but have never seen this message.

The query should be bringing back multiple references between the specified
date ranges. Can anyone advise how to get round this? A further problem is
conflicting data-types within the database, but I have no control over this.

My query is:

SELECT UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE,
UKL_OWNER_REJECTED_METER_RD.ACTUAL_READ_DATE,
UKL_OWNER_REJECTED_METER_RD.METER_INDEX, UKL_OWNER_ORGANISATION.SHORT_CODE
FROM UKL_OWNER_REJECTED_METER_RD INNER JOIN UKL_OWNER_ORGANISATION ON
UKL_OWNER_REJECTED_METER_RD.ORG_ID = UKL_OWNER_ORGANISATION.ORG_ID
WHERE (((UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE)=

(SELECT Trim(Str([UKL_OWNER_METER_POINT]![MPO_REFERENCE])) FROM
UKL_OWNER_METER_POINT WHERE (((UKL_OWNER_METER_POINT.STATUS_CODE) Not In
("DE","EX")) AND ((UKL_OWNER_METER_POINT.CMPTTN_EFCTV_DATE) Between
#5/1/2008# And #8/31/2008#));)

));

Thanks,
Steve.
Click to expand...
Click to expand...
 
I forgot that you can use ANY, ALL, or SOME so should also be able to use
= ANY (SELECT SomeField ...)
or
= SOME (SELECT SomeField ...)

WHERE UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE = ANY
(SELECT Trim(Str([UKL_OWNER_METER_POINT]![MPO_REFERENCE]))
FROM UKL_OWNER_METER_POINT
WHERE UKL_OWNER_METER_POINT.STATUS_CODE Not In ("DE","EX")
AND UKL_OWNER_METER_POINT.CMPTTN_EFCTV_DATE Between
#5/1/2008# And #8/31/2008#)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
No, it wasn't a typo.
Subqueries do not have semicolons. A query, no matter what kind only has
one semicolon and that is at the very end of the SQL statement.

Did you try the 'IN' function as suggested --
WHERE (((UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE) IN (SELECT
Trim(Str([UKL_OWNER_METER_POINT]![.......

--
Build a little, test a little.


FBxiii said:
No, it wasn't a typo. I have removed it and got the same error so I am now
trying the In method (no error as yet).

I shall await in anticipation of a result and confirm the outcome :-)

Thanks for the prompt response.
Steve.


Jerry Whittle said:
Unless it's just a typo, what you show below has too many semicolons. You
shouldn't need one in the subquery.

After that I'd try changing the = to an IN statement.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

FBxiii said:
Hi.

I am trying to run a query, but am getting a 'At most one record can be
returned by this subquery' error.

I have used subqueries before but have never seen this message.

The query should be bringing back multiple references between the specified
date ranges. Can anyone advise how to get round this? A further problem is
conflicting data-types within the database, but I have no control over this.

My query is:

SELECT UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE,
UKL_OWNER_REJECTED_METER_RD.ACTUAL_READ_DATE,
UKL_OWNER_REJECTED_METER_RD.METER_INDEX, UKL_OWNER_ORGANISATION.SHORT_CODE
FROM UKL_OWNER_REJECTED_METER_RD INNER JOIN UKL_OWNER_ORGANISATION ON
UKL_OWNER_REJECTED_METER_RD.ORG_ID = UKL_OWNER_ORGANISATION.ORG_ID
WHERE (((UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE)=

(SELECT Trim(Str([UKL_OWNER_METER_POINT]![MPO_REFERENCE])) FROM
UKL_OWNER_METER_POINT WHERE (((UKL_OWNER_METER_POINT.STATUS_CODE) Not In
("DE","EX")) AND ((UKL_OWNER_METER_POINT.CMPTTN_EFCTV_DATE) Between
#5/1/2008# And #8/31/2008#));)

));

Thanks,
Steve.
Click to expand...
Click to expand...
Click to expand...
 
I meant it wasn't a typo, but a genuine mistake. I had copied/pasted the SQL
from another query.

The query ran but I got an 'Out of Memory' error.

I am having major performance issues but it is down to the difference in
data-types across the 2 tables I need to query. There is no way of changing
the DB because it would cost a lot of money.

Are there any hints/tips for improving the performance or changing
data-types in-query?


KARL DEWEY said:
Subqueries do not have semicolons. A query, no matter what kind only has
one semicolon and that is at the very end of the SQL statement.

Did you try the 'IN' function as suggested --
WHERE (((UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE) IN (SELECT
Trim(Str([UKL_OWNER_METER_POINT]![.......

--
Build a little, test a little.


FBxiii said:
No, it wasn't a typo. I have removed it and got the same error so I am now
trying the In method (no error as yet).

I shall await in anticipation of a result and confirm the outcome :-)

Thanks for the prompt response.
Steve.


Jerry Whittle said:
Unless it's just a typo, what you show below has too many semicolons. You
shouldn't need one in the subquery.

After that I'd try changing the = to an IN statement.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi.

I am trying to run a query, but am getting a 'At most one record can be
returned by this subquery' error.

I have used subqueries before but have never seen this message.

The query should be bringing back multiple references between the specified
date ranges. Can anyone advise how to get round this? A further problem is
conflicting data-types within the database, but I have no control over this.

My query is:

SELECT UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE,
UKL_OWNER_REJECTED_METER_RD.ACTUAL_READ_DATE,
UKL_OWNER_REJECTED_METER_RD.METER_INDEX, UKL_OWNER_ORGANISATION.SHORT_CODE
FROM UKL_OWNER_REJECTED_METER_RD INNER JOIN UKL_OWNER_ORGANISATION ON
UKL_OWNER_REJECTED_METER_RD.ORG_ID = UKL_OWNER_ORGANISATION.ORG_ID
WHERE (((UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE)=

(SELECT Trim(Str([UKL_OWNER_METER_POINT]![MPO_REFERENCE])) FROM
UKL_OWNER_METER_POINT WHERE (((UKL_OWNER_METER_POINT.STATUS_CODE) Not In
("DE","EX")) AND ((UKL_OWNER_METER_POINT.CMPTTN_EFCTV_DATE) Between
#5/1/2008# And #8/31/2008#));)

));

Thanks,
Steve.
Click to expand...
Click to expand...
Click to expand...
 
There are change functions such as CDate, CStr, etc., which can change things
like text to a date or convert a number to a text. There's also the Val
function that can take then numbers at the beginning of a string and convert
them to numbers.

Press the F1 key to bring up Help and search on CStr. Type Conversion
Functions should be one of the items returned.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


FBxiii said:
I meant it wasn't a typo, but a genuine mistake. I had copied/pasted the SQL
from another query.

The query ran but I got an 'Out of Memory' error.

I am having major performance issues but it is down to the difference in
data-types across the 2 tables I need to query. There is no way of changing
the DB because it would cost a lot of money.

Are there any hints/tips for improving the performance or changing
data-types in-query?


KARL DEWEY said:
No, it wasn't a typo.
Click to expand...
Subqueries do not have semicolons. A query, no matter what kind only has
one semicolon and that is at the very end of the SQL statement.

Did you try the 'IN' function as suggested --
WHERE (((UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE) IN (SELECT
Trim(Str([UKL_OWNER_METER_POINT]![.......

--
Build a little, test a little.


FBxiii said:
No, it wasn't a typo. I have removed it and got the same error so I am now
trying the In method (no error as yet).

I shall await in anticipation of a result and confirm the outcome :-)

Thanks for the prompt response.
Steve.


:

Unless it's just a typo, what you show below has too many semicolons. You
shouldn't need one in the subquery.

After that I'd try changing the = to an IN statement.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi.

I am trying to run a query, but am getting a 'At most one record can be
returned by this subquery' error.

I have used subqueries before but have never seen this message.

The query should be bringing back multiple references between the specified
date ranges. Can anyone advise how to get round this? A further problem is
conflicting data-types within the database, but I have no control over this.

My query is:

SELECT UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE,
UKL_OWNER_REJECTED_METER_RD.ACTUAL_READ_DATE,
UKL_OWNER_REJECTED_METER_RD.METER_INDEX, UKL_OWNER_ORGANISATION.SHORT_CODE
FROM UKL_OWNER_REJECTED_METER_RD INNER JOIN UKL_OWNER_ORGANISATION ON
UKL_OWNER_REJECTED_METER_RD.ORG_ID = UKL_OWNER_ORGANISATION.ORG_ID
WHERE (((UKL_OWNER_REJECTED_METER_RD.MPO_REFERENCE)=

(SELECT Trim(Str([UKL_OWNER_METER_POINT]![MPO_REFERENCE])) FROM
UKL_OWNER_METER_POINT WHERE (((UKL_OWNER_METER_POINT.STATUS_CODE) Not In
("DE","EX")) AND ((UKL_OWNER_METER_POINT.CMPTTN_EFCTV_DATE) Between
#5/1/2008# And #8/31/2008#));)

));

Thanks,
Steve.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top