Solved

Report Comparrison

Posted on 2002-05-11
9
187 Views
Last Modified: 2010-05-02
I have an application where I load 2 reports from a database each into its own list view box.

I need to be able to compair the two reports and show the differences found in the 2nd report.

How I show the differences is not important at this time.

What would be the best/most efficient way of doing this?

Should I load each listitem from each listview into the dictionary object and compair them that way?

or would it be better to do an Instr vbCompare while loading the 2nd listview?

I am open to suggestions.

I need what will be the most efficient way as some of the reports can have over 1000 rows.

Thanks,

Dave
0
Comment
Question by:raizon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 15

Accepted Solution

by:
ameba earned 200 total points
ID: 7003400
It is better to work with raw data. This is not efficient:
   data >> listview >> collection

This is better:
   data >> listview
   data >> collection (or array)

To find difference, or common elements, see:

"create a common array" (10pts)
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20292440
contains GetCommon() function

"Comparing listbox contents" (10pts)
http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20266563
contains my GetDiff() function
0
 
LVL 2

Expert Comment

by:selim007
ID: 7003441
hi,
as i can c there's 2 cases, i am not sure which one is urs
- case1: is that you are looking for differences between the same index from the 2 lists for example:
list1.list(1) should be equal to list2.list(2)
this case is quiet easy:
for p=0 to list1.listcount -1 'assuming that the 2
'lists have the same number of items, or u
'u can write a line to get the max number
if list1.list(p) <> list2.list(p) then
' here you write the code that shows the
'mismatching result
end if
doevents
next p
- case2: items r not necessary in the same row.
in this case you should load ur list in a array or use collections as ameba mentioned
0
 
LVL 5

Author Comment

by:raizon
ID: 7003450
its case 2

the two lists are directory scans ex: c:\winnt\system32

The comparrison is to show what has changed
ex.  file not there, modified date, version number, etc.

This is so for example the sys admin can see whats changed on a server since the last report ran after installing a new driver, service pack, software ...etc.

I can load the report into a collection or an array my question then becomes do I concantinate each subitem/field in recordset into a single string then load that into collection/array and compair off of that?

or load it into a multidimentional array?
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 15

Expert Comment

by:ameba
ID: 7003494
I don't know if you checked my samples, but they all use the fact that "collection cannot hold items with the same key."
0
 
LVL 46

Expert Comment

by:aikimark
ID: 7003498
Take a look at DLSuperC.com.  It has the ability to compare directories quickly and efficiently and has a command line interface.  You could launch the utility and then process the results.

=======================================================
You could use the Windows ?GetFiledata? API to rapidly extract the path and filedatetimestamp from all related directories.  Then you could rapidly do a merge-comparison between the prior snapshot and this one.
0
 
LVL 5

Author Comment

by:raizon
ID: 7004062
ameba,

looking at your GetDiff() Function.

Looks like this might work or at least get me on the right track.

Will post back tomorrow with further updates.

Thanks,

Dave
0
 
LVL 46

Expert Comment

by:aikimark
ID: 7004583
If you fed the file (with path) and timestamp data into columns in a table in your database, you can run three queries that will quickly tell you the difference between the before and after snapshots.

Query 1 (changed files):
Select A.PathFilename, "Changed On:" + A.DateTimeStamp As
Status
From tblBefore As B Inner Join tblAfter As A ON B.PathFilename = A.PathFilename
Where B.DateTimestamp <> A.DateTimeStamp;

Query 2 (deleted files):
Select B.PathFilename, "Deleted" As Status
From tblBefore As B Left Join tblAfter As A ON B.PathFilename = A.PathFilename
Where A.DateTimeStamp Is Null;

Query 3 (added files):
Select A.PathFilename, "Added" As Status
From tblBefore As B Right Join tblAfter As A ON B.PathFilename = A.PathFilename
Where B.DateTimestamp Is Null;

=========================================
You can Union these three queries to create one report.
0
 
LVL 5

Author Comment

by:raizon
ID: 7004869
Thanks for the help everyone and the ideas.

I'm using a variation of ameba's GetDiff function.
0
 
LVL 15

Expert Comment

by:ameba
ID: 7004970
Thank you for the points!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month5 days, 11 hours left to enroll

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question