form data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I apologise for the "multiposting" but when I posted I seemed to get crashed
out of the system so I wasn't sure it had posted. Now I know it worked I
won't feel the need to keep trying!
Basically I have a database for a coach tour operator company and the user
will enter the tour information into the "tours" table. This will include
anything up to 10 different locations and times for pick ups for each tour. I
can't input the information on development because each tour will vary
considerably...it has to be the data inputter. I am unsure which type of box
to use to allow multiple entries. The "Booking Information" form hopefully
should be able to look up the various pick up times for the customer's chosen
tour in order for them to select the most convenient one for them. The only
way I can see that is through a combo box but what can I use in the "tours"
form to allow the tour operator to input the information in the first place?
I'm sorry if I didn't make it very clear the first time round, I hope I've
clarified it a little more. Thanks for being patient!

Michelle
 
What you need to look at before even creating any forms is the actual
database design. I'm imagining that your table looks something like this:

tblTours
tour_id
tourguide
loc1
loc2
loc3
loc4...
loc1pickup
loc2pickup
loc3pickup
loc4pickup...

This all information that should not be in the same table. What you have
here are a couple of many to many relationships that need to be controled
through different tables as well as a couple of join tables. Your table
should only contain the information that is specific to that tour and won't
change (generally, if you have white space in a table, then it is not
normalized). So something like this:

tblTours
tourID
tour_name
tour_startdate
tour_enddate
tour_guide (if there is only one guide per tour)

Then you will want to have a locations table:
locationID
location_name
location_information
location_attractions
blah...blah...blah...(again only information that is specific to that a
location)

Now because on tour can be related to many locations and one location can be
related to many tours, we need a join table which looks like this:

tblTourLocations
tourID
locationID

Now on your form... the key in operator can select the tour and then assign
locations from the location table to the tour. Each time she/he does this,
it will create another entry in the TourLocations table. If you need to
track the drop off and pickup times... ad this info to the TourLocations
table so it looks like this:

tblTourLocations
tourID
locationID
drop_off
pick_up

Basically this setup will "normalize" your database and make managing the
data much easier. If a user wants to set up a new tour... you provide a form
that allows them to add a single records to the tours table... then they can
select the locations from a combo or list box that is linked to the locations
table and assign a drop off and pick up time for that tour/location group. :)
 
I apologise for the "multiposting" but when I posted I seemed to get crashed
out of the system so I wasn't sure it had posted. Now I know it worked I
won't feel the need to keep trying!
Basically I have a database for a coach tour operator company and the user
will enter the tour information into the "tours" table. This will include
anything up to 10 different locations and times for pick ups for each tour. I
can't input the information on development because each tour will vary
considerably...it has to be the data inputter. I am unsure which type of box
to use to allow multiple entries. The "Booking Information" form hopefully
should be able to look up the various pick up times for the customer's chosen
tour in order for them to select the most convenient one for them. The only
way I can see that is through a combo box but what can I use in the "tours"
form to allow the tour operator to input the information in the first place?
I'm sorry if I didn't make it very clear the first time round, I hope I've
clarified it a little more. Thanks for being patient!

Michelle
Click to expand...

See Jason's very clear reply. If you're trying to store multiple
values in one field - *that is not how Access works*. If you have a
one tour to many pickups relationship, you need TWO TABLES (at least)
- a table of Tours related one-to-many to a table of Pickups. A
Subform is a handy toolto enter this data.

John W. Vinson[MVP]
 
Thanks for replying Jason. I don't think I could have explained myself
clearly enough. The actual location isn't the destination of the tour it is
the pick up location of the coach. As the coach will pick up anything up to
10 locations at different times (such as a bus) all for the same tour I need
a field that will enable the user to enter these locations and relevant times
so the data can be retrieved when a booking is made. I don't know if i'm
still clear but I don't know how else to explain it without giving you each
detail of the tables and fields I already have. I am aware of the "normal"
rule and i believe my database abides by it as there is no duplicate or
redundant data in any of my tables. Are there any fields that will list items
without having to look them up from another table basically to make data
entry more straight forward for the user or will I have to incorperate a
seperate table and possibly have it as a subform in the main form?
 
Mich987 said:
Thanks for replying Jason. I don't think I could have explained myself
clearly enough. The actual location isn't the destination of the tour
it is the pick up location of the coach. As the coach will pick up
anything up to 10 locations at different times (such as a bus) all
for the same tour I need a field that will enable the user to enter
these locations and relevant times so the data can be retrieved when
a booking is made. I don't know if i'm still clear but I don't know
how else to explain it without giving you each detail of the tables
and fields I already have. I am aware of the "normal" rule and i
believe my database abides by it as there is no duplicate or
redundant data in any of my tables. Are there any fields that will
list items without having to look them up from another table
basically to make data entry more straight forward for the user or
will I have to incorperate a seperate table and possibly have it as a
subform in the main form?
Click to expand...

Take the old example of a sales order. Each order with one or more items
purchased. This takes two tables, one for each order and one for the line
items on each order related with a one-to-many relationship.

You have tours that that can have one or more pick up locations each. Same
relationship (one-to-many) as the sales order example and therefore you also
need two tables.
 
Are there any fields that will list items
without having to look them up from another table
Click to expand...

No. There is no such type of field. And you're not "looking up" data
in another table - you're STORING data (multiple pick up points) in
another table.
basically to make data
entry more straight forward for the user or will I have to incorperate a
seperate table and possibly have it as a subform in the main form?
Click to expand...

I don't see what's not straightforward about a well built subform.
What the user will see is


Tour Name Tour Date This Field That Field

Pickup Points:
Hilton Hotel
Marriott Hotel
Joe's Bar and Flophouse
Westin Hotel

and be able to remove the third and replace it with the Sheraton...


John W. Vinson[MVP]
 
Back
Top