Unmatch query?

[zz12]

Hello. Would anyone know of an easy method in trying to find unmatching
records in 2 different tables that have the same table structure? A user
had been updating a different copy of the same .mdb file and now have to try
to find which records and how to merge the data in keeping the integrity
since I notice the data have same ID fields but different text. For example
an Employee table that have EmployeeID and EmployeeName as fields in where
table A could have EmployeeID=100 and EmployeeName=Albert and table B will
have EmployeeID=100 and EmployeeName=Bob.

I was thinking of concatenating all of the fields in a query as 1 large
column and doing a unmatching query based on this 1 super column but this
doesn't seem doable if the table contains numerous fields. Would anyone
know the best way to approach this in merging the 2 tables in keeping the
data integrity?

Thanks in advance.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CompareTwoTables.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[zz12]

Wow, this looks pretty cool. Will look into it further. Thanks a bunch
Roger for your helpful reply

 

[zz12]

Just encountered something else in would you happen to have any samples or
recommendations for tables that don't have a primary key field? I notice
some of the other tables in our .mdb file have tables that don't have a
primary key ID fields so was wondering how would one go about comparing the
tables with this table structure? Thanks Roger.

 

[zz12]

Sorry hopefully this would be the last question in regards to your sample
Roger is that it seems that Module2 doesn't succeed entirely if there are
data that contains an apostrophe since it displays 'Syntax
error (missing operator) in query expression ...' at the following section:

" VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" &
rstVarying(fld.Name) & "');")

{For example if: rstVarying(fld.Name) = 'Bob's'}

I've been tweaking this by using different combinations of apostrophes,
double quotes, double apostrophes but can't seem to get it to succeed when
the data has an apostrophe value. Would you happen to have any ideas or
advisements?

Thanks Roger.

 

[John Spencer]

Without havng the entire code to look at, you MIGHT be able to use the
replace function to double the single "quote" marks to two single "quote"
marks

" VALUES ( '" & rstVarying(PrimaryKeyField) & "','" & fld.Name & "','" &
rstVarying(Replace(fld.Name,"'","''") & "');")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Roger Carlson]

Whups. I usually think of that. Replace each apostrophe with TWO quote
marks:

" VALUES ( """ & rstVarying(PrimaryKeyField) & """,""" & fld.Name & ""","""
&
rstVarying(fld.Name) & """);")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

There has to be SOME basis for identifying unique records if you're going to
compare tables. Otherwise, no comparison is possible. In my sample, I
chose the simplest case, i.e. a single primary key field. If you don't have
a primary key established, you can still use any single field as long as it
uniquely identifies a record. (BTW, if you have one, you should at least
create a unique index on it if not a primary key).

If you have a *combination* of fields that uniquely identify the records,
then things get more complicated. If you had two fields (say LastName and
Firstname, for example), you could modify the routine to accept parameters
like this:

Sub CompareTables(BaseTable As String, KeyField1 As String, _
KeyField2 As String, BaseTableQuery As String, _
VaryingTableQuery As String)

Then everywhere you would use

rstBase(PrimaryKeyField)

for comparison, you would replace it with:

rstBase(KeyField1) & rstBase(KeyField2)

which concatenates the two values and will allow the comparison.

Unfortunately, this would require separate routines for tables with 2 or 3
or 4 fields in the combination. Off hand, I can't think of a nice, general
solution.

HTH.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[zz12]

That fixed it. Thanks a ton Roger for your samples and insightful replies
and John also. Totally appreciate it. You guys are awesome. Take care
guys