Relationships

[Mike Diamond]

Hi

My database has a table containing a list of counties and a table with a
list of countries. From these i want 5 different tables to be able to Lookup
counties based on the selection of a Country. I have no problem doing this
and it works fine. However, when looking at the relationships window it is a
mess and there are lines everywhere. I am just wondering whether this would
be the most efficient way of looking up counties and countries, because
presumably it is best to have each table with its own addresses contained in
each - and a values list is out of the question?

Mike

 

[Steve]

TblCountry
CountryID
CountryName

TblCounty
CountyID
CountryID
CountyName

These two tables will do the trick for you!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Mike Diamond]

Hi Steve

Thank you for the reply. I did actually lay out the tables as you had
suggested, but one the lookups were created there were lines everywhere - as
the County and Country tables were supplying the information for 5 different
other tables with addresses. I am okay doing this if you suggest that it is
the most efficient way of doing it..

Regards
Mike

 

[John W. Vinson]

Hi

My database has a table containing a list of counties and a table with a
list of countries. From these i want 5 different tables to be able to Lookup
counties based on the selection of a Country. I have no problem doing this
and it works fine. However, when looking at the relationships window it is a
mess and there are lines everywhere. I am just wondering whether this would
be the most efficient way of looking up counties and countries, because
presumably it is best to have each table with its own addresses contained in
each - and a values list is out of the question?

No, it is certainly NOT appropriate to use a value list - nor do you need five
tables. *ONE* table is ample; you can use Queries as the source of a COmbo Box
on a form (if you're using a combo box in a Table, don't: see
http://www.mvps.org/access/lookupfields.htm for a critique of the Lookup Field
datatype).

Have one combo, cboCountry on your form (frmMyForm I'll call it), with a
rowsource like

SELECT DISTINCT CountryName FROM Counties ORDER BY Countryname;

and a second County combo:

SELECT County FROM Counties
WHERE CountryName = [Forms]![MyForm]![cboCountry]
ORDER BY County;

You'll need to requery the County combo in the afterupdate event of the
Country combo.

John W. Vinson [MVP]

 

[Steve]

As John suggested, do away with the lookups. In each of your five tables,
add CountryID or CountyID whichever you need. Join each CountryID to
CountryID in TblCountry and each CountyID to CountyID in TblCounty. Then use
a combobox with the appropriate rowsource to get either CountryID or
CountyID.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Mike Diamond]

Hi

My database has a table containing a list of counties and a table with a
list of countries. From these i want 5 different tables to be able to Lookup
counties based on the selection of a Country. I have no problem doing this
and it works fine. However, when looking at the relationships window it is a
mess and there are lines everywhere. I am just wondering whether this would
be the most efficient way of looking up counties and countries, because
presumably it is best to have each table with its own addresses contained in
each - and a values list is out of the question?

No, it is certainly NOT appropriate to use a value list - nor do you need five
tables. *ONE* table is ample; you can use Queries as the source of a COmbo Box
on a form (if you're using a combo box in a Table, don't: see
http://www.mvps.org/access/lookupfields.htm for a critique of the Lookup Field
datatype).

Have one combo, cboCountry on your form (frmMyForm I'll call it), with a
rowsource like

SELECT DISTINCT CountryName FROM Counties ORDER BY Countryname;

and a second County combo:

SELECT County FROM Counties
WHERE CountryName = [Forms]![MyForm]![cboCountry]
ORDER BY County;

You'll need to requery the County combo in the afterupdate event of the
Country combo.

John W. Vinson [MVP]

Hi John

The five tables i have are for the addresses of different bodies;
Educational Institutions, Project Associates, Company Offices, Contracting
Authorities and Project Information. Each table contains details other than
just addresses but there are number of repeat fields in each such as;
address1, address2, address3, town, county, country, tel, fax and email. I
was thinking about having this info in one table with an id field for the
associated parties but it would mean having to explicitly state what type of
contact is being entered which i dont think would be appropriate even though
it would be more efficient. The thing that made me think about seperating
these addresses into different tables was after looking at the Northwind
database where addresses have been seperated into their associated tables -
why would this have been done?

Regards
Mike

 

[Armen Stein]

The five tables i have are for the addresses of different bodies;
Educational Institutions, Project Associates, Company Offices, Contracting
Authorities and Project Information. Each table contains details other than
just addresses but there are number of repeat fields in each such as;
address1, address2, address3, town, county, country, tel, fax and email. I
was thinking about having this info in one table with an id field for the
associated parties but it would mean having to explicitly state what type of
contact is being entered which i dont think would be appropriate even though
it would be more efficient. The thing that made me think about seperating
these addresses into different tables was after looking at the Northwind
database where addresses have been seperated into their associated tables -
why would this have been done?

Regards
Mike

In my opinion, it's not worth the trouble to put address fields
(address1, address2, city, county, state, country, postal) in a
central table and then try to link them to the other tables that use
them. Just put the addresses where they belong.

In the future if you need a master list of ALL addresses, you can use
a union query to put them all together.

Database design purists may disagree with this approach, but with a
static structure like an address, it is more practical in the real
world.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com