Open two databases to compare data and generate report

I think I have both DBs open but how do you write the WHILE statement to retrieve the data.

All the results for the 2nd database field are exactly the same.

Code is attached.
WHILE.txt

Eventually I will be comparing the two pieces of data and if they are different the cell will be highlighted.

since you have 2 recordset objects, objRS2 and objRS2, populating, outputting the data in a WHILE loop is simply a matter of which object to loop through:

'-- first loop
While Not objRS.EOF
counter = counter + 1

	Response.Write "<TR><TD class='CCTable' scope='row' width='25px' valign='top'>" & counter & "</td>"
	Response.Write "<TD class='CCTable' scope='row' width='200px' valign='top'>" & objRS("City") & ", " & objRS("State") & VbCrLf
	
	Response.Write "<TD class='CCTable' font-size='44' scope='row' width='100px' valign='top'>" & objRS("VISNID") & "&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS("Grade1") & "<br>" & objRS("Title1") & "</b>&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS2("Grade1") & "<br>" & objRS2("Title1") & "</b>&nbsp;</TD></TR>" & VbCrLf



'-------------------------------------------------------------------------------


   'Advance the recordset
   objRS.MoveNext

Wend
objRS.Close
Set objRS = Nothing

'-- second loop:
counter = 1
While Not objRS2.EOF
counter = counter + 1

	Response.Write "<TR><TD class='CCTable' scope='row' width='25px' valign='top'>" & counter & "</td>"
	Response.Write "<TD class='CCTable' scope='row' width='200px' valign='top'>" & objRS2("City") & ", " & objRS2("State") & VbCrLf
	
	Response.Write "<TD class='CCTable' font-size='44' scope='row' width='100px' valign='top'>" & objRS2("VISNID") & "&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS2("Grade1") & "<br>" & objRS2("Title1") & "</b>&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS2("Grade1") & "<br>" & objRS2("Title1") & "</b>&nbsp;</TD></TR>" & VbCrLf



'-------------------------------------------------------------------------------


   'Advance the recordset
   objRS.MoveNext

Wend
objRS2.Close
Set objRS2 = Nothing

Select allOpen in new window

if you only want 1 output loop, then you would do (assuming the same number of records exist in both recordsets):

While Not objRS.EOF and not objRS2.EOF
counter = counter + 1

	Response.Write "<TR><TD class='CCTable' scope='row' width='25px' valign='top'>" & counter & "</td>"
	Response.Write "<TD class='CCTable' scope='row' width='200px' valign='top'>" & objRS("City") & ", " & objRS("State") & VbCrLf
	
	Response.Write "<TD class='CCTable' font-size='44' scope='row' width='100px' valign='top'>" & objRS("VISNID") & "&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS("Grade1") & "<br>" & objRS("Title1") & "</b>&nbsp;</TD>" & VbCrLf

	Response.Write "<TD class='CCTable' scope='row' width='150px' valign='top'>" & objRS2("Grade1") & "<br>" & objRS2("Title1") & "</b>&nbsp;</TD></TR>" & VbCrLf



'-------------------------------------------------------------------------------


   'Advance the recordset
   objRS.MoveNext
   objRS2.MoveNext

Wend
objRS.Close
Set objRS = Nothing

Select allOpen in new window

One of the issues is the number of records in the tables. The common field in each table is the ChartID. There is a table (tblGeneral) in each database that has the "master" ChartIDs with the city, state, etc. Both databases match in the tblGeneral.ChartID.

In this report I do want to include ALL ChartIDs from both databases if they appear in either of the tables (from archive.mdb or the current.mdb). Will this be possible?

I am including a screenshot of what the report will look like (except for highlighting the rows where there are changes).
report.PNG

will there be duplicate Chart IDs across the 2 tables, or will the be unique? if there are duplicates, do you want to show them as well?

The ChartIDs are unique. There are no duplicate ChartIDs in the same table.
To make sure I am clear, one database table may have ChartIDs that are not in the
other database table and vice versa. For example ChartID=112=Chicago is the same
in both databases.

Make sense?

Just a note here...
Typically there is little need to actually "open" two database just to pull data form the two.

You can create a third database and "Link" the tables from the original two databases, into this third database.
Then,  you only need open this one database...

if ChartID exists in one database, CAN it also exist in the second database? if so, do you want to display it twice?

No, I don't want to display twice.
Database1 is the database containing current data.
Database2 is an archive copy of the current database with the same tables and fields.

I need to do a comparison to see how things have changed between the current and the archived data so I am comparing the tblStaff.Grade1 field in database1 with the corresponding tblStaff.Grade1 field in database2.

But there will be instances where database2 has ChartIDs in the tblStaff that are not in database1. The ChartIDs will be in tblGeneral where all ChartIDs exist.

The only other option I was thinking of was to create a new table in the current database and copy the data from the archived data and do a comparison that way. I would just have to compare the records and make sure the ChartID data matches up.

displaying the contents of each table from each database isn't difficult, I already gave you an example on how to do that. I'm just having difficulty understanding what else you need to get you what you need.

Sorry I missed the objRS2.MoveNext statement. So it is now pulling data from archive.mdb and is looping through the records. But since there are some ChartIDs in one table and not in the other table it is not matching up the ChartIDs correctly.

Do I need a 3rd SQL statement linking the current.mdb.tblGeneral.ChartID to the archive.mdb.tblGeneral.ChartID

you would need to create linked tables to connect the two databases. otherwise you have no way to reference the 2 sets of data, unless you put each into its own array and do the comparison there, but that would get tricky if you're not good with arrays

After reading several posts, I tried linking the tables with this statement:

Dim sql, objRS, counter
counter = 0

sql = "SELECT comparison.tblGeneral.ChartID, comparison.tblGeneral.City, comparison.tblGeneral.State, comparison.tblGeneral.VISNID, " & _
      "comparison.tblStaff.Grade1+'<bt>'+comparison.tblStaff.Title1 AS Current, " & _
      "comparison2008.tblStaff.Grade1+'<bt>'+comparison2008.tblStaff.Title1 AS 2008, " & _
      "FROM (comparison.tblGeneral FULL OUTER JOIN comparison.tblStaff ON comparison.tblGeneral.ChartID= comparison.tblStaff.ChartID) " & _
      "FULL OUTER JOIN comparison2008.tblStaff.ChartID = comparison.tblGeneral.ChartID "


      If Request.querystring("sort") = "" then
            sql = sql & "ORDER BY City,State"
      Else
            sql = sql & "ORDER BY " & Request.querystring("sort")
      End If                  
      
      Set objRS = Server.CreateObject("ADODB.Recordset")
      objRS.Open sql, objConn

But this generates an error '80004005'

Am I on the right track?

to be honest, I don't know much on linking access tables together, as I've never had a need for it. I did a
quick google search on that error number and a possible cause could be due to permissions.do you have a lot of records for each data set (say, over 50)? if not, can you just print out each recordset individually and then eyeball the comparisons? or does it need to be side by side? if you do need to print out each section, let me know the steps you took in linking the 2 databases and I'll see if I can figure something out for you if no other experts have any ideas.