PLEASE Help with Design of Travel Logging Table

[Julia UK]

Hi,
I need to create a table that can store the following data:

Employee ID, Reference, Departure Location, Transport Type, Departure Date,
Destination , Arrival Date, Multileg Journey?, Taxi Booked?, Visa Required?,
Total Flight Cost, Booked by, Travel Agent Contact , Status

The problem I have is with the "Multileg Journey" field - if the user
selects "yes" then I need to have the functionality to add multiple
destinations as well as arrival and departure dates to these destinations!
I'm not sure how to do this as to put it into one table will surely create
redundant data. Also which field could I use as my primary key, because one
employee can make many journeys?

Sorry if it's a stupid question but Im still a beginner and seem to have hit
a brick wall!

 

[mnature]

You need to create several tables, that will then be related to each other.
First, a table for employee information. This will only have information
directly related to an employee, such as ID, name, and whatever else you need
to know about that person. The employee's ID can serve as the primary key
for that table, as long as it is always unique, and can only apply to that
employee. Then you will probably want two more tables. One will be for
keeping track of each journey, and will probably have an autonumber primary
key, and include information about which employee is going on the trip,
initial departure date, final return date, travel agent contact, etc. The
third table will have an autonumber primary key, and include information
about each journey. This could have multiple entries for each journey (the
"legs") and could include the particular airline used (which could be drawn
from a fourth table), departure, arrival, perhaps a memo field for any notes
or explanations.

So, each employee can be linked to any particular journey, and each journey
can be linked to any number of legs. You will use queries and forms to pull
all this information together, both to make it easy to see what is happening,
and for entering the information.

 

[stripedfrog]

I have a similar situation and want to make sure I understand you. I have a
table created for dealers that sell "product A". There are also two other
products that are sold but not every dealer carries all three products.

I understand I need two other tables for "products B and C" but am a bit
confused. What fields need to be in these two tables? The primary key will
be the dealer's account number. Then what do I put?

Thanks.

 

[mnature]

You should have a table that includes all of your dealers, and one table that
includes all of the products that you can get from the dealers. A third
table will reference both of these tables, and that is where you select which
products go with which dealers. Any particular dealer could have one or more
products, and any particular product could have one or more dealers.

TblDealer
DealerID
DealerName

TblProduct
ProductID
ProductName

TblDealerProduct
DealerProductID
DealerID
ProductID

 

[John Vinson]

I have a similar situation and want to make sure I understand you. I have a
table created for dealers that sell "product A". There are also two other
products that are sold but not every dealer carries all three products.

I understand I need two other tables for "products B and C" but am a bit
confused. What fields need to be in these two tables? The primary key will
be the dealer's account number. Then what do I put?

No, you do NOT need a separate table for each product. That's not how
relational databases work!!!

You have a Many (dealers) to Many (products) relationship. The correct
structure for this does involve three tables, but not product-specific
tables:

Dealers
DealerID
<information about the dealer>

Products
ProductID
ProductName
<information about the product itself>

Dealerships
DealerID <link to Dealers, who sells this>
ProductID <link to Products, what do they sell>
<information about this dealer/this product, e.g. the dealer's
price, delivery terms, etc.>

If Dealer X sells A and B, and Dealer Y sells B and C, you'ld have
four records in dealerships:

X A
X B
Y B
Y C


John W. Vinson[MVP]

 

[stripedfrog via AccessMonster.com]

Thanks All!