Explanation of Erro (A2K)

Topic

New Reply

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

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));

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?

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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 for instructions.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

I’ll get this right yet….

Reply | Quote

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.

Reply | Quote