Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Explanation of Erro (A2K)

    I have the following code that works perfectly for 1 query.

    <pre>SELECT tIP_DEMOS_DX.[maxMC#], tIP_DEMOS_DX.SSN AS SSN, tIP_DEMOS_DX.SFD, &_
    tIP_DEMOS_DX.STD, [sfd]-nz((SELECT TOP 1 ID.STD FROM tIP_DEMOS_DX AS ID &_
    WHERE ID.STD< tIP_DEMOS_DX.SFD AND ID.SSN=tIP_DEMOS_DX.SSN ORDER &_
    BY ID.STD DESC,[sfd]+1) AS DaysSinceLastVisit, tIP_DEMOS_DX.AUTHORIZATION_NO &_
    , tIP_DEMOS_DX.FACILITY_NAME, tIP_DEMOS_DX.PRIMARY_DX_DECIMAL, tIP_DEMOS_DX &_
    .DISCHARGE_DX, tIP_DEMOS_DX.DISCHARGE_DATE, tIP_DEMOS_DX.TOTAL_CERTIFIED_DAYS
    FROM tIP_DEMOS_DX WHERE ((([sfd]-nz((SELECT TOP 1 ID.STD FROM tIP_DEMOS_DX AS ID &_
    WHERE ID.STD < tIP_DEMOS_DX.SFD AND ID.SSN=tIP_DEMOS_DX.SSN ORDER BY ID.STD DESC,[sfd]+1)) &_
    Between 0 And 30));</pre>


    yet when I try to apply it to another query I have the following problem:
    The query returns records, but when I click on a record I get the following error msg "At most one record can be return by the subquery" then each records value change to #Name?

    The field names and data types are exactly the same. Could someone please explain to me what I might be doing wrong?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Explanation of Erro (A2K)

    Try saving the subquery as a separate query, and add that to the query that doesn't work.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Explanation of Erro (A2K)

    I don't understand when you say "I have the following code that works perfectly for 1 query". The "code" is a query. Do you mean you are using it as a subquery in another query? And if so, where/how are you using it? In a JOIN or perhaps in a WHERE clause?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Explanation of Erro (A2K)

    Sorry I mis spoke. This is the syntax from a query (in SQL view). There is only 1 TABLE in the query (the one that works correctly). With that said, there is only 1 TABLE in the query (the one that doesn't work) each field is named the same and each field is the same data type, i.e. text v text, date v date, etc.

    I know the error msg says something to do with a sub query, but this isn't a sub query. 1 small table with a few fields. In the "DaysSinceLastVisit" criteria section is "between 0 and 30".

    Anyway, in 1 query it works perfectly, yet in the other I get the error message and I simply don't understand what it's trying to tell me.

    Sorry for the misunderstanding.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Explanation of Erro (A2K)

    The subquery is

    (SELECT TOP 1 ID.STD FROM tIP_DEMOS_DX AS ID &_WHERE ID.STD< tIP_DEMOS_DX.SFD AND ID.SSN=tIP_DEMOS_DX.SSN ORDER &_BY ID.STD DESC

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Explanation of Erro (A2K)

    OK...here it is....

    You will only see 2 tables and 2 queries.

    q_30_Day_Logic_tIP_DEMOS_DX works as it should but....
    q_30_Day_Logic_MainReport doesn't...and

    The field names are identical as well as the data types.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Explanation of Erro (A2K)

    Sorry, this doesn't help. The query q_30_Day_Logic_MainReport is based on q_30_Day_Logic_ReAdmits1 which isn't included in the database.

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Explanation of Erro (A2K)

    I'll get this right yet....
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Explanation of Erro (A2K)

    A TOP 1 query may not always do what you expect. If there is a tie, it will return *all* records with the highest value, not just one. In q_30_Day_Logic_tIP_DEMOS_DX, this apparently doesn't occur, but in q_30_Day_Logic_MainReport, it does - so the subquery violates the rule that it should return only one record.
    In the attached version, I have created an extra query that uses MAX instead of TOP, and used this in q_30_Day_Logic_MainReport.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •