Basic Access Problems

[daleibz]

Apologies for what is probably a very simple request but my skills are limited as I've not used Access for about 10 years!

I'm creating a travel booking database and the main goal is to have all the travel info (client details, traveller details, flight details, costs, hotels,ect) for a booking viewable in one place.

As there could be more than one set of flight details for one booking I need to be able to input several tables/forms and all the data but link it to one booking reference. The flight details table has around 40 fields.

So

MainBookingTable
Linked to :
+ClientTable
+TravellerTable
+HotelDetailsTable
+CarDetailsTable
+FlightDetails1 (has around 40 fields) + FlightDetails 2(the same fields) +FlightDetails3 etc etc
+PaymentTable

I think I understand how to set the relationship up correctly but getting all the data together I do not know where to start!!

If anyone can assist it would be amazing!

Thank you

 

[plog]

Time to read up on normalization: https://en.wikipedia.org/wiki/Database_normalization

That's the process of setting up your tables and relationships. You've broken normalization in a big way with all those FlightDetails tables. You only need 1 of them.

What you are doing by numerating them (FlightDetails1, FlightDetails2, etc.) is essentially storing a field value in the table name. You don't have a new table for every Airline do you? No, you store the airline information in a field within FlightDetails. Same with that number.

What you need to do is take FlightDetails1, rename it to FlightDetails and add a field in it called [FlightNumber]. In that field you will store what is now in the table name of the others. That way all flight info is in one table and you can tell if its the 1st, 2nd, or 303rd flight.

I fear you've made that error a few other times. Whenever you have tables with the same (or nearly the same) fields all the data needs to go into one table with a new field to differentiate records. The same can be said of Numerated fields (Traveler1, Traveler2, Traveler3), when you do that with field names its time for a new table to store that info, but properly--with a new record for each traveler not a new field.

I suggest reading up on normalization, giving it a shot, setting up the Relationship Tool in Access, taking a screenshot of it and posting it back here. Then we can help you arrive at the proper structure.

 

[HiTechCoach]

I agree with plog about the flights and that it is not properly normalized.

A flight is a single ticket with a single flight number. You want only one record per flight (flight number/ticket). It may take several flights (flight numbers/tickets) to get to your destination. If you have multiple flights (tickets) then yiu have multiple records. This become important for printing an itinerary.


I see flights, car rentals, and hotels as all the same. They all have a start date/time and end date/time. Flights have a departures and arrival date/time. Car rentals has a pickup and return date/time. Hotels have a check in and check out date/time.

It is possible to have multiple flights, multiple hotels stays and even multiple car rentals all in a single trip/booking. I have done it more than a few times while traveling.


I had a trip itineraries like this before:

Flight 1
Flight 2 (connecting flight)
Car rental 1 pick up
Hotel 1 check in
Hotel 1 check out
Car rental 1 return
Flight 3
Car rental 2 pickup
Hotel 2 check in
Hotel 2 check out
Hotel 3 check in (had to switch hotels due to room availability or changed cities)
Hotel 3 check out
Car rental 2 return
Flight 4


By having all the above as separate records it makes printing the above itinerary easy.

If you want to test your design with all the all the flights in a single record then enter some test data like above and try to print the itinerary.