Subquery help

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

FBxiii wrote:
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.

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" wrote:

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.

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" wrote:

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" wrote:

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.

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

John Spencer wrote:
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 Countynn

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" wrote:

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" wrote:

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" wrote:

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.

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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.