Only records that do not have a certain entry in subform

P

papa jonah

I have a form that has multiple subforms. One of these forms is used
to enter related codes of which there can be anywhere from 1 to three
entered per main record.
I have a query that I want to run that will identify all records that
DO NOT have a certain code (10A3) identified in the subform.
The only way I know to do that returns all of the record identifiers
1-3 times depending on how many codes may have been entered in the code
table. I am not really interested in whether the codes are included in
the query results, I just want to identify the records that do not have
10A3 assocated.
How can I do that?
 
J

John Vinson

I have a form that has multiple subforms. One of these forms is used
to enter related codes of which there can be anywhere from 1 to three
entered per main record.
I have a query that I want to run that will identify all records that
DO NOT have a certain code (10A3) identified in the subform.
The only way I know to do that returns all of the record identifiers
1-3 times depending on how many codes may have been entered in the code
table. I am not really interested in whether the codes are included in
the query results, I just want to identify the records that do not have
10A3 assocated.
How can I do that?
Click to expand...

The first thing to do is to get away from the idea that there is any
data stored in your subform. There isn't. The form (subform) is *just
a tool*; the data is stored in your Tables, and only in your Tables.

A Query based on your subform's recordsource Table will do the trick
here. Not knowing the structure or fieldnames of your table I can't be
precise, but something like

NOT IN(SELECT RecordID FROM childtable WHERE Code='10A3')

as a criterion on the RecordID field should find all records in the
table which lack a 10A3 record in the child table.

John W. Vinson[MVP]
 
P

papa jonah

John,
This is what I am using:
Not in (SELECT Subgroup.[ORPS Designator]
FROM Subgroup
WHERE (((Subgroup.Subgroup)="10a3"));)

The main table is "ORPS Data" and the child table is the subgroup
table.

However the result when I do this is:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT' or 'UPDATE'.
I suspect something is amiss.
 
J

John Spencer

Try removing the semi-colon at the end of the sub-query. That is the
termination indicator for Access and it means that this is the end of the
ENTIRE query - not just this subquery.
 
P

papa jonah

John,
It still occurs with this
Not in (SELECT Subgroup.[ORPS Designator]
FROM Subgroup
WHERE (((Subgroup.Subgroup)="10a3")))

and with
Not in (SELECT [ORPS Designator]
FROM Subgroup
WHERE [Subgroup]="10a3")

and with
Not in (SELECT ORPS Designator FROM Subgroup
WHERE Subgroup="10a3")
 
J

John Spencer

POST the entire query and not just the subquery.

Any field names or tables names with spaces must be surrounded with [].
Do you really have a subgroup table and a field in the table named subgroup?
 
J

John Vinson

John,
This is what I am using:
Not in (SELECT Subgroup.[ORPS Designator]
FROM Subgroup
WHERE (((Subgroup.Subgroup)="10a3"));)
Click to expand...

Is that just the WHERE clause, or the complete SQL of the query? (It
won't work standalone). Please post the SQL of the entire outer query.

John W. Vinson[MVP]
 
P

papa jonah

At the moment, this is the entire query.

Not in (SELECT [ORPS Data].[ORPS Designator], [Subgroup].[Subgroup]
FROM [ORPS Data] INNER JOIN Subgroup ON [ORPS Data].[ORPS Designator] =
[Subgroup].[ORPS Designator]
WHERE ((([Subgroup].[Subgroup])="10a3")))

Yes, I have a subgroup table with a field called subgroup. The only
other field is the child link, ORPS Designator
 
J

John Spencer

Well that is the problem. This is not query in and of itself. This would
be the criteria to be applied and even then there is a problem as the
subquery should only return one field.

GUESSING that what you want is something like the following.

SELECT [ORPS DATA}.*
FROM [ORPS Data]
WHERE [ORPS DATA].[ORPS Designator]
Not in (SELECT [SubGroup].[ORPS Designator]
FROM Subgroup
WHERE [Subgroup].[Subgroup]="10a3")

This would return all the records in ORPS Data that don't have associated
subgroup records where the subgroup is 10a3
 
Top