Solved Multiple validations - cross values and columns - in one query

[spike_access]

Hi all,

Me again with a challenge that gives me gray hairs. Of course I can create multiple queries taking care of one scenario at the time but was hoping for a solution where I don't have to create a million separate queries.

I have two tables tblContentTracker and tblMeta in which I want to compare values and create a report where I have mismatches (see attachment for examples).
1. tblMeta contains the unique metaID value and multiple columns defining different compliances (column headings) that can contain an edit level ID value.
2. tblContentTracker contains metaID, compliance version (equivalent to tblMeta columns) and the edit level ID

The aim is to provide a report of records from tblContentTracker where we have a mismatch;

• metaID match between tables but where edit level ID from tblContentTracker doesn't exist on the metaID record in tblMeta (but exist on another metaID record)
• metaID match between table but where edit level ID from tblContentTracker doesn't exist at all in tblMeta
• metaID and edit level ID match between tables but where the compliance stated in tblContentTracker isn't the same value as the column heading where the edit level ID in tblMeta exist
• edit level ID match between tables but where metaID from tblContentTracker doesn't match the metaID on the record in tblMeta that holds the edit level ID

Additionally it would be good with an indication in the report what kind of mismatch it is, although that would just be icing on the cake.

Note: The current list of compliance versions contains 25+ different values/columns and is ever increasing, hence I would really benefit from a general query instead of per compliance. My example file only contains a couple of different.

Any ideas on how to create this query (that will ultimately be exported to Excel) would be greatly appreciated.

Best,

 

[arnelgp]

im not sure how to do it in query.
but i made a function and call it in the query.
see query1.

 

[spike_access]

This is bloody amazing @arnelgp! This is really really helpful - thank you!

 

[arnelgp]

test it first on larger dataset.