• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

Need help fixing "At most one record can be returned by this subquery" SQL query on Access DB

I am having problems with a SQL query on an Access database.  The query is returning the “[Microsoft][ODBC Microsoft Access Driver] At most one record can be returned by this subquery.” error.  I have no idea why.

I have two very similar queries and the first one below works but the second doesn’t.  I don't know what I am missing.  Any thoughts?

QUERY BELOW WORKS
SELECT o.oid, o.problem, o.suggestion, e.firstname, e.lastname, (SELECT date FROM history WHERE oid = o.oid AND action = 1) AS dateAddedDate FROM ofi o INNER JOIN employee e ON o.eid = e.eid WHERE oid IN (SELECT o.oid FROM keyofi o INNER JOIN keyword k ON o.kid = k.kid WHERE k.keyword = 'Safety') AND ((o.status = 1) OR (o.status = 2)) ORDER BY o.oid

QUERY BELOW RETURNS ERROR
SELECT o.oid, o.problem, o.suggestion, e.firstname, e.lastname, (SELECT date FROM history WHERE oid = o.oid AND action = 1) AS dateAddedDate FROM ofi o INNER JOIN employee e ON o.eid = e.eid WHERE oid IN (SELECT o.oid FROM keyofi o INNER JOIN keyword k ON o.kid = k.kid WHERE k.keyword = 'Safety') AND o.status = 3 ORDER BY o.oid
0
rockincfman
Asked:
rockincfman
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
In the second quey you have:

...WHERE k.keyword = 'Safety') AND o.status = 3 ORDER BY o.oid

Change it to:

...WHERE k.keyword = 'Safety' AND o.status = 3) ORDER BY o.oid

Mike
0
 
rockincfmanAuthor Commented:
I am pretty sure that your change is going to return a different set of results than the ones I want.  This doesn't seem to be the right solution.  Any other thoughts?
0
 
jefftwilleyCommented:
Based on the "working" SQL

SELECT o.oid, o.problem, o.suggestion, e.firstname, e.lastname, (SELECT [date] FROM history WHERE oid = o.oid AND action = 1) AS dateAddedDate FROM ofi o INNER JOIN employee e ON o.eid = e.eid WHERE oid IN (SELECT o.oid FROM keyofi o INNER JOIN keyword k ON o.kid = k.kid WHERE k.keyword = 'Safety') AND (o.status = 3) ORDER BY o.oid;


Also, the use of the word "Date" as a field name is not recommended as it is a function within Access. So I surrouned it with brackets.

J
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Mike EghtebasDatabase and Application DeveloperCommented:
How about:

SELECT o.oid, o.problem, o.suggestion, e.firstname, e.lastname, (SELECT date FROM history WHERE oid = o.oid AND action = 1) AS dateAddedDate FROM ofi o INNER JOIN employee e ON o.eid = e.eid WHERE (o.status = 3) AND  (oid IN (SELECT o.oid FROM keyofi o INNER JOIN keyword k ON o.kid = k.kid WHERE k.keyword = 'Safety')) ORDER BY o.oid

I just moved  (o.status = 3) before (oid IN (SELECT ... WHERE k.keyword = 'Safety'))
0
 
rockincfmanAuthor Commented:
jefftwilley,
I had already tried that and it didn't work.  I tried it again to make sure and it still didn't work.

eghtebas,
I had not thought of that because I didn't think it would make a difference since it is an AND statement and unforunately I was right.  It still did not work.

Any more ideas?
0
 
jefftwilleyCommented:
you cannot return multiple records from o for every record in keyofi when you include it as a field in the select portion of the main query.

I think if you drop OID from the select statement, you'll not have the issue.

Reference\
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20422917.html

Why it works for one and not the other...your OR statement isn't returning multiple selects from your subquery...I'm guessing.
J
0
 
jefftwilleyCommented:
I would venture to guess you have a duplicate ID in your join.
0
 
danrosenthalCommented:
Change this:
 (SELECT date FROM history WHERE oid = o.oid AND action = 1) AS dateAddedDate

To this:
 (SELECT TOP 1 date FROM history WHERE oid = o.oid AND action = 1) AS dateAddedDate
0
 
martinveenCommented:
Your subquery: (SELECT date FROM history WHERE oid = o.oid AND action = 1) syntax might return more than 1 record, eventhough your tablestructure doesn't allow more than one value.

Use a MAX(date) and the group by clause on your subquery  to fix your problem.
0
 
rockincfmanAuthor Commented:
danrosenthal,

Thank you.  Your solution worked perfectly!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now