Problem with Showing All Records

[molsen]

Hi All

I have developed a system where three separate lots of data are imported and compared in a query. There is a unique code contained within each import, which is the key field that relates all three sources.

However I want to see data returned where for example this unique code and its associated data does not exist in one of the other three sources. In reality all three sources must have exactly the same matching data within them, and if a line is missing in any one or more of them, we have a reconciliation problem.

I know its a simple answer but I'm stumped! At the moment the query which draws in all the data will miss out a line altogether in the resulting query. I want it to appear as an exception. Attached is a picture of the relationships between the three source tables.

Many thanks

 

[jdraw]

This seems related to a few of your recent posts. Perhaps you should tell us more about the purpose and where you currently stand regarding a solution. Also, some sample data showing the incoming the process and the expected results would help put your post(s) into context.

 

[plog]

You need a full outer join query (http://www.w3schools.com/sql/sql_join_full.asp). Good news, Access doesn't support them, so you have to resort to trickery. Here's how:

1. Make a UNION query (http://www.techonthenet.com/sql/union.php) of all your ISIN values from all sources.

2. Create a query using that UNION query and your 3 data sources. The 3 datasources will be LEFT JOINED to the UNION query (show all from the UNION query). Bring down the ISIN value from all 4 sources.

3. Run you query and sort Ascending by each of your ISIN columns. Any blanks, means that table doesn't have that value.

4. For extra points, use this query as a basis of another query which does the sorting/finding for you and returns only missing results.