Making data entry faster on form

[Sailor]

Greetings!

Say you are making a database that tracks where clients have gone on vacation.

You have a table named 'tblTrip.' tblTrip looks something like

[client id (primary key)] [destination]

It's keyed by the client id to a table tblClient which has your basic client info. We track where John Smith goes, and we will enter an unlimited number of trips per client.

There's a tremendous amount of duplication, obviously, since many clients are going to the same place.

How can you design a form that allows the option to choose from trips already taken, pulling from the entered trips of others, but also allows the data entry person to add a new trip? In other words, he can select from Paris, Rome, NY but could also enter Brussels. Also, I need the mechanism to be intelligent so that, if 15 clients have gone to Paris, I avoid listing Paris 15 times.

I'm sure this is an easy question for those more expert than I.

Many thanks in advance for any guidance.

John Sailor

 

[Mark Wild]

Hello Sailor,

It sounds like you need a different table structure to the one you have.

I would suggest three tables

TblClient
ClientId
ForeName
Surname
Address1 etc

TblDestination
DestID
City
Country

NB. Althoug husers might not want coutnry info at the moment, they always want data cut in different ways in the future, so it allows you some flexibility.

If you want to normalise your tables further, have a TblCountry with CountryID and CountyName, and only place the CountryID into TblDestination

TblHoliday
DestID
ClientID

Make the relationships

TblClient.ClientID-TblHoliday.ClientID one to many
TblDestination.DestID-TblHoliday.DestID one to many

For the input on the forms, you then use combo boxes, but I'd try to sort this first and then look at the data entry issues.

Look some of this up and let me know if you have any probs.

M

 

[Sailor]

thks

Awesome stuff. I'll try a new structure and see if it sorts out my issues.

Thanks.