Solved

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

Posted on 2007-03-31
10
585 Views
Last Modified: 2012-05-05
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
Comment
Question by:rockincfman
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 18830230
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
 

Author Comment

by:rockincfman
ID: 18830254
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18830413
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 18830691
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
 

Author Comment

by:rockincfman
ID: 18831138
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18831147
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18831149
I would venture to guess you have a duplicate ID in your join.
0
 
LVL 15

Accepted Solution

by:
danrosenthal earned 125 total points
ID: 18831425
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
 
LVL 1

Expert Comment

by:martinveen
ID: 18832403
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
 

Author Comment

by:rockincfman
ID: 18832573
danrosenthal,

Thank you.  Your solution worked perfectly!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question