Show all records in selected by query

[MFranz]

Hi again everyone,

I have to say this list serve is great. I’ve posted a couple of questions
and both times I’ve managed to get the answer I was looking for. Got another
one that I’m hoping someone can point me in the right direction.

I’ve got a Main Form and a Subform within it. The Main Form only has one
record for each property while the Subform is used to input multiple COC
inspections, each property can have x number of records in the Subform. What
I’m trying to do is create a query that will show me all properties that have
a purchase agreement but no COC inspection. The problem is when I link the
two tables together and create a query showing me those records where a COC
inspection has not been done (COC inspection: Criteria = No) it only gives a
blank query. If I reverse the query (COC inspection: Criteria = Yes) then it
will show me only those properties that have had an COC inspection. How can I
get the query to show me all the remaining records from the Main From
(table1) that have a purchase agreement? Thank you in advance for any and all
help.

MFranz

Main Form (table1) – Has a Check Box to indicate if a it has a purchase
agreement – only ONE record per property
Subform (table2) – Has a Check Box to indicate if a COC inspection has been
done – can have MULTIPLE records per property
PID/Maplot is the id field that links the Main Form and the Subform together

 

[schasteen]

If I understand correctly there is no records added to the subform until COC
inspection. My solution will be based on this assumption, but you know what
they say about when you assume. In the queary design view, when you add the
two table and there is a line showing the relationship. Double click that
line and you should get 3 choices. Select the choice that says show all
records from table1 and only those from table2 that match (not exact). Then
for you criteria for COC put isnull or no. If there is no line showing the
relationship between the tables, you can create it by clicking and dragging
the field from table1 to the field in table2.

 

[MFranz]

Hi schasteen,

Thanks for the help. I should have known it would be something as simple as
changing the join type. Well you learn something new everyday, thank you
again though it was a giant help.

MFranz