Ideas on merging two organisations data?

[Tony Williams]

I have two organisations who are merging. OrgA has a database that OrgB
wants to use. The databases that OrgB will be using is a copy, with all the
data, of the one used by OrgA, so the tables, queries and reports are
unchanged. However (and there is always one isn't there!) The reference
number used by OrgA is formatted as ABC/000/YYYY , ABC are fixed text
characters, 000 are numeric characters and YYYY was the year of the date
each record was created. Whereas the format of the reference number for OrgB
is UVXYZ/00000/YYYY where UVXYZ are fixed text characters (different from
OrgA's) 00000 is a generated number using DMax and YYYY is the year of the
date the record is created. The forms for input and the reports etc have the
reference number formatted as "UVXYZ"/00000/0000;0;_
So when I import the current data into the database there is an error
message that the reference number is not formatted correctly.
Anyone any ideas how I can overcome this problem?
Thanks
Tony

 

[Bob Quintal]

I have two organisations who are merging. OrgA has a database that
OrgB wants to use. The databases that OrgB will be using is a
copy, with all the data, of the one used by OrgA, so the tables,
queries and reports are unchanged. However (and there is always
one isn't there!) The reference number used by OrgA is formatted
as ABC/000/YYYY , ABC are fixed text characters, 000 are numeric
characters and YYYY was the year of the date each record was
created. Whereas the format of the reference number for OrgB is
UVXYZ/00000/YYYY where UVXYZ are fixed text characters (different
from OrgA's) 00000 is a generated number using DMax and YYYY is
the year of the date the record is created. The forms for input
and the reports etc have the reference number formatted as
"UVXYZ"/00000/0000;0;_ So when I import the current data into the
database there is an error message that the reference number is
not formatted correctly. Anyone any ideas how I can overcome this
problem? Thanks
Tony

Several possibilities. 1. Change OrgA's reference numbers, 2. change
OrgB's reference numbers, 3. add a new reference number for both org
A and Org B.

Based on the format string you provided, the prefix is not stored in
the table. That's gotta be changed, since it is no longer a
constant.

I'd therefore go with the new column, You might be able to reformat
ABC/000/YYYY to ABCxx/00000/YYYY or chop out the 4th and fifth
characters of Org B's prefix, and if Org never has more than 999
reference numbres per year, remove two zeroes from that.

 

[Tony Williams]

Thanks Bob. Unfortunately I can't change the format of OrgB's number but I
like the idea of reformatting OrgA's numbers to fit. All future records will
be OrgB's reference numbers so presumably I could have an input mask on the
new record form of UVXYZ"/00000/0000;0;_ as I'm assuming that it wouldn't
conflict with the existing records. The only problem I can see is formatting
the control on reports but I don't suppose that matters as the report will
presumably print whatevers in the table no matter what format it's in? How
would I go about changing the format of OrgA's numbers to ABCxx/00000/YYYY ?
Thanks for your thoughts
Tony

 

[Bob Quintal]

Thanks Bob. Unfortunately I can't change the format of OrgB's
number but I like the idea of reformatting OrgA's numbers to fit.
All future records will be OrgB's reference numbers so presumably
I could have an input mask on the new record form of
UVXYZ"/00000/0000;0;_ as I'm assuming that it wouldn't conflict
with the existing records. The only problem I can see is
formatting the control on reports but I don't suppose that matters
as the report will presumably print whatevers in the table no
matter what format it's in? How would I go about changing the
format of OrgA's numbers to ABCxx/00000/YYYY ? Thanks for your
thoughts Tony

I need to know the field's type (string or number) and size to be
absolutely sure, but the principle would be to use the string
manipulation functions
left(Reference_ID,3) & "nn/" & "00" & mid([reference_ID,7)
in an update query.

Remember to experiment on a copy of the table, or a copy of the
mdb.until you are certain it is right.

Q

Several possibilities. 1. Change OrgA's reference numbers, 2.
change OrgB's reference numbers, 3. add a new reference number
for both org A and Org B.

Based on the format string you provided, the prefix is not stored
in the table. That's gotta be changed, since it is no longer a
constant.

I'd therefore go with the new column, You might be able to
reformat ABC/000/YYYY to ABCxx/00000/YYYY or chop out the 4th and
fifth characters of Org B's prefix, and if Org never has more
than 999 reference numbres per year, remove two zeroes from that.