By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,402 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

How to compare fields from similar tables

P: n/a
Hi everybody-

I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the new database. For instance,
her old database has a table with Band Info in it. Her new database
also has a table with Band Info in it but slightly different. I was
wondering if there was an easy way to compare the fields from similar
tables in the old database with fields in the new database to see if
they match or don't match. Date fields and fields with strings are the
types of comparisdons we're looking to make. Or is code needed?

Thanks,

Megan
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
In message <5c**************************@posting.google.com >, Megan
<me**************@hotmail.com> writes
Hi everybody-

I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the new database. For instance,
her old database has a table with Band Info in it. Her new database
also has a table with Band Info in it but slightly different. I was
wondering if there was an easy way to compare the fields from similar
tables in the old database with fields in the new database to see if
they match or don't match. Date fields and fields with strings are the
types of comparisdons we're looking to make. Or is code needed?


You can create a query including data from two tables where, for
instance, the name of the band is identical in both tables. The
difficulty is that they do have to be completely identical, the computer
can't tell if the difference is only because of a typing or punctuation
mistake.

You can create a query that links two tables and set the selection
criteria so that it only includes records where the album names are
identical but the band names are different, or vice-versa.

You can also use the IIF() function in calculated fields.

Diff:iif(fieldA=fieldb, "Same", "Different")
--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #2

P: n/a
Thanks for the ideas!

Maybe if I provided an example of what I want to do, then you'll be
able to understand what I'm trying to do.

Old Bands Table
BandID (Primary Key, Number)
BandName (Text)
CDDate (Date)

New Bands Table
BandID (Primary Key, Number)
BandName (Text)
CDDate (Date)

The two tables are almost identical, but the newer/ more recently
created table (New Bands Table) has more fields than I listed and has
more fields than the old table.

The BandIDs for both tables are identical (for example: 1 = Eric
Clapton, 2 = The Beatles, etc...).

I want to compare the BandNames from both tables to see if there are
any differences or accidental misspellings. For example: Eric Clapton
versus Eri Klapton. Hopefully, the query would tell me the BandIDs
that have different spellings in the BandName fields. For example, the
query would return BandID = 55 because Led Zeppelin in Table 1 is
spelled differently than Led Zeplin in Table 2.

I also want to compare my date fields to see if I mis-entered a date.
For example:

Table 1:
BandID = 7
CDDate = 1/1/1998

Table 2:
BandID = 7
CDDate = 11/11/1998

I know how to create queries, but how would I compare fields from
these 2 similar tables because I don't have a relationship between
them?

Do I need to use any special string comparison functions to compare
the text from 2 different fields?

I know I might be getting off subject, but another question comes to
mind. In my old table, I have an artists name in this format: last
name, first name. I used 1 field for the entire name.

For example:
Clapton, Eric
Spears Brittany

In my new table, I have separate fields for the artists last name and
first name. Is there any way to separate the last name and first name
in my old table and append the last name and first name to new fields
in my new table?

For example:

Table 1:
Name = Clapton, Eric

New Table:
LastName = Clapton
FirstName = Eric

I'll post this in another separate post.

Thanks,

Megan

Bernard Peek <ba*@shrdlu.com> wrote in message news:<4H**************@shrdlu.com>...
In message <5c**************************@posting.google.com >, Megan
<me**************@hotmail.com> writes
Hi everybody-

I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the new database. For instance,
her old database has a table with Band Info in it. Her new database
also has a table with Band Info in it but slightly different. I was
wondering if there was an easy way to compare the fields from similar
tables in the old database with fields in the new database to see if
they match or don't match. Date fields and fields with strings are the
types of comparisdons we're looking to make. Or is code needed?


You can create a query including data from two tables where, for
instance, the name of the band is identical in both tables. The
difficulty is that they do have to be completely identical, the computer
can't tell if the difference is only because of a typing or punctuation
mistake.

You can create a query that links two tables and set the selection
criteria so that it only includes records where the album names are
identical but the band names are different, or vice-versa.

You can also use the IIF() function in calculated fields.

Diff:iif(fieldA=fieldb, "Same", "Different")

Nov 13 '05 #3

P: n/a
In message <5c**************************@posting.google.com >, Megan
<me**************@hotmail.com> writes

I want to compare the BandNames from both tables to see if there are
any differences or accidental misspellings. For example: Eric Clapton
versus Eri Klapton. Hopefully, the query would tell me the BandIDs
that have different spellings in the BandName fields. For example, the
query would return BandID = 55 because Led Zeppelin in Table 1 is
spelled differently than Led Zeplin in Table 2.
OK. That's reasonably easy to do, it's not quite as easy to describe in
plain text. For the purpose of this explanation I am assuming that you
know very little about how to use Access. Bear in mind that these posts
are read by other people with less experience than you have.

What you need to do is to create a query which links the old table and
the new table. Create a new query in design view, add both the old and
the new tables to it. I assume that you have both tables in the same
database file, or can use linked tables.

Link the BandID of the old table (Table1) to the BandID of the new one
(Table2) by dragging and dropping between the BandID lines in
representations of the two tables. Access will draw a line showing the
relationship. Double-click on it to show the properties of this
relationship. It should say that you want to include all records where
the two values are equal.

Add all of the fields in the old table to the query, by double-clicking
on the asterisk shown at the top of that table.

Create an alias for the BandName in the second table. In the list of
fields included in the query type another entry. Type the alias name,
for instance NewName followed by a colon (:) and the name of the field
in Table2.

Underneath that look for the line labelled Criteria. In that box,
underneath your new alias entry, type "<> [BandName]" but without those
quote marks.

What this does is it restricts the query to returning only the entries
where the BandName in Table1 id different to BandName in Table2.

Save the query and switch to table view. You will now see a list of all
of the query records that meet two criteria. The first is that BandID in
Table1 and Table2 are identical, and the second is that the BandName
fields are different. You can edit the data in this query, and that will
correct the data in the underlying tables.

The next thing is to do the same thing again but linking the tables by
BandName and using "<> [BandID]" in the criteria. That tells you if you
have entries that have different IDs with the same name.

Lastly, try creating queries using a different form of relationship.
Instead of selecting records where the two fields are identical try
using the relationship that selects all records in Table1 and all of the
linked records in Table2. That gets you an entry in the basic query even
if you left that entry out of Table2. In the criteria for your new alias
just put "is null", again without the quotes. This will restrict the
results to just those Table1 records which have no matching record in
Table2.


I also want to compare my date fields to see if I mis-entered a date.
For example:
You can use a similar type of query, using an alias for the CDDate in
Table2 and "<> [CDDate]" as the criteria.

Table 1:
BandID = 7
CDDate = 1/1/1998

Table 2:
BandID = 7
CDDate = 11/11/1998

I know how to create queries, but how would I compare fields from
these 2 similar tables because I don't have a relationship between
them?

Do I need to use any special string comparison functions to compare
the text from 2 different fields?
No

I know I might be getting off subject, but another question comes to
mind. In my old table, I have an artists name in this format: last
name, first name. I used 1 field for the entire name.

For example:
Clapton, Eric
Spears Brittany

In my new table, I have separate fields for the artists last name and
first name. Is there any way to separate the last name and first name
in my old table and append the last name and first name to new fields
in my new table?


It's not easy because there are lots of exceptions to the basic rules.
Do you want "The Beatles" to be copied over as "Beatles, The"?

In some ways the size of the problem affects the choice of method. How
many records do you have in the database?


--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #4

P: n/a
Hi Megan,

Try this:
1.) Import (or link) your Old Bands Table into your new DB

2..) Design a new query based on both tables.

3.) You say that the BandID is identical in both tables, so create a join
between the 2 tables on BandID (Access will likely even do this by default.)

4.) Add the fields that you want to compare from both tables.
If you wanted to be able to quicky differentiate between them you *could*
assign aliased field names i.e. OldBandName:BandName and
NewBandName:BandName , OldCDDate:CDDate and NewCDDate:CDDate

5.) Specify criteria to return only data that doesn't match. There's no
sense in displaying the record if it's identical ... you're only concerned
with the exceptions, right?

I built a couple of quick sample tables conatining about 10 records each,
and designed this query to illustrate the above:
***********************************************
SELECT tblBandsNEW.BandName AS NewBandName, tblBandsOLD.BandName AS
OldBandName, tblBandsNEW.CDDate AS NewCDDate, tblBandsOLD.CDDate AS
OldCDDate
FROM tblBandsNEW INNER JOIN tblBandsOLD ON tblBandsNEW.BandID =
tblBandsOLD.BandID
WHERE (((tblBandsNEW.BandName)<>[tblBandsOld].[BandName])) OR
(((tblBandsNEW.CDDate)<>[tblBandsOld].[CDDate]));
***********************************************

Moving on now to your second problem...

Splitting the old "FullName" up into "FirstName" and "LastName" requires a
little more work...
Some of the old band names (i.e. "Supertramp") don't have a
FirstName/LastName, right?

So what I came up with was using a test to see if there WAS a comma used in
FullName...
If there was, then the FirstName / LastName data needs to be split up.
If there was no comma found, then just use the old "FullName" as the
"LastName"

This process requires the use of the IIF() function to determine how to do
the update, and the Instr(), Left(), and Mid() functions to manipulate the
strings.

Using the syntax: IIf(expr, truepart, falsepart) as it applies below ...
breaks down as follows:

IIf(InStr(1,[FullName],",")>0 means "This is a test to see if there IS a
comma in 'FullName' "

Value if True ... Left([FullName] means return the characters on the Left
side of the comma, which we use InStr(1,[FullName],",")-1 to determine the
position of the comma, then subtract 1 from it's position to get *just* the
FirstName

Value if False ... ,[FullName]) means If there was no comma found ... just
use FullName

Using the same Table and Field Names as above, I designed a second UPDATE
query that will change the data in the NEW table.
I'd suggest making a backup copy of this table before experimenting with the
update query, just in case something goes horribly wrong. :)

***********************************************
UPDATE tblBandsNEW INNER JOIN tblBandsOLD ON tblBandsNEW.BandID =
tblBandsOLD.BandID SET tblBandsNEW.LastName =
IIf(InStr(1,[FullName],",")>0,Left([FullName],InStr(1,[FullName],",")-1),[Fu
llName]), tblBandsNEW.FirstName =
IIf(InStr(1,[FullName],",")>0,Mid([FullName],InStr(1,[FullName],",")+2),Null
);
***********************************************
--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"Megan" <me**************@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...
Thanks for the ideas!

Maybe if I provided an example of what I want to do, then you'll be
able to understand what I'm trying to do.

Old Bands Table
BandID (Primary Key, Number)
BandName (Text)
CDDate (Date)

New Bands Table
BandID (Primary Key, Number)
BandName (Text)
CDDate (Date)

The two tables are almost identical, but the newer/ more recently
created table (New Bands Table) has more fields than I listed and has
more fields than the old table.

The BandIDs for both tables are identical (for example: 1 = Eric
Clapton, 2 = The Beatles, etc...).

I want to compare the BandNames from both tables to see if there are
any differences or accidental misspellings. For example: Eric Clapton
versus Eri Klapton. Hopefully, the query would tell me the BandIDs
that have different spellings in the BandName fields. For example, the
query would return BandID = 55 because Led Zeppelin in Table 1 is
spelled differently than Led Zeplin in Table 2.

I also want to compare my date fields to see if I mis-entered a date.
For example:

Table 1:
BandID = 7
CDDate = 1/1/1998

Table 2:
BandID = 7
CDDate = 11/11/1998

I know how to create queries, but how would I compare fields from
these 2 similar tables because I don't have a relationship between
them?

Do I need to use any special string comparison functions to compare
the text from 2 different fields?

I know I might be getting off subject, but another question comes to
mind. In my old table, I have an artists name in this format: last
name, first name. I used 1 field for the entire name.

For example:
Clapton, Eric
Spears Brittany

In my new table, I have separate fields for the artists last name and
first name. Is there any way to separate the last name and first name
in my old table and append the last name and first name to new fields
in my new table?

For example:

Table 1:
Name = Clapton, Eric

New Table:
LastName = Clapton
FirstName = Eric

I'll post this in another separate post.

Thanks,

Megan

Nov 13 '05 #5

P: n/a
Megan,
Look up UNION queries in the help files. Joins, which is what the other
posters are suggesting, allow you to display results from multiple tables in
adjacent columns. There are times when it is more helpful to display rows
from multiple tables that have similar columns. This is where a UNION query
can help.

So, using your given table definitions:

SELECT BANDID, BANDNAME, CDDATE FROM OLD_BANDS_TBL
UNION SELECT BANDID, BANDNAME, CDDATE FROM NEW_BANDS_TBL.

Please excuse the all caps. It's a habit of mine from when I worked with
Oracle. Anyway, all the rows in both tables that are exactly the same will
only show up once in the result. Any of the rows that are different in some
way will repeat with the different rows displayed for each contributing
table. This would let you quickly spot the rows that have differences and
fix them in the contributing table.

"Megan" <me**************@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...
Hi everybody-

I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the new database. For instance,
her old database has a table with Band Info in it. Her new database
also has a table with Band Info in it but slightly different. I was
wondering if there was an easy way to compare the fields from similar
tables in the old database with fields in the new database to see if
they match or don't match. Date fields and fields with strings are the
types of comparisdons we're looking to make. Or is code needed?

Thanks,

Megan

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.