Subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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))
 
I receive the error message "At most one record can be returned by this
subquery". And it doesn't return any records. Any ideas?
 
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))
 
Back
Top