Only records that do not have a certain entry in subform

  • Thread starter Thread starter papa jonah
  • Start date Start date
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?
 
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]
 
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.
 
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.
 
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")
 
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?
 
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]
 
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
 
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
 
Back
Top