Comparing tables in report

[raybacosta]

Greetings! This might just be a simple fix but thought to still ask. I have 2 tables that I want to compare each field and find missing or non-matching values. One has 147 fields and the other 47. Both have 47 fields in common. My problem is the comparison is 1 to many.

My current solution is to create a report and bring the common fields in the same report comparing 1:1 but then I have many pages where there's repeating data per each row comparison to find a field that might be off.

Anyone able to offer a better solution?

 

[Pat Hartman]

If the relationship is 1-m, then you compare all pairs. Of course having the duplicate data is a problem to begin with. Hope this is part of the process of removing the duplicate fields.

 

[raybacosta]

Yeah... That's been the report so far. Comparing all pairs but with a lengthy report.

 

[plog]

There's "Access Reports" which are specific Report objects in Access. And then there are "reports" which is a generic term for usable data and can take the form of Excel, text file, queries or even Access Reports. You do not need an Access Report for this, just queries. You just need something to spit out data that you can view. An Access Report is overkill for this unless you plan on presenting it to some one who needs formatting.

Let's call your tables A and B and all their fields AF1, AF2, AF3...,BF1, BF2, BF3... Then to accomplish what you want this is what you should do:

1. Create 2 queries, one based on A one based on B like so:

subA = SELECT DISTINCT AF1 AS FieldA FROM A
subB = SELECT DISTINCT BF1 AS FieldB FROM B

2. Bring those 2 queries into 2 new queries and LEFT JOIN them like so:

QA = SELECT FieldA, FieldB FROM subA LEFT JOIN subB ON FieldA = FieldB
QB = SELECT FieldB, FieldA FROM subB LEFT JOIN subA ON FieldB = FieldA

QA will show you all the values in A. If FieldB is null it means that data is not in B, if FieldB has a value it means the data is in both A and B.

QB will show you all the values in B. If FieldA is null it means that data is not in A. If FieldA has a value it means the data is in both A and B.

That does 1 one field. When you want to move onto the next field to compare between the tables you just need to edit subA and subB to the new fields like so:

subA = SELECT DISTINCT AF2 AS FieldA FROM A
subB = SELECT DISTINCT BF2 AS FieldB FROM B

Save those changes and you can just run QA and QB and they will work for those new fields so you can determine which values match.

 

[ebs17]

Example for 3 fields

SELECT
   MIN(T) AS S, A, B, C
FROM
   (
      SELECT
         "Table1" AS T, A, B, C
      FROM
         Table1
      UNION ALL SELECT
         "Table2", A, B, C
      FROM
         Table2
   ) AS X
GROUP BY
   A, B, C
HAVING
   COUNT(*) = 1

Records that do not have an identical partner in the other table are shown. Hopefully you don't use memo fields.

 

[Pat Hartman]

We have no idea what the purpose of the compare it. If it will result in deleting individual rows from the many-side table, then you can't summarize them away ahead of time.

 

[MajP]

I have 2 tables that I want to compare each field and find missing or non-matching values. One has 147 fields and the other 47. Both have 47 fields in common. My problem is the comparison is 1 to many.

This can be interpreted very differently and could impact the complexity of any solution
Lets assume table A has 147 fields and B has 47 fields.

1. Do you know which records in A relate to records in B? Is there a PK and FK? Or are you trying to determine potential related records based on matching 47 fields. That would relate to this

More than 15 left joins for matching rows

@Jarek The famous quote by Donald Rumsfeld (US Secretary of State under George Dubya Bush) may come in useful here: As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But...

www.access-programmers.co.uk

2. You say this is one to many. In that case I would assume there is then a PK to FK relationship. Are you saying there are many child records with 47 similar fields and for some of these child records the fields match? I am having a super hard time wrapping my head around this.

The proposed solutions do not seem to be scalable if what you are describing is the case. Nor would I think that they give you what you what you want. Lets assume there is some kind of PK and FK relationship. I would think you do this in code.

Read each record in A
  Find matching Records in B (assuming PK to FK)
   loop all fields in A
      Find matching Field in B
           Compare values
                write to log table (FieldName, table A PK, Table B PK, Table A Field Value, Table B Field Value)
         Next Field
      next record in B

The code to do this is pretty simple, but not trivial.

then you would have something like

EmployeeName  1, 66, Smith, Smyth
StartDate             1,66, 1/1/2021, 1/1/2022
EmployeeName  2,  88, Jones, Jons

This says record 1 of table A relates to record 66 of table b and the Employee Name field is Smith in A and Smyth in B

I interpret this line to me find and identify the differences and not simply identify completely matching records.

I want to compare each field and find missing or non-matching values