HELLPPPP!!!!!! Assistance Needed For a New Starter

[accessnewbie7]

Hi there,


I’m a very new starter with Access, this week to be precise, and i have decided to take it upon myself to start a project and this seemed like the logical place to beg for assistance so please help me somebody!!!


I am trying to build an information database. The information will be global events so within my database I have created a Main table, with all my columns (region, country, city, date etc) and I have also created a region table (that just lists the different regions) and a country table (which lists all the countries).
What I would like to be able to do is have 2 combo boxes on my form, one of which will be ‘region’ and the next ‘country’. When you click on the region, Europe for example, I would like the second combo box to only display those countries within Europe.
My question would be, firstly how do I do that and secondly do I need to have a separate table for my main table, region and countries or is there a better way to do it. Do I also need to link these in a relationship so the data is all housed on my main table? Previously when I attempted this it was displaying the ID number in the main table and not the region which was midly irritating!!
I'm sorry if this is a simple question but please can anyone help???

Thank you

 

[DCrake]

Why are you making duplicate posts then deleting them. Stick to one post, it will be read. Also it is a Sunday, people do rest.

To give you a breif answer though, yes you can do all this, you need to do a search for cascading combo boxes.

 

[accessnewbie7]

thanks for the reply, i didnt post duplicate posts, i reworded the first one to make more sense.

 

[DCrake]

What about the one I deleted form the tables section?

 

[accessnewbie7]

i realised once i'd posted it that it wasnt a table related question, hence me moving it to the General section. As i said, its my first time on the site. apologies for the inconvenience, i'm just trying to get some assistance from somebody with my query. i'll carry on waiting out.

 

[David Eagar]

You will need Country, Region and City tables from the description of your project - 1 region contains many Countries, 1 Country contains many Cities

 

[accessnewbie7]

David,

Appreciate the response, thank you. How exactly would i break the relevant data out though in each table, would i have one table for all the countries in the world or one table per region. i'm confused as to how the database will recognise which countries per region. Am i explaining this ok!?

 

[David Eagar]

Good design procedures (normalisation) dictates that a peice of data should only appear once in the database - so what fields go into which tables needs to be thought through.. This is one of the basic building blocks that decide a well designed (efficient operating) database and needs some time.

As it happens, I buuilt a database along similar lines to what you are attempting and a few words of caution: Depending on how many Cities you are envisaging adding, the database will become VERY big (mine got to about 50mb and that was before adding any events!! So how many users and how they will access the database may become an issue

 

[Mr Smin]

Need to define 'Region' as well. In the UK it is often taken as meaning a subsection of one of the countries (North Wales, Western Scotland, South West England), but you could equally mean 'Middle East' or 'South America'.

 

[accessnewbie7]

Well i plan to include a list of countires but not the cities, they can be inputted manually as all searches will be carried out at a country level.

So far i have created a main table, with every field that i want in my form. I have then created a region table, which breaks the world into 8 regions, and a country table which has all the countries in the world. In the country table i also have a region column which i think i need for the filter, though i'm not sure. I have then linked each table to the main one via a lookup column so that the data will all feed back into the main table. i think i now need to create 2 cascading combo boxes on my form to filter the information but i have looked at the code that i should paste into Visual Basic and it just doesnt make sense to me. i think i might need to spend some hours doing some training!!

 

[David Eagar]

Need to define 'Region' as well. In the UK it is often taken as meaning a subsection of one of the countries (North Wales, Western Scotland, South West England), but you could equally mean 'Middle East' or 'South America'.

If you wish to go down to State/Province/Shire level, this can lead to operational difficulties - you know which cities are in what province for your own country, but are not familar with other countries - if you don't know what province a city is in, it can lead to problems when doing searches

 

[accessnewbie7]

apologies, i dont think i've explained myself well at all!! I want one drop down box with the 8 regions of the world in it. then i want a second combo box which will have just the countires within those regions. So if you clicked on 'Middle East' for example, it would bring up only the countries in the Middle East. I have created 2 tables, one for regions and one for countries, but i dont know how to create those combo boxes.

 

[David Eagar]

Something along these lines?

 

[accessnewbie7]

David,

Exactly what i was looking for, thank you. i will embed within my database. thanks again for taking the time out, much appreciated.

 

[David Eagar]

Glad to help - Can you post your database - I'd like to see your table structure

 

[accessnewbie7]

David,

Once i have completed her (not sure why my database is female!) i will clear out the sensitive information/tables i have created and will gladly post it.

 

[David Eagar]

I was just thinking, before spending too much time adding data to ensure that the structure is right from the beginning

 

[accessnewbie7]

my problem is that i've created all the tables/forms which display most of the sensitive material, to strip it would take me quite a while. Since i made the amendments though everything is running well so appreciate your help BIG TIME!!