Anyone willing to help me design a database?

[accessistough]

Hello everyone. Im relatively new to access, i've only been using it for a few weeks. There are certain aspects that I cannot get my head around.

Is anyone willing to let me email them what I have done so far and the criteria for the database I am making and advise me where I am going wrong and where I need to be heading to get it right?

Thanks very much. Any help will be very much appreciated.

 

[daherb]

i dont mind having a look

 

[accessistough]

As you can see, its pretty complicated, or it is to me anyway... any ideas?

 

[accessistough]

Anyone else willing to lend a hand? Really need help here.

 

[ScottGem]

just ask your questions here. If you need to attach a copy of your database Zip it and Attach

 

[accessistough]

Here is what Ive done so far. Ive developed it from a hospital database so ignore the random reports.

For a start, I cant figure out how to link them all to the passenger booking table so all the lookup tables ive made for it work. Help with that for a start would be much appreciated.

 

[RV]

For starters, you should add a primary key to your "Flight Information" table.

Second, do not use lookup columns.
Replace them by tables, as far as you haven't created those tables yet.

Third, do not use spaces in object names.

Remove the column called "Date To Fly" from your "Passenger Booking" table.
It's already in your flights table.

Realize that most of your tables (all expect "Passenger Booking") are merely used to lookup data.
Create a form that is based on your "Passenger Booking" table.
After you've created your form, open it in design mode and add listboxes, based on the appropiate tables.
See to it that the listboxes are bound.

RV

 

[accessistough]

Thanks very much for the help. Can I not leave 'Date to fly' in? As in the clerk operating the system asks the passenger what date they wish to fly?

I have already created a form in a previous database I did, is there a way to import it?

As for the other things you mentioned, i'll try to tackle them and post an updated version, thanks again.

EDIT : When you say to not use lookup columns... and instead use tables. Do you mean this as in create tables which will store i.e. All the passengers going for a One way trip to Paris?

 

[daherb]

if you have a form that you have already done in another database and you want to import it into your new database just click on "file", "get external data" and then click "import" locate the old database on the computer and select it. you will then get a new window asking you want you want to import, click on the "forms" tab select the form you want in the list and click ok.

NOTE: you will need to change the forms properties in design view to link it to your tables.

 

[daherb]

also a good tip would be, when naming forms, tables, queries etc try to keep to a standard format such as tblFlightInformation for tables qryFlightQuery for queries and rptFlightReport for reports etc...

 

[daherb]

Thanks very much for the help. Can I not leave 'Date to fly' in? As in the clerk operating the system asks the passenger what date they wish to fly?

if the date to fly information from your "Passenger Booking" table is going to be different data from that held in the "Flight Information" table then it would sense to leave the column in but surely if the customer wants to fly on say 16.10.05 they would only be able to fly on this day if there is a flight leaving on this day. so really in my opinion there is now need for this column in the table as it would be up to the clerk operating the system to find the flight available closest to this date.

 

[accessistough]

Cheers daherb. Ive renamed them all now. I need to show my methods for making it so I think its best if I leave that in... its not actually going to be used for a real airline. I can show, by leaving it in, that I have thought about the passengers needs.

I can take the date they wish to fly down and match them with the closest possible date available.

With regards to linking all these tables. Could you guide me through that?

With the form for entering the information, should I only link it to the main booking file?


Another thing... Is there a way so when I select that the passenger wants to go 'one-way' from the drop list for example, it will only display the one way trip types in the next drop down list.

Just so i dont have a huge list of every trip type avaliable...

Here are the criteria for the database below...

 

[daherb]

make the flight number field in the "flight information" table a PK (primary key). Change the field names of journey type and trip type to journey id and trip id. by doing this the last 3 fields in the table are now your FK's (foreign keys)

Then open the relationships window by clicking "tools" and then "relationships"

add all your tables if they are not already there (right click, show table) then click the primary key (shown in bold) from your child tables (such as "flight information", "journey type", "journey destination") drag and drop into the master table (in your case "passenger booking"). in the window that pops up make sure each side has the same field name. i.e left side "flight number" and right side "flight number" (this is your PK and FK) then click create join. The join you want at the moment is only include matching (the one that is already selected)

 

[accessistough]

Thanks, Ive made the changes that you suggested. When making the form it won't allow me to have values such as 'one-way' in the trip ID. So would the clerk just have the enter values such as 1 2 and 3? instead of 'one-way', 'return' and 'tour'.

Here's my revised version of the database.

 

[ScottGem]

Looking at your criteria document (which is the same thing you sent me privately) this looks like a homework assignment. If so, then suggesting that you would pay me to do this for you, is very wrong.

Most of us will not do homework assignments. What we will do is critique your work and make suggestions for improvements. So far, RV and Daherb have given you good advice.

You asked; "Is there a way so when I select that the passenger wants to go 'one-way' from the drop list for example, it will only display the one way trip types in the next drop down list." This is a standard technique called cascading or synchronized combos. If you search here on those terms you will find instructions on how to do it. However, the question shows a lack of understanding of the airline industry. Flights have a start point and a destination. So essentially all flights are one-way. What is either one-way or roundtrip is the FARE not the flight. A passenger will book a trip as round trip because there is often a discount to do so rather then just book 2 one way trips. So you need to separate the issue of flights from fares.

You should laso have separate passenger and bookings tables. Your bookings table is essentially a join table, joining the passenger with the flight. You also need two tables for flight info. One table for the flights and another for the schedule. For example, you might have Flight 445 from NY to Chicago. It is scheduled for every Wednesday at 9AM. So you need a table that lists the departure dates separately from the flight.

 

[daherb]

Another thing to think about is that in order to create your forms i.e the one you already have created correctly you are going to need to create a query for the form to be based upon. Use a query to bring together the information required on the form so that you can bind the form to said query not an individual table like you have.

 

[accessistough]

You also need two tables for flight info. One table for the flights and another for the schedule. For example, you might have Flight 445 from NY to Chicago. It is scheduled for every Wednesday at 9AM. So you need a table that lists the departure dates separately from the flight.

I have made two tables as suggested, how do I make it a weekly occurance that the flight happens? Rather than having to type all the dates in until the end of time?

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??

 

[accessistough]

Another thing to think about is that in order to create your forms i.e the one you already have created correctly you are going to need to create a query for the form to be based upon. Use a query to bring together the information required on the form so that you can bind the form to said query not an individual table like you have.

It doesnt seem to like the query I made for the form. I just need all the information to go into the Booking form dont I?

Its just the journey type and trip type im having difficulties with. I dont want the clerk to have to know which numbers to enter. Just select the words from the combo box and have it replaced by the numbers in the table... if that makes sence...

 

[ScottGem]

I have made two tables as suggested, how do I make it a weekly occurance that the flight happens? Rather than having to type all the dates in until the end of time?

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??

Airlines don't plan that far ahead. You need to decide how far ahead you want to schedule. You can use loops to add records for specified periods.

 

[accessistough]

thanks scottgem.

Say I want to plan just a month ahead. Ive done a search on loops and can only find information on writing code. Having never writen code before Im not sure what Im doing with regards to that.

I thought I could simply repeat the flight numbers and type new dates up to a month ahead but since it is the primary key, i cannot repeat the fields...