Field for Multiple Sales on Single Record (1 Viewer)

[Opcode]

I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?

I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.

 

[wmphoto]

The answer to this I think is our old friend, the many-to-many relationship. This time connecting customers to flights with a 3rd table, which could be called customerflights, or in this case it is often normal to call the 3rd table orders, or bookings. The bookings table will then have a field for the customer, one for the flight and fields for additional information such as quantity, window-seat/vegetarian meal requested, etc.

To create a bill for the customer you then create a query which will show the bookings for a particular customer and pull the price for that flight from flights and multiply it by quantity.

 

[jdraw]

Further to wmphoto's comments, there is a data model at
http://www.databaseanswers.org/data_models/airline_reservations/index.htm

that shows a number of tables and relationships related to airline, flights, legs, reservations, passengers etc that might help put your issue into context.

In addition, the business rules that the above data model is intended to support are listed at
http://www.databaseanswers.org/data_models/airline_reservations/facts.htm

Good luck with your project.