Solved Comparison with non-matches in query

[spike_access]

Hi,

I have a very specific query that I want run in order to identify mismatches.
The comparison is to be performed between two tables; tblContentTracker and tblMeta.

The key between tblContentTracker and tblMeta is the field MetaID/Meta ID and the comparison should be done between the edit level ID and compliance fields from tblContentTracker and the relevant compliance field from tblMeta. And here is where it becomes way to complicated for a simple minded hobbyist like me.

Every record in tblContentTracker has a specific compliance and edit level ID. However, the tblMeta table contains all possible compliance versions as a unique field (simply because a meta record can contain multiple compliances, but only one edit level ID per compliance) which holds the edit level ID valid for that compliance.

The end result I'm after is a query that identifies where;

• Assigned compliance in tblContentTracker has another edit level ID on the compliance in tblMeta
• Assigned edit level ID in tblContentTracker has another compliance with that edit level ID in tblMeta
• Assigned edit level ID in tblContentTracker doesn't exist at all in tblMeta
• Assigned compliance in tblContentTracker doesn't have an edit level ID on the compliance in tblMeta

So basically, any other scenario than a full match ([tblContentTracker.Edit level ID] and [tblContentTracker.Compliance] exactly the same as in tblMeta).

Have attached an Excel file with example data split up in three sheets;

• tblContentTracker
• tblMeta
• Query results
  • Shows the expected outcome of the comparison between the two previous sheets

This is most likely not the best setup data wise, but just as in many other cases I just have to accept the output :-(
Will highly appreciate any suggestions on how to perform this exercise.

Best

 

[MajP]

Make a union query to normalize your data

SELECT tblMeta.[Meta ID], "HUGM" AS Compliance, tblMeta.HUGM AS EditLevelID
FROM tblMeta
WHERE Not tblMeta.HUGM Is Null
UNION
SELECT tblMeta.[Meta ID], "CEGM" AS CompType, tblMeta.CEGM AS Compliance
FROM tblMeta
WHERE Not tblMeta.CEGM Is Null
UNION
SELECT tblMeta.[Meta ID], "NDGM" AS CompType, tblMeta.NDGM AS Compliance
FROM tblMeta
WHERE Not tblMeta.NDGM Is Null
UNION SELECT tblMeta.[Meta ID], "SPGM" AS CompType, tblMeta.SPGM AS Compliance
FROM tblMeta
WHERE Not tblMeta.SPGM Is Null
ORDER BY 1, 2, 3;

Then you can do whatever you want.
qryCompliance

qryCompliance qryCompliance

Meta ID	Compliance	EditLevelID
EP000012	CEGM	123456
EP000012	NDGM	HBON-ABCDE
EP001256	CEGM	456123
EP001256	HUGM	456321
FE000125	CEGM	654132
FE000125	HUGM	654133
FE000125	SPGM	HBOE-AADGT
SP000016	NDGM	165324
SP000016	SPGM	HBON-ACCYU

 

[MajP]

So using the union you can build QryAll to answer all your questions.

SELECT tblcontenttracker.unique,
       tblcontenttracker.[edit level id],
       tblcontenttracker.compliance,
       qrycompliance.editlevelid AS MetaEditLevelID,
       qrycompliance.compliance  AS MetaCompliance,
       tblcontenttracker.metaid
FROM   tblcontenttracker
       LEFT JOIN qrycompliance
              ON tblcontenttracker.metaid = qrycompliance.[meta id]
ORDER  BY tblcontenttracker.unique,
          tblcontenttracker.[edit level id],
          tblcontenttracker.compliance;

qryAllData qryAllData

Unique	Edit level ID	Compliance	MetaEditLevelID	MetaCompliance	MetaID
Hungary-74851	456123	HUGM	456123	CEGM	EP001256
Hungary-74851	456123	HUGM	456321	HUGM	EP001256
Nordic-16875	HBON-ACCYU	NDGM	165324	NDGM	SP000016
Nordic-16875	HBON-ACCYU	NDGM	HBON-ACCYU	SPGM	SP000016
Romania-14578	123456	CEGM	123456	CEGM	EP000012
Romania-14578	123456	CEGM	HBON-ABCDE	NDGM	EP000012
Spain-87413	125634	SPGM	654132	CEGM	FE000125
Spain-87413	125634	SPGM	654133	HUGM	FE000125
Spain-87413	125634	SPGM	HBOE-AADGT	SPGM	FE000125

 

[MajP]

Example where Compliance = metaCompliance and EditLevelID <> MetaEditLevelID, which I believe is the query results you want.

SELECT qryalldata.unique,
       qryalldata.[edit level id],
       qryalldata.compliance,
       qryalldata.metaeditlevelid,
       qryalldata.metacompliance,
       qryalldata.metaid
FROM   qryalldata
WHERE  ( ( ( qryalldata.[edit level id] ) <> [metaeditlevelid] )
         AND ( ( qryalldata.metacompliance ) = [compliance] ) );

Query1 Query1

Unique	Edit level ID	Compliance	MetaEditLevelID	MetaCompliance	MetaID
Hungary-74851	456123	HUGM	456321	HUGM	EP001256
Nordic-16875	HBON-ACCYU	NDGM	165324	NDGM	SP000016
Spain-87413	125634	SPGM	HBOE-AADGT	SPGM	FE000125

 

[arnelgp]

i created a function again and call it in Query1.

 

[spike_access]

Hi @arnelgp,
As your solution seemed a bit easier I tried that one, but when applying it to my db I get "Data type mismatch in criteria expression" when running the query. I've double checked and all the fields have the same type (short text) so not sure what the reason could be. Any idea?
Only thing I changed was changing metaID to MetaID.

Best

 

[arnelgp]

see my sample table in design view (they are all short text).
show me your table structure so i can adjust the code.
or much better if you can post those Two tables.

 

[spike_access]

Seems like I'm getting either -1, 0 or #Error values in the IsCompliant so when filtering on "False" I get the error. If I remove the "False" it works. Not sure how to filter the non-successful mappings... Tried Not "-1" as filter but that rendered an error.

 

[arnelgp]

"false" should be False (no quotes).

you should either use False or 0.

 

[spike_access]

Still getting an error when running the query I'm afraid. If I leave the IsComplied without any criteria it works, only when adding False or 0 it errors

 

[arnelgp]

you upload your db with that has tblContentTracker and tblMeta.

 

[spike_access]

Here's a snapshot of the db

 

[arnelgp]

see the function in module1.
right now when "Compliance" field is blank (Null), it will return True.
change the function if you like.

 

[spike_access]

Perfect @arnelgp, thank you so much!
Wouldn't have been able to pull this through without you. Not the first time you've been my hero (and probably not the last one either).

Very much appreciated!