Subquery

S

SHIPP

I've got this sub-query in Access 2003.

(SELECT tblPanelMbr.PanMbrID FROM tblPanelMbr WHERE in
(tblPanelMbr.Archived=yes))

What I am trying to do is to delete records in a sub-table where a field in
the header record is marked as archived.

I get a syntax error. Please help.
 
D

Dale Fye

Try the following, your entire query looks something like:

DELETE * FROM tblPanelMbr_Sub
WHERE tblPanel_Mbr_Sub.PanMbrID
IN (SELECT tblPanelMbr.PanMbrID FROM tbl_PanelMbr
WHERE tblPanelMbr.Archived = True)

Does this work? If not, what kind of error are you getting?

Dale
 
O

Ofer Cohen

The problem with your sub query is the extra "in"

Yours
(SELECT tblPanelMbr.PanMbrID FROM tblPanelMbr WHERE in
(tblPanelMbr.Archived=yes))

Should Be
(SELECT tblPanelMbr.PanMbrID FROM tblPanelMbr WHERE
(tblPanelMbr.Archived=yes))
 
S

SHIPP

I receive the error message "At most one record can be returned by this
subquery". And it doesn't return any records. Any ideas?
 
O

Ofer Cohen

It's important to post the full SQL and not only the sub query

Have you looked at Dale's sample of SQL?
In your query you might have
Select * From TableName Where FieldName = (SELECT tblPanelMbr.PanMbrID FROM
tblPanelMbr WHERE (tblPanelMbr.Archived=yes))

The Equal sign require only one input, Istead use In

Select * From TableName Where FieldName In (SELECT tblPanelMbr.PanMbrID FROM
tblPanelMbr WHERE (tblPanelMbr.Archived=yes))
 
Top