Solved Compare data from two documents (1 Viewer)

[lacampeona]

Hello experts
I put the topic in General becouse I dont know where is the correct area for that.

I have some question if something is possible to create in microsoft access. Maybe some form something.


Problem:
I need to compare imagine 200 fields with another 200 fields with numbers to check if the data is written correctly.

So we have lets say on the left side data that is correcly written ( checked manualy from some original document) and then I have the same data copied to the right side or to the multiople tables in the same document.

What we normally do when we have to check the data is that we visualy check with our eyes? one by one? Maybe I am doing that wrong?

I know that comparing contents is possible in Microsoft word where you can compare original and revised documents. I have to say that is very good if you have documents.

But sometimes you have selected area of numbers and you need just to revise that. I try by copying the selected numbers into one and second document and then using the microfoft word feature compare. It is working but in my way of doing that i notice that I lost some time ( not a lof ) in copying and then saving to the word and them comparing.

Now I am wondering and thinking if it possible to create some microsoft acccess database let say some form, that will contain some field where you will just copy the data you need to compare and then you will see the errors in another field.
I am attaching how is that make in ms word for better unerstanding.
I have a challenge to see if something like that is possible to make-
thanks in advance

 

[MarkK]

If you have data you know is correct, why compare? Just write a result from the correct set.

It's like you have a working car, and now you want to drive it to a broken car so you can fix the broken car so you can do what? You already have a working car. Just drive the car that works.

 

[lacampeona]

hi
becouse when you are preparing documents...reports...you have to write results or name od the test in multiple tables...
how you check that?
imagine you write the names and date of births of 1000 persons? how you check if you write that correct in 5 tables ( some results for some people )on diferent places on your 40 pages document?

 

[lacampeona]

you have some document paper in your hand....then you have to write that in some report....you have to check name...date of birth....you make one table from that document....then you make another table with the same names but difenret results..so you have to make 10 tables with the same names or more...
how to check every table? one table is correct becpuse i checked it visually one by one...and then i also have to check the next 50 tables ...one by one...i cant have errors.....
so i am wondering if it is possible then to create some form that will know how to compare the correct data with another set of data that i copy and paste,.....

 

[MajP]

I have used this in the past with disperse documents and data. Worth the money.

Scooter Software - Home of Beyond Compare

Scooter Software: Home of Beyond Compare

www.scootersoftware.com

 

[The_Doc_Man]

The hardest part will be alignment of the comparison. Components of this "hardest part" will be how you access the items (on each side) that you wish to compare. Are we talking about two documents of tabular text? Are we talking two spreadsheets? Two documents of different type where one is numeric and the other is text?

The trick will always be that you have two lists. If they match up in terms of that to which they apply - i.e. they are BOTH lists of some type where the order of appearance is the same regardless of how many rows there are - then you could write a FOR index = first-row TO last-row (providing numbers of course). Or there are other ways. However, ...

I don't understand why you don't take the once-validated data and propagate it to the other documents. Don't enter it each time and compare. If the first document is now your authoritative list, programmatically do the copy/paste and don't worry about it matching up.

 

[lacampeona]

Hi MajP
thank you I will look for free trial
So do you think is possible to make somethng like that in ms access?

 

[lacampeona]

Hi Doc Man
I am talking about the checking some results in tables most of them are names and numbers..
something like that

 

[cheekybuddha]

Problem:
I need to compare imagine 200 fields with another 200 fields with numbers to check if the data is written correctly.

Can you show how your tables are set up?

 

[cheekybuddha]

SELECT
  t1.Name,
  t1.Results,
  t2.Results AS ResultsToCompare,
  IIf(t1.Results = t2.Results, 'Same', 'Different') AS [Check]
FROM t1
INNER JOIN t2
        ON t1.Name = t2.Name
;

 

[lacampeona]

In my work i am responsible for lot of test and resutls. I cant have errors in writing the names of the test or results.
Normally i receive some paper docments with names of the samples...and their lot numbers.... i make the analysis and then i have to report the results.
what i do now is:
form the document in my hand i make some table...enter the names of the test and lot numbers..i visually check if that is correct..then i add some columns with the results to the corresping lot number....and i have 5 different anaylsis for the same lot numbers...i have to compare if in on my tables the names, lot numbers are correct and if my results are also copy and pasted correctly from the row data that gives me the insrtrument

 

[MajP]

i have to compare if in on my tables the names, lot numbers are correct and if my results are also copy and pasted correctly from the row data that gives me the instrument

I still am confused in where the data is and what is being compared. The image was a document. However, the quote above sounds like a potential database normalization problem. The names and lot numbers should likely only be entered in one place in the database and all other tables reference the Primary Key to this table.
Beyond compare works great with comparing code, spreadsheets, text documents, and other files. If this is a pure database question, then of course SQL can do a lot or you can write code.

Here is one I did to compare tables looking for differences across an unlimited amount of fields and handling Null values.

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

 

[MajP]

Can you post those images as excel tables, and then tell us if you were to compare the tables what results you would want to see?

 

[MajP]

That can be done in pure sql queries. Do a left outer join from table on to table 2 on the first column. Identify anything that does not match and fix it. Then do a left outer join on columns 1 and 2 and fix anything that does not match.

 

[lacampeona]

Hi yes here is the excel

 

[MajP]

However those two fields should be entered into your database Once and only once. Then all other tables should reference the PK to those records. They should not retype that data. They should have foreignkeys like 1,2,3,4 and that is it. Then use a query to bring those columns back in.

 

[MajP]

Hi yes here is the excel

you forgot the attachments.

 

[lacampeona]

I dont have that in my database
I am havinf that in ms word and ms excel....
i am wondering if it possible to make some form in ms access that will know how to compare..
lets say i will copy the data from excel into ms acces form and ms access will find the errors ....

 

[MajP]

If I understand (which I think I do not), the data is arriving in Excel. If the data is in Excel you can do this validation directly in Excel. Make each a table in Excel and then you can do that directly in Excel.

Excel: Compare two columns for matches and differences

See how to compare 2 columns in Excel and how to compare and match two lists with a different number of columns.

www.ablebits.com

Or use power query in Excel.
You could link to the Excel tables and do it in a query.

Still very confused how the data comes in, where you store it, where you export it.
But the code I posted should work. You could link to two excel tables and run the compare. Unfortunately that was designed to show matches and not the differences. It could be modified for your case to show what does not match.

This can be done, but you will have to show some real examples are clearly explain how the data comes in, in what format, where are you comparing, and what are you doing with results. A real example in the real format would be the most useable.

 

[ebs17]

Comparison of two tables

A first helpful step should be to find out those records where there is no identical match.

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