Anyone willing to help me design a database?

[ScottGem]

What's the PK? Yes you need to write code. There is no way you will get the app to do all you want without it.

 

[accessistough]

Flight Number was the primary key. Ok, gonna have to have a look at some code I guess...

 

[daherb]

Also, it seems to be having some trouble with my flight numers (EG1642 for example). Saying they are too complicated and to split it up using variables??

it is not the flight numbers that are too complicated, i have recreated your database, but have split the original tables down even further than you have so each table only holds the information relevant to the table.

i.e

tblAircraftInfo - only contains FlightID (PK) holding the flight numbers EG1642 etc, PlaneCapacity, ScheduleID (FK) - This table is simply info about the aircraft.

tblSchedule - only contains ScheduleID (PK), DepartureDate, DepartureTime, FlightTimeHours, FlightTimeMinutes, FlightID (FK) - This table only holds information regarding the flight schedules.

Look at your tables and what you are trying to do. Try to normalize everything, if your are confused about normalization do a search in these forums on the term normalization.

 

[ScottGem]

Flight Number was the primary key. Ok, gonna have to have a look at some code I guess...

That's why I said you need two tables. Looks like Daherb is giving you the same advice.

 

[accessistough]

Ok, I think all my tables are ok now. Please can you check the 'tblBookings' table I have made. Would you say what I have in there is correct? If so I can delete the corresponding fields from the 'tblPassengerBookings' table.

 

[daherb]

Yes what you have in your bookings tbl is good, make sure you change the data types of flight, journey & trip to number, long integer not text or relationships wont work when you come to make them.

 

[ScottGem]

This is getting better, but its far from complete.

You really need to stay away from spaces in object names. I would get rid of tblTripDestinations and replace it with this:

tblAirports
AirportID (PK Autonumber)
AirportCode
AirportName
City
Country

Then your tblAircraftIfno would include OriginID and DestinationID as FKs to tblAirports.

In tblFlightSchedule Departure should be a single field holding the date AND time. FlightTime should be a single field showing the minutes. You can extract hours if you know the total minutes. Also its not clear what is the join to tblAircraftInfo. Is it FlightID or ScheduleID?

 

[accessistough]

Also its not clear what is the join to tblAircraftInfo. Is it FlightID or ScheduleID?

Thanks again for the tips on the tables. I have made the required changes as you suggested.

Scottgem, in response to your above quote, ScheduleID is the join to tblAircraftInfo, I forgot to set the FlightID to a PK.

Also, a quick question. Why is it important that spaces are removed from field titles and such? I have removed them as suggested but am curious to know the reason behind the importance of leaving out spaces.

Thanks again.

 

[ScottGem]

Also, a quick question. Why is it important that spaces are removed from field titles and such? I have removed them as suggested but am curious to know the reason behind the importance of leaving out spaces.

Thanks again.

When you refer to a object that has spaces in the name, you need to put it in brackets, otherwise Access won't know its an object name. If you forget you get syntax errors. If you don't use spaces, you don't have to worry about that.

 

[accessistough]

Please could you take a look at the relationships I have set up? I have made all the changes to the tables, i think they are all ok now... slowly getting there...

Do I need 'TripID' in 'tblbookings'? I can't see what I can link that to.

 

[ScottGem]

Getting there. You need to add another instance of tblAirports and link that to DestinationID. I would just use Departing as the field name. Definitely not use DepartureDate&Time as the & is a special character. I'm not sure what TripID is either. the relation from FlightSchedule to tblBookings should be ScheduleID not FlightIDthe relation from AircraftInfo to FlightSchedule should be FlightID. Remember, you generally go from PK to FK.

With those changes you may be able to start on a form. A main form bound to tblBookings. On that form would be a combobox bound to PassengerID to select the Passenger. I would then have 2 UNBOUND controls to enter a date range and 2 unbound combos to select the origin and destination airports. I would then use those controls to filter the list of flight to match that criteria.

 

[accessistough]

Getting there. You need to add another instance of tblAirports and link that to DestinationID. I would just use Departing as the field name. Definitely not use DepartureDate&Time as the & is a special character. I'm not sure what TripID is either. the relation from FlightSchedule to tblBookings should be ScheduleID not FlightIDthe relation from AircraftInfo to FlightSchedule should be FlightID. Remember, you generally go from PK to FK.

Added another instance of tblairports and linked it as suggested, ive also linked the two airport tables together by the AirportID if this is needed?

when you say that tblFlightSchedule should be linked to tblBookings by scheduleID, are you saying I need to create an extra field in tblBookings called ScheduleID?

Sorry if this is an obvious question... thanks very much.

 

[ScottGem]

No, you do not want to join the 2 instances of Airports.

As I said your links are from PK to FK. FlightID is the PK in AircraftInfo so its the FK in FlightSchedule. ScheduleID is the PK in FlightSchedule so you should have that as an FK in Bookings. Delete FlightID and replace it with ScheduleID or just rename FlightID.

 

[accessistough]

Right OK. I think my relationships are OK now, unlinked the two Airport tables. Started on this form now...

I'm having difficulty getting the first combo list (the one that selects the passengers ID) to show me the passenger ID's to select, it just displays an empty box, scrolling through the records at the bottom the number changes ok though...

Are the unbound boxes ok?

 

[ScottGem]

Did you use the Combo wizard to build the combo?

You don't want to show the IDs, what you want is to show the names but store the IDs. The RowSource should be:

SELECT PassengerID, Surname & ", " & Firstname As Fullname FROM tblPassengers ORDER BY Surname, Firstname;

 

[accessistough]

It asks me to display a parameter value but wont show the names in the drop down list. i created it using the combo wizard.

I entered this into RowSource:

SELECT PassengerID, Surname & ", " & Forename As Fullname FROM tblPassengers ORDER BY Surname, Firstname;

 

[daherb]

The reason it asks you for a parameter is because you have told it to order the results by surname and firstname, yet you do not have a field called firstname, the field is called forename

 

[accessistough]

I realised i didnt have a field called 'firstname' so I changed it to 'forename' but for some reason it didnt like it.

Is the fact that I don't have a field called Fullname an issue?

 

[daherb]

The thing i dont understand is why are you trying to have the passenger name in a drop down???? surely the clerk is going to have to enter the name of the customer as they are making the booking, because the customers name is not going to be in the system until thgey make a booking. The only things you would imagine are already in the system are the schedules, airports, flight times, etc.

 

[accessistough]

I think this must be for when the passengers have already booked... When they are already in the system. To alocate them a flight time.