Design Productivity

[Guest]

I am designing a database that would contain university programs of the
world. I want to know how to best design it so there wouldnt be difficulties
when it comes to accessing the information. I was thinking having separate
table for Continents, Countries and Schools with relations to each other and
Continents at the top. The Programs table which would list information
including the Contact person name, etc. I cant figure out how to design the
Programs table so that I can have a form that gives me the option of first
selecting a continent which would list the countries of that continent in a
box (listbox, combo box, etc), then I can select a country from this box
which would list the schools in that country in another box, then finaly be
able to select a school and get the Programs Table's detail shown in a form
where I can enter or view information. Why am I having such a hard time with
the form design? Would it be imposible to design using the form wizard?

 

[jwm]

It seems to me that rather than worry about forms design at this point, time
would be better spent on implementing a normalized table structure first.

After the table structure is normalized, developing forms with cascading
combo boxes is rather trivial. Candace Tripp, one amongst many, has a
tutorial on her website - http://www.candace-tripp.com/ - a very straight
forward example of what you seem to be seeking.

Regards...

 

[Guest]

There are so few Continents why not have a validation rule on a Country table
field so that only the Continents setup in the validation can be entered
(down one table).

Countries table with autonumber field as primary key in a one-to-many
relation to Schools table.

You need at least two forms – Programs listing schools AND Schools listing
programs. A Junction table between them with contact information and other
stuff like whether that school’s program is recognized by anyone, length,
cost, resident/correspondence/WEB, etc.

 

[Guest]

Do you mean have a table for evey continent?

There are so few Continents why not have a validation rule on a Country table
field so that only the Continents setup in the validation can be entered
(down one table).

Countries table with autonumber field as primary key in a one-to-many
relation to Schools table.

You need at least two forms – Programs listing schools AND Schools listing
programs. A Junction table between them with contact information and other
stuff like whether that school’s program is recognized by anyone, length,
cost, resident/correspondence/WEB, etc.

 

[Guest]

I understand now. You mean setup a feild in the countries table that looks
like "Like "Noth America" or Like "Asia" etc. Is that what you mean

 

[Guest]

Yes, this is what I mean. In the Validation Rule property put --
"Noth America" OR "Asia" OR "Europe" OR ... the rest.

 

[Guest]

There are a lot of schools (7000) how do I minimine the amount of time spent
seaching for a School in the Programs table?

 

[Guest]

What about subdividing the schools? Medical, Business, etc.

 

[Guest]

You would pre-select country, that narrows list.

There are a lot of schools (7000) how do I minimine the amount of time spent
seaching for a School in the Programs table?

 

[Guest]

I have one huge list of the schools in excel so subdividing up the schools
would not be easy. Preselecting the country however would be possible but
would that mean having a seperate table for each country?

 

[Guest]

You seem to be forgetting the purpose and workings of a relational database.

Your Country table would have a primary key that would be linked ont-to-many
relationship the the foreign key in the School table.

When you select a country your query uses the country key as criteria and
ONLY displays sschools in that country. This automatically narrows the
search.

I also said you could add a subdivision like Medical, Business, etc. and use
it in your query also.

 

[Guest]

I know what you are suggesting is probably very simple but I havent used
Access in a very long time. So you are suggesting I use/create a query that I
can use in the programs table?

 

[Guest]

Maybe I should describe the exact situation. I have a list of countires
alphabetically listed in excel in one book. I also have a list of schools of
the world with thier countires listed in a column beside them also in another
book in excel. I am going to have to input the programs manually. I am
looking for a way to create this database with the information by importing
the data in from excel. What I did was create a Countires table by importing
the fist book into access. The second book however contains the schools
listed in one column and thier countries listed in another. I was thinking to
import the schools column into access and name it the Schools table. But
then I have to assign each one of the schools a country (relationship link to
the countries table) manually and there are more than 7000 schools in that
list. I was looking for a way to create this database by using the second
book only because it lists the schools in one column and their countries in
the next.