"Simple" Global Mailing List - 1 table or 3?

[Nunnsby]

Hey all

Thanks for taking the time to read this msg. I am a "newbie" to Access,
so bare with me here.

I have a fairly simple request, but am struggling the basics of it, so
any help would be highly appreciated.

I am based in South Africa and the scenario is as follows: we have a
fairly large mailing list, already in Access, which contains approx 80
k names around the world. South Africa consists of about 50 k, with
another 20 k in Europe, and then the rest split around the rest of the
world, including mainly America and so on. We print this entire mailing
list 3 times a year to send our brochures to clients.

My main AIM here, and you are probably wondering why I am trying to
re-invent the wheel, is to provide CONSISTANCY to the Database. We
currently have only one person editing the database, and you will be
amazed at how many spellings there are of the word Germany alone!!
And when you are posting from South Africa, you want there to be
Correct Addresses.

Our database currently consists of about 20 tables, as we have
different tables for each category: South Africa A-Z, Foreign A-Z,
Africa A-Z, US A-Z, Companies A-Z, Companies Foreign, South African
Embassies, Foreign Embassies, etc, etc, etc

NOW . . .

I realise this can be put into one table, BUT I want to minimise the
amount the data-inputter can type. Eg, I want:
* Countries to all be pre-listed.
* In South Africa I want all the Regions pre-listed.
* In America I want all the States Pre-listed
- and would like there to be the 2-letter state code based on the
choice of state chosen automagically placed in the Postal Code field as
a pre-text.

As Embassies, Companies, Associations can all be boolean, they can be
simple yes/no boxes.

The concept I am struggling with is the following: If I have one table,
I can't sub-select. If I have more tables, I am stuggling, using forms
to apply the data properly.

Any help would be much appreciated.

Thanks and regards to all reading this.

Richard

 

[John Vinson]

Hey all

Thanks for taking the time to read this msg.
... <very useful background snipped>

NOW . . .

I realise this can be put into one table, BUT I want to minimise the
amount the data-inputter can type. Eg, I want:
* Countries to all be pre-listed.
* In South Africa I want all the Regions pre-listed.
* In America I want all the States Pre-listed
- and would like there to be the 2-letter state code based on the
choice of state chosen automagically placed in the Postal Code field as
a pre-text.

This is the role of Lookup Tables (not to be confused with Microsoft's
infamous "Lookup Field" wizard).

You need a table of countries - I'd recommend using the United
Nations' country codes - with the proper postal service spelling of
the country name; and a table of United States states and posessions
and Canadian provinces with the two letter codes as the primary key.

You would do your data entry on a Form, using Combo Boxes to select
the country (and as appropriate, state/region/province).

As Embassies, Companies, Associations can all be boolean, they can be
simple yes/no boxes.

The concept I am struggling with is the following: If I have one table,
I can't sub-select. If I have more tables, I am stuggling, using forms
to apply the data properly.

Ummm... what ever gave you the idea that you cannot subselect!?

Certainly, your Addresses should be all in one table. And you can
create Queries based on that table, to sub-select it in any way you
wish. Those Queries can be used as the basis of a Report (for example
to print mailing labels), a Form, an export to Excel or comma
delimited text files, whatever you wish.

I'd be happy to email you a text file with the US states & posessions
and Canadian provinces. The UN table of nations that I have is a few
years old and therefore out of date - a Google search should find a
more current one.

John W. Vinson[MVP]