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
627 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 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