Full Outer Join for Dummies

[Ziggy1]

so this is what I mean, first verify that the data is in the main and also in the Queries. you can do this manually like I said, or just work with the the Main table and Only one of the queries at a time. In which case you can reverse the Join to return all of the records from the query. Then you check the Record count of the query alone against the record count of the query when it is joined.

since the queries are coming from other soures you next possibility is that there could be spaces in the fields. you can try using the Trim function in the query.

 

[jdcfsu]

Ok, I'm still not quite getting this. I've revised my queries so that they both contain the same information as the Main Table. The process works like this: Query 1 searches a secondary table --> Query 2 combines information from Query 1 to match that of the Main Table. The result is my two "Target Full" queries which contain separate sets of records, but have all the same fields as that of the Main Table.

You can see in my new attachment that I have created the join the same as was originally proposed to me. When I set my joins like this I return everything that is in the main table with no consideration to what my queries have filtered out. When I switch the joins the other way, "Include ALL records from 'QUERY' and only those that match in 'TABLE', which is what it logically should be -- I think --, I receive this error message: "The SQL statement could not be executed because it contains ambiguous outer joins."

I don't really understand why there isn't a join option that says "Include ALL records from 'QUERY 1' and ALL records from 'QUERY 2'" because that is really all I want to do...

 

[Ziggy1]

I'm not sure why it is not working, your screen print is the correct way to join. But I also suggest you audit the results the way I suggested. you need to work with each query and the main table to make sure your data is returning properly.

To answer your last question, you are doing exactly that with the joins as they are set. but the Trick is the Main table MUST have ALL of the GCFA# that will be found in both queries...it has to be a master list or it won't work.

Your other enemy is if there are spaces in your fields if the data comes form different sources.

You need to look closely at the queries and understand what they are returning

 

[The_Doc_Man]

I might step back and take a different approach.

In your main table that holds the contact information, add a yes/no field.

Build a query to reset the yes/no field to NO.

Take your two candidate-selector queries. Instead of producing a list of candidates, UPDATE the yes/no field to YES (in the main table.)

Now just run a report on contact information where the yes/no field is YES.

You can put all of that into a macro if you like.

Alternative two:

Build another table that holds the prime key of the main table as an FK. Add the decision-making fields from BOTH of the other queries as fields in this table. Make it a one//many with the main table.

Write an ERASE query for the decision table.

Now write the queries to append records to this table. For query 1, store blanks in the fields associated with query 2. For query 2, store blanks in the fields associated with query 1. Make the report based off of a JOIN between the main table and this decision table. Given the ERASE, and two appends, you will have zero, one, or two records in the decision table. Your report can make the fields in the decision table the detail fields, then make the selected record in the main table a group-header.

Yes, this duplicates some data, but only for the purpose of building a report and only for a brief time. No long-term duplication exists.

That's at least two ways of skinning this cat. (MMMMMEEEEEOOOOOWWWWW )