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
535 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 33

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
 
LVL 33

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now