Creating an Auto-Look on a datasheet form

[McObraz]

Hello all,

Glad to be back. Now, I am creating a database that has a transport aspect to it.

Basically, I have a table with the list of buses, and tables for routes plied by each of the buses.

What I would like to do is that on a datasheet form, I would wish that if at the point of entering data, the data entry person selects Bus Number 1, then in the next field where the route goes, ONLY the options for the routes that this bus plies show up as what has to be selected from the look-up menu that appears. The same for the other buses and routes. Is this possible? Or if anybody has a better suggestion of how to do this, kindly let me know.

As always, any kind of help is appreciated. I hope that one day I will also be helpful to access novices like me.

The database is attached.

Thanks.
Maurice

 

[Mihail]

Run the query q_Route !

I have tweaked your database for you.
Try to understand what I have doing.
Google, read and understand, the concept of normalization for databases. You will not be able to design any database until you will not understand this.

Feel free to ask more.

PS:
Is the dream of my life to emigrate in Africa and your country is in the top of my preference.
Have you any idea how I can do this ?

 

[McObraz]

Mihail,

Thank you so very much for your help. It has indeed been helpful and you are a life-saver. Now, I have gone through it and included some real data on my part. I am so very new to access so I know I still have a long way to go before I completely comprehend it all so thanks for bearing with me.

Now, basically, my issue is under transport. I have included a datasheet form through which I would like the data to be entered. Now, basically, at the point of entry, when it comes to transport, first the person chooses whether it is Full or One Way, then on the next field, the person entering data chooses which route (At this point, all the options for that route fill themselves in automatically from the choice of route, then an automatic calculation is done to put in the figures. As an example, when use Kimumu as a route when it asks for a route input. When someone chooses the Transport mode to be Half, I would just like an entry of Half of what is shown for Transport Cost. Is this Possible?

Again, thank you very much for your help.

Thanks.
Maurice.

In response to your request for information, as a Kenyan, let me be among the first to welcome your idea of wishing to emigrate over here. It indeed is a wonderful place. How about you drop me an e-mail through mondymaurz@gmail.com then I can give you further information? Even a blank e-mail will do, as long as I know where to send you the details.

Again, your help is much, much appreciated.

 

[Mihail]

I quote myself:

Google, read and understand, the concept of normalization for databases. You will not be able to design any database until you will not understand this.

Google, read and understand, the concept of normalization for databases. You will not be able to design any database until you will not understand this.

Google, read and understand, the concept of normalization for databases. You will not be able to design any database until you will not understand this.

In each table you must store information about one (and only one) object type.
Examples:

tblContinents - This is the object type (Continent)
ID_Continent (Prymary key - AutoNumber) Even if each continent has different names, Access deal better with numbers.
ContinentName (Text) - A property of object Continent: the name
ContinentArea (Number - Double) Other property of object Continent: area [Kmp]
ContinentPopulation (Number - Long) Other property of object Continent

tblCountries - This is the object type (Country)
ID_Country (Prymary key - AutoNumber) Even if each country has different names, Access deal better with numbers.
ID_Continent (Foreign key - Number) This field identify each country from which continent is.
CountryName (Text) - A property of object Country: the name
CountryArea (Number - Double) Other property of object Country: area [Kmp]
CountryPopulation (Number - Long) Other property of object Country

tblCities - This is the object type (City)
ID_City (Prymary key - AutoNumber) Even if each country has different names, Access deal better with numbers.
ID_Country (Foreign key - Number) This field identify each city from which country is.
CityName (Text) - A property of object City: the name
CityArea (Number - Double) Other property of object City: area [Kmp]
CityPopulation (Number - Long) Other property of object City

And so on.

Do not store the same information in two places
Example (how NOT should define a table):

tblCountries
ID_Country (Prymary key - AutoNumber)
Continent (Text) This field store the name of continent for each country.
CountryName (Text)
CountryArea (Number - Double)
CountryPopulation (Number - Long)

The red field is not OK because I already have the continents names in tblContinents.

So:

Google, read and understand, the concept of normalization for databases. You will not be able to design any database until you will not understand this.

Google, read and understand, the concept of normalization for databases. You will not be able to design any database until you will not understand this.

Google, read and understand, the concept of normalization for databases. You will not be able to design any database until you will not understand this.