By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

access subquery not enough storage is available to complete this operation

P: n/a
access subquery error: "not enough storage is available to complete
this operation"
Query---

SELECT
TOP 100 PERCENT
UPPER(dbo.Employee.last + N', ' + dbo.Employee.first) AS Employee,
Employee.employeeNumber

FROM
(SELECT DISTINCT
TOP 100 PERCENT
Employee.employeeNumber
FROM
Courses INNER JOIN
Inservices ON dbo.Courses.courseNumber = Inservices.courseNumber INNER
JOIN
Register ON Inservices.courseCounter = Register.courseCounter INNER
JOIN
Employee ON Register.employeeNumber = Employee.employeeNumber
WHERE
(Courses.courseDescription = N'yyyyy')
AND
(Employee.terminationDate IS NULL)
AND
(Register.attendedWorkshop = N'yes')
) AttendedTraining
RIGHT OUTER JOIN Employee ON AttendedTraining.employeeNumber =
Employee.employeeNumber
WHERE
(AttendedTraining.employeeNumber IS NULL)
AND
(Employee.terminationDate IS NULL)
AND
(NOT (Employee.employeeNumber IN (0, 1)
)
)
ORDER BY Employee.last + N', ' + dbo.Employee.first

When I try to add AttendedTraining.employeeNumber I get the error
noted. I can add Employee.employeeNumber (or any other column for that
matter) from the real table.

Any thoughts? The query acts normally when not nested, i.e. the
AttendedTraining base query works normally. Is this just a limitation
with access or what? Thanks!

Jeff

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
is there a reason you're using the TOP predicate and specifying 100%?
that's insane. Why not just use a plain SELECT?

Nov 13 '05 #2

P: n/a
uj*****@charter.net wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:
access subquery error: "not enough storage is available to
complete this operation"


My guess is that you've not got enough free disk space on the drive
where your TEMP directory is located. To find out where that is, in
a the START | RUN command prompt, type %TEMP%, which will open
Windows explorer pointing to the folder that is referenced by the
TEMP variable. Then check to see how much space is available on that
drive.

I prefer to have the TEMP folder on any drive but the one the OS is
running on, since I like to keep nothing but the OS installed on
that drive (I usually put the TEMP folder on the same drive as the
programs are installed).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #3

P: n/a
Access throws that in by default, drives me nuts too.

Nov 13 '05 #4

P: n/a
p.s., if I opened it in SQL I could do what ever I want to it. I
wonder if this is a bug?

Nov 13 '05 #5

P: n/a
In answer to:
is there a reason you're using the TOP predicate and specifying
100%? that's insane. Why not just use a plain SELECT?
uj*****@charter.net wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:
Access throws that in by default, drives me nuts too.


Uh, no, Access does nothing of the sort.

Open the query in design view, go to the query properties and remove
everything from the TOP setting.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.