Report Records That Are Missing A Specific Related Record
I need to create a report that shows records that are missing a specific related record. I have to two tables. Table A is the main record and Table B contains related records. Table A can relate to many Table B records, and Table B records can be 1 of 5 different types. I need to find out which Table A records are missing a specific Table B type. My current view is an Outer join between Table A and Table B.
My filter is for all Table A records that do not have a specific Table record type but what gets returned are all of the other related Table B records for the Table A Record. I believe this is because of the Outer join, but making it an inner join returns no records. I was able to create an Append query to Table B and get a count of Table B records for a specific type, but then couldn't just show the main records without a count <1; all records and the ones without the relationship type show NULL in the count column.
Is this possible to do with my current setup?