Need help with table relations

G

Guest

I'm new at this... I'm using Access 2000. I've set up a working database
and a form to see all fields on page and more easily add info. I'm adding
new records daily (just over 1000 at present). I made a copy of said db when
it had 100 records in it - perfect to play around with.

My problem... My db contains about 70 fields, and I need to separate into
various tables ralated together and just can't figure out, even after reading
and help and many posts.

The business... I have a reservation ID (PK), Customers' LName, FName,
Pickup and Dropoff Locations, Date, Time, Driver Assigned, Type of Payment,
so on and so forth.

Many customers are random, i.e. farmed out to us--I just need that record,
but not necessarly to save that particular customer's info in a customer
table. Other customers are regular clients of ours, so I'd like all their
info (address, CC# etc) in a customer table that when I add a new record in
the main table I don't have to type all their info in over and over. Is it
possible to set it up where I have the option to type in a random name or
choose to pull one from cust table in the same field? I have the same
problem in the pickup and dropoff fields. Some of our regulars will be
dropped off at their residence every time. I'd like to be able to have that
populated by itself when I choose that cust. (I put "res" in the Dropoff
field and then the address in the dropoff address field). Most customers are
dropped off at hotels. I also want a hotel list table to lookup from, so that
if I put the name of the hotel in the Dropoff field, the hotel's address
would automatically populate. This would be both for reg. customers and the
many random entries. Do I have to create separate fields for each type of
Dropoff Location (i.e. the regulars pulling from the customer list and the
randoms pulling from the hotel list) or can one field be used to accommodate
both types?

If I can get this set up it will be a huge help. I also need a table for
drivers (I only have a few so right now I'm using a list box to pick from). a
table for the companies we get jobs from.

Well, this is enough for now. I'm quite new at Access and databases and
understand nothing about writing code at this point, just so you know.

Thanks for your help...
 
G

Guest

This will give you some ideas --

Reservation --
ReserID (PK)
CustID
Hotel_Res – Yes/No --
PickupLoc
DropoffLoc
PickupDate DateTime
DriverID
Payment

Customer –
CustID
Comapny
LName
FName
Address1
Address2
City
State
Zip
Phone
FAX
CC#
CrossStreet
Res Address1
ResAddress2
ResCity
ResState


Driver --
DriverID
LName
FName
Qual
HireDate

Hotel –
HotelID
CrossStreet
Address1
Address2
City
State
In your data entry form have a combo box with the AutoExpand set to Yes.
The Hotel_Res – Yes/No field can control where the PickupLoc and DropoffLoc
combo boxes pull their data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top