Subquery returning more than one result

  • Thread starter Thread starter Gareth Mercer
  • Start date Start date
G

Gareth Mercer

I have a query that is used to cascade a delete on a
single table with the following SQL

DELETE tblFolders.FolderID, tblFolders.Level,
tblFolders.ParentFolder, [Forms]![frmDeleteFolder]![Level]
AS [Level]
FROM tblFolders
WHERE (((tblFolders.FolderID)=[Forms]![frmDeleteFolder]!
[SelectedFolder]) AND ((tblFolders.Level)=[Forms]!
[frmDeleteFolder]![Level]) AND (([Forms]![frmDeleteFolder]!
[Level])=2)) OR (((tblFolders.ParentFolder)=[Forms]!
[frmDeleteFolder]![Subfolder1]) AND (([Forms]!
[frmDeleteFolder]![Level])=1)) OR
(((tblFolders.ParentFolder)=(SELECT [FolderID] FROM
[Select Level 1 for Delete])) AND (([Forms]!
[frmDeleteFolder]![Level])=0))

I know the problem is that the subquery is likely to
return more than 1 record, but how amend the SQL to allow
for this eventuality.

Many thanks in advance

Gareth
 
Change it to

tblFolders.ParentFolder IN
(SELECT Folderid
FROM [Select Level 1 for Delete])

Or if you only want ONE value
tblFolders.ParentFolder =
(SELECT Max(Folderid)
FROM [Select Level 1 for Delete])
 
John,

Thanks for for swift response, but even with that
alteration I am still getting the pesky eror messages:

When run from form - Data Type Mismatch
When run from Query Builder - Typed incorrectly or too
complex.

For your information, the SQL of the [Select Level 1 for
Delete] query is:

SELECT tblFolders.FolderID, tblFolders.Level, [Forms]!
[frmDeleteFolder]![Level] AS [Level],
tblFolders.ParentFolder
FROM tblFolders
WHERE ((([Forms]![frmDeleteFolder]![Level])=0) AND
((tblFolders.ParentFolder)=[Forms]![frmDeleteFolder]!
[Root])) OR (((tblFolders.FolderID)=[Forms]!
[frmDeleteFolder]![SelectedFolder]) AND ((tblFolders.Level)
=[Forms]![frmDeleteFolder]![Level]) AND (([Forms]!
[frmDeleteFolder]![Level])=1))


I do need multiple results from the subquery


Gareth

-----Original Message-----
Change it to

tblFolders.ParentFolder IN
(SELECT Folderid
FROM [Select Level 1 for Delete])

Or if you only want ONE value
tblFolders.ParentFolder =
(SELECT Max(Folderid)
FROM [Select Level 1 for Delete])

Gareth said:
I have a query that is used to cascade a delete on a
single table with the following SQL

DELETE tblFolders.FolderID, tblFolders.Level,
tblFolders.ParentFolder, [Forms]![frmDeleteFolder]! [Level]
AS [Level]
FROM tblFolders
WHERE (((tblFolders.FolderID)=[Forms]![frmDeleteFolder]!
[SelectedFolder]) AND ((tblFolders.Level)=[Forms]!
[frmDeleteFolder]![Level]) AND (([Forms]! [frmDeleteFolder]!
[Level])=2)) OR (((tblFolders.ParentFolder)=[Forms]!
[frmDeleteFolder]![Subfolder1]) AND (([Forms]!
[frmDeleteFolder]![Level])=1)) OR
(((tblFolders.ParentFolder)=(SELECT [FolderID] FROM
[Select Level 1 for Delete])) AND (([Forms]!
[frmDeleteFolder]![Level])=0))

I know the problem is that the subquery is likely to
return more than 1 record, but how amend the SQL to allow
for this eventuality.

Many thanks in advance

Gareth
Click to expand...
.
Click to expand...
 
Problem now solved - the nested query was fine with the IN
statement. The probelm below was caused by [Subfolder1]
being NULL when [Level]=0. 1 quick hidden textbox later
[Subfolder1Value] with a default number and everything now
works!

Final working SQL statement below for others with a
similar problem:

DELETE tblFolders.FolderID, tblFolders.Level,
tblFolders.ParentFolder, [Forms]![frmDeleteFolder]![Level]
AS [Level]
FROM tblFolders
WHERE (((tblFolders.ParentFolder) In (SELECT [tblFolders].
[FolderID] FROM tblFolders WHERE (([tblFolders].
[ParentFolder])=[Forms]![frmDeleteFolder]![Root]))) AND
(([Forms]![frmDeleteFolder]![Level])=0)) OR
(((tblFolders.ParentFolder)=[Forms]![frmDeleteFolder]!
[Subfolder1Value]) AND (([Forms]![frmDeleteFolder]![Level])
=1)) OR (((tblFolders.FolderID)=[Forms]![frmDeleteFolder]!
[SelectedFolder]) AND ((tblFolders.Level)=[Forms]!
[frmDeleteFolder]![Level]) AND (([Forms]![frmDeleteFolder]!
[Level])=2));

Again thanks

Gareth

-----Original Message-----
John,

Thanks for for swift response, but even with that
alteration I am still getting the pesky eror messages:

When run from form - Data Type Mismatch
When run from Query Builder - Typed incorrectly or too
complex.

For your information, the SQL of the [Select Level 1 for
Delete] query is:

SELECT tblFolders.FolderID, tblFolders.Level, [Forms]!
[frmDeleteFolder]![Level] AS [Level],
tblFolders.ParentFolder
FROM tblFolders
WHERE ((([Forms]![frmDeleteFolder]![Level])=0) AND
((tblFolders.ParentFolder)=[Forms]![frmDeleteFolder]!
[Root])) OR (((tblFolders.FolderID)=[Forms]!
[frmDeleteFolder]![SelectedFolder]) AND ((tblFolders.Level)
=[Forms]![frmDeleteFolder]![Level]) AND (([Forms]!
[frmDeleteFolder]![Level])=1))


I do need multiple results from the subquery


Gareth

-----Original Message-----
Change it to

tblFolders.ParentFolder IN
(SELECT Folderid
FROM [Select Level 1 for Delete])

Or if you only want ONE value
tblFolders.ParentFolder =
(SELECT Max(Folderid)
FROM [Select Level 1 for Delete])

Gareth said:
I have a query that is used to cascade a delete on a
single table with the following SQL

DELETE tblFolders.FolderID, tblFolders.Level,
tblFolders.ParentFolder, [Forms]![frmDeleteFolder]! [Level]
AS [Level]
FROM tblFolders
WHERE (((tblFolders.FolderID)=[Forms]! [frmDeleteFolder]!
[SelectedFolder]) AND ((tblFolders.Level)=[Forms]!
[frmDeleteFolder]![Level]) AND (([Forms]! [frmDeleteFolder]!
[Level])=2)) OR (((tblFolders.ParentFolder)=[Forms]!
[frmDeleteFolder]![Subfolder1]) AND (([Forms]!
[frmDeleteFolder]![Level])=1)) OR
(((tblFolders.ParentFolder)=(SELECT [FolderID] FROM
[Select Level 1 for Delete])) AND (([Forms]!
[frmDeleteFolder]![Level])=0))

I know the problem is that the subquery is likely to
return more than 1 record, but how amend the SQL to allow
for this eventuality.

Many thanks in advance

Gareth
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top