RE: Subquery help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

.

Relevant Pages

  • Re: Slow SQL, too many logical reads ?
    ... I would be tempted to try reformating the SQL statement like this to ... Oracle is performing a nested loop join between the subquery and the ... The explain plan for... ... (Run your query) ...
    (comp.databases.oracle.server)
  • RE: Subquery help
    ... No, it wasn't a typo. ... shouldn't need one in the subquery. ... Jerry Whittle, Microsoft Access MVP ... The query should be bringing back multiple references between the specified ...
    (microsoft.public.access.queries)
  • RE: Assigning unique identifier to unique authors
    ... The SQL statement would look something like below. ... Books to your actual table name. ... Jerry Whittle, Microsoft Access MVP ... Create a query based on just the FirstName and LastName fields. ...
    (microsoft.public.access.queries)
  • RE: Query criteria repeating
    ... The SQL statement doesn't define the parameter. ... Jerry Whittle, Microsoft Access MVP ... same criteria twice before the query runs. ...
    (microsoft.public.access.queries)
  • RE: how do I update multiple queries at once?
    ... SQL statement: ... Jerry Whittle, Microsoft Access MVP ... I assume that F1 and F2 are different fish ID's? ... Something like the query below can find ...
    (microsoft.public.access.gettingstarted)