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
579 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
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 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
 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.

831 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