Input and return multiple values from a query

[Bill Beshlian]

I have a query where I input claim number(s) and I want returned the claim
number, last payment date and a check value of "OK" or "NO". Here is the SQL
code:

SELECT (SELECT MPT.ClaimNumber FROM MPT WHERE Instr("," & [Enter claim
numbers separated by commas with no spaces] & ",",
"," & [ClaimNumber] & ",")>0 AS [Claim Number],
Max(MPT.[Company/InsuranceCheckDate]) AS [Last Payment Date], IIf([Last
Payment Date]>(Now()-730),"OK","NO") AS [Check]
FROM MPT
GROUP BY MPT.ClaimNumber;

When I run the query I can input the claim number(s) and I get this error,
"At most on record can be returned by this subquery."

The query worked fine when I had a single parameter. Thanks!

 

[Bob Barrows [MVP]]

I have a query where I input claim number(s) and I want returned the
claim number, last payment date and a check value of "OK" or "NO".
Here is the SQL code:

SELECT (SELECT MPT.ClaimNumber FROM MPT WHERE Instr("," & [Enter claim
numbers separated by commas with no spaces] & ",",
"," & [ClaimNumber] & ",")>0 AS [Claim Number],
Max(MPT.[Company/InsuranceCheckDate]) AS [Last Payment Date],
IIf([Last Payment Date]>(Now()-730),"OK","NO") AS [Check]
FROM MPT
GROUP BY MPT.ClaimNumber;

When I run the query I can input the claim number(s) and I get this
error, "At most on record can be returned by this subquery."

The query worked fine when I had a single parameter. Thanks!

It is possible for multiple records to satisfy that criterion. When used
in a SELECT list, a subquery must be guaranteed to return a single field
from a single record.

The subquery does not seem to be needed. Why not simply put that
criterion in the main query's WHERE clause?

SELECT ClaimNumber,
Max(MPT.[Company/InsuranceCheckDate]) AS [Last Payment Date], IIf([Last
Payment Date]>(Now()-730),"OK","NO") AS [Check]
FROM MPT
WHERE Instr("," & [Enter claim numbers separated by commas with no
spaces] & ",","," & [ClaimNumber] & ",")>0
GROUP BY MPT.ClaimNumber;

 

[Bill Beshlian]

Thanks Bob! The query returned exactly what I am looking for.

Bill Beshlian

I have a query where I input claim number(s) and I want returned the
claim number, last payment date and a check value of "OK" or "NO".
Here is the SQL code:

SELECT (SELECT MPT.ClaimNumber FROM MPT WHERE Instr("," & [Enter claim
numbers separated by commas with no spaces] & ",",
"," & [ClaimNumber] & ",")>0 AS [Claim Number],
Max(MPT.[Company/InsuranceCheckDate]) AS [Last Payment Date],
IIf([Last Payment Date]>(Now()-730),"OK","NO") AS [Check]
FROM MPT
GROUP BY MPT.ClaimNumber;

When I run the query I can input the claim number(s) and I get this
error, "At most on record can be returned by this subquery."

The query worked fine when I had a single parameter. Thanks!

It is possible for multiple records to satisfy that criterion. When used
in a SELECT list, a subquery must be guaranteed to return a single field
from a single record.

The subquery does not seem to be needed. Why not simply put that
criterion in the main query's WHERE clause?

SELECT ClaimNumber,
Max(MPT.[Company/InsuranceCheckDate]) AS [Last Payment Date], IIf([Last
Payment Date]>(Now()-730),"OK","NO") AS [Check]
FROM MPT
WHERE Instr("," & [Enter claim numbers separated by commas with no
spaces] & ",","," & [ClaimNumber] & ",")>0
GROUP BY MPT.ClaimNumber;


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.