Report to show differences between two records (1 Viewer)

[Futures_Bright]

Hi all,

I'm currently working on a report to show the differences between two records (in this case, Documents). Each Document contains a number of paragraphs which are separate objects within my database. The purpose of this is to narrow down which paragraphs require reading (i.e. remove/highlight which are duplicates).

I've set up the report to show only the paragraphs from two selected documents (I don't think there is need for more than two, but if anyone has a reasonably easy solution for how to select two or more documents for the query I would be interested), and currently I have set the 'Hide duplicates' which kind of works but not the result I was hoping for - it only hides the related field and leaves the original in tact.

I am looking for a method that hides both duplicate records (not just the duplicated fields) if possible, failing that a method that will conditionally format the remaining duplicate or all duplicates. I'm trying to steer clear of the conditional formatting as I've read this can take some time to complete - but if it gives the desired result then I am happy to do so.

Any help is much appreciated!


Kind regards,

Michael

Edit: Looks like the Hide Duplicates hasn't quite worked either - I have two fields which are clearly different and it has hidden the longer one. Likewise another later paragraph is not duplicated and it has hidden another which isn't duplicated. If it makes any difference the field is of type 'Memo'

 

[Futures_Bright]

Okay this is frustrating me now - I've spent a large portion of today trying to make this work with zero success.

It might be best if I start again from basics. The need for this report is where documents are largely the same, allowing the users to quickly locate the 'new'/different sections. This is not for comparing to the previous version, it is against a separate document. The preferred solution would be to remove all records where the [Text] is the same - based on what I've read this is tricky because [Text] is not the only thing in the Detail section of the report (we need to see which document it is from). The next best thing would be to 'grey out' anywhere the [Text] is identical.

I can't for the life of me work out why the 'Hide Duplicates' property is not working for this - it leaves records where [Text] is literally identical from start to finish, and then hides a record which starts the same as the other document, but is considerably longer (i.e. clearly not a duplicate) than the other. Does 'Hide duplicates' not work with the 'Memo' field type?

I've also tried a solution based on a link (which I have since shut and can't find again) which highlights both duplicates in a form based on the below code:

fnFormat = 0
TheCount = DCount("[TheField]", "tblTest", "[TheField] = '" & strField & "'")
If TheCount > 1 Then fnFormat = 1
End Function

and the following in conditional formatting:

fnFormat([TheField])>0

This does not work either - because I am using it in a Report? As fnFormat highlights if there is any other identical records I was hoping this would be the basis of my solution.


... Help?

 

[JHB]

Show some data and which result you want.

 

[Mihail]

Is this what you are looking for ?

 

[Futures_Bright]

Hi Mihail, that is sort of what I'm looking for although I have some reservations - there is currently about 43 documents (with more to be added) in the database and over 2750 paragraphs - meaning the query will be extremely large!

Where does the CompareParagraphs function in the query come from - is it SQL? I've not come across this before - is there a limit to the length of string it will compare (as I believe the weakness of the 'Hide Duplicates' property to be)? If it is SQL I might be able to build a statement in VBA.

@JHB, all of the data is copyrighted so I'd rather not publish it on here. To try and explain the reason for this report there are a number of industry standard documents with different scopes - meaning the majority of the text (paragraphs) is identical, but some will be different (e.g. 'this section does not apply', or the same text to begin with but more text at the end - as was the case with the one described in the OP edit). The solution I would like is to narrow down a document to only the paragraphs which may be different (i.e. don't show either record if another one is found to be identical).

If the solution isn't as easy to come by as first hoped I might instead compare lengths and highlight (not hide in the unlikely event that they are not identical but have the same number of characters), or look instead at a way of exporting to Excel which should handle the highlight/remove duplicates a bit better (and can leave a list of the paragraph numbers to look at in each document).

 

[Mihail]

Hi Mihail, that is sort of what I'm looking for although I have some reservations - there is currently about 43 documents (with more to be added) in the database and over 2750 paragraphs - meaning the query will be extremely large!

No problem with the size
But I think that you wish to compare documents one by one not one against all others. Do you ?

Where does the CompareParagraphs function in the query come from - is it SQL? I've not come across this before - is there a limit to the length of string it will compare (as I believe the weakness of the 'Hide Duplicates' property to be)? If it is SQL I might be able to build a statement in VBA.

If the query do what you need (must be tested with real data for only 2 documents, I'm pretty sure that we can find a way to automatize the whole process.

@JHB, all of the data is copyrighted so I'd rather not publish it on here.

JHB asked fore "some" data. Not for real data but for realistic data. This help us to better understand the problem.

If the solution isn't as easy to come by as first hoped I might instead compare lengths and highligh

And how you plan to do this if you don't know how to design the queries ?

or look instead at a way of exporting to Excel

This is a simple task. A little bit on Google and you can do this.

which should handle the highlight/remove duplicates a bit better (and can leave a list of the paragraph numbers to look at in each document)

Maybe but I have huge doubts.

Cheers.

 

[Futures_Bright]

Hi Mihail,

Sorry you are correct, comparing two documents at a time (Monday morning - confused it with a different report!), so yes I think it might work as your example has set up (provided no length limits) and a form listing the two documents to choose (which I have already set up). I'll implement this and report back when I get a spare few minutes.

Edit: From a brief look it appears to work. The last think I need to clear up to get the exact result I am after is to ensure the clauses where there is any duplicates are excluded. Your database did this by comparing where the paragraph number is equal, this can't be guaranteed in these documents, but i have a 'categorisation' which makes it more manageable; it is however possible to have 2 or more clauses in 1 document with the same category.

At the moment I'm thinking set the query to only find duplicates, and use this to tell the report which paragraphs to not show. I probably won't be able to test this until tomorrow/Wednesday so if anyone can think of a better solution please let me know.

 

[Futures_Bright]

Okay, I've finally got around to taking some time with this report and it does work where the Clause Numbers are the same. I've tried using 'Sub Category' as the link instead and this works better (meaning a different order can be used) however it isn't a perfect solution due to the fact that 1 document could have a number of clauses relating to one sub-category - meaning this compares each related one to each other.

As some simple attached data, see attached. I'd like to get a report showing ONLY the non-duplicate entries.

At the moment I think I could do this with the onPaint command (adding the Clause ID's - autonumber primary key not included in example data - to a Collection and making the height of anything associated zero); but that solution is a bit messy - can anyone think of a cleaner solution?