Replacing Continents with Multiple Countries in an Access Table

[InOverMyHead]

Hello,

The Subject says it all: I have a table as follows:

(ID), ContractID, CountryName

With data in it that looks like:

713 396 Africa
729 397 Europe
730 390 Canada

What I'd like to do, is replace "Africa" with the actual countries...
so:

713 396 Africa

Would become:

713 396 Africa
731 396 Algeria
732 396 Angola

etc etc

(ID) - the first field - is the Primary key of the table, and
ContractID refers to the Contract number from another table. I'd want
each line I created to have the same Contract # as the original line
it's being created from (I hope that makes sense...).

If someone can lead me in the correct direction - either a link or a
quick solution (I'm rather clueless with Access, but I've backed this
database up and have no problems mucking around with it) it would be
appreciated.

Thanks in advance.

ps: I am using Access 2003 SP2

 

[John W. Vinson]

Hello,

The Subject says it all: I have a table as follows:

(ID), ContractID, CountryName

With data in it that looks like:

713 396 Africa
729 397 Europe
730 390 Canada

What I'd like to do, is replace "Africa" with the actual countries...
so:

713 396 Africa

Would become:

713 396 Africa
731 396 Algeria
732 396 Angola

etc etc

(ID) - the first field - is the Primary key of the table, and
ContractID refers to the Contract number from another table. I'd want
each line I created to have the same Contract # as the original line
it's being created from (I hope that makes sense...).

First, you need a table with two fields: Continent and Country. Fill it with
records for each country, showing which countries are in each continent. (How
you'll handle countries such as Turkey and Russia which have portions in both
Europe and Asia is up to you).

Creaet a Query joining this table to your existing table, joining on the
continent.

Change the query to an Append query; select the ContractID and the
countryname, and append to your table. I presume that ID is an autonumber -
just don't include it.

Records such as the Canada example - which don't contain a continent name -
simply won't be included, and that record will be left in the table unchanged.

John W. Vinson [MVP]

 

[InOverMyHead]

Just to follow up: I've exported the table into Excel, then manually
modified it to my requirements... It's a long way around, so if anyone
does have an automated method that's relatively painless to implement,
I'm all ears.

Thanks

 

[raskew via AccessMonster.com]

Checkout John Vinson's previous post. He's provided a very workable solution.


Bob

 

[InOverMyHead]

Thanks for the reply - it's bookmarked and I'll give this a shot
tomorrow... Anything has to be better than the copy/paste fiasco I
just went through. Cheers!