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