Linking data in two seperate tables

[gavlap]

I'm not that advanced when it comes to Access but I should be able to figure out what I need to do if someone could point in the right direction. I have set up a travel agent DB and I'm trying to integrate some new tasks in to the DB that we currently do separately. The thing that I need help with is creating tickets which we currently do manually in word.

I have an [Enquiries] table which stores the client details, which joins to a [Passengers] table and a [Quotes] table using an [EnquiryRef]. The [Quotes] table then joins to various tables which include a [Flights] and a [Products] table using a [QuoteRef].

The area I'm struggling to get my head around - if we book a flight with 'Emirates', we would enter the relevant booking references, costs etc in the [Products] table. However to create the tickets, each passenger would have their own e-ticket number for this Emirates product. What's the simplest way of entering a different e-ticket number for each passenger?



I know this info could be added in to the [Passengers] table but my concern is that some [Quotes] have several flight bookings, so it could get messy linking it all together.

Any advice would be really appreciated.

Thanks in advance.

[recyan]

Posting your current ERD along with a detailed explanation of the relationships between the different entities, the process and the business rules, might make it easier for someone to help.
As a starter,
A) The ERD
B) Just thinking out loud,
1) You have many CLIENTs.
2) Each CLIENT might ENQUIRE for One or many PASSENGERS.
3) A PASSENGER at any given time, might belong to the same or different CLIENTs.
4) A PASSENGER over a period of time, might belong to the same or different CLIENTs.
5) An ENQUIRY generates single or multiple QUOTATIONS.
6) A CLIENT might accept any one QUOTATION or reject all QUOTATIONs.
7) An acceptance of a QUOTATION by CLIENT, leads to BOOKING of Tickets.
and so on.

Thanks

[gavlap]

Sorry what's the ERD?

Each of the [Enquiries] could have one or many [Passengers], but these passengers only relate to 1 of the [Enquiries]. Each of the [Enquiries] could have one or many [Quotes], but each of the [Quotes] will only relate to 1 of the [Enquiries]. All the related passengers and quotes are then linked to the [Enquiries] with an [EnquiryRef].

Each of the [Quotes] will have many [Flights], it will have one or many [Hotels], it may have [CarHire] or [Excursions], and all these are all linked to the [Quotes] with a [QuoteRef] which then builds the itinerary. There is also a [Products] table where all the different costs are entered for the [Quotes]. There will be many [Products] for each of the [Quotes], but each of the [Products] will only relate to one of the [Quotes]. All the [Products] are linked to the [Quotes] by a [QuoteRef].

I hope this helps, thanks, Gav

[gavlap]

Also I just realised I never mentioned.. although there can be several [Quotes] for each of the [Enquiries], there is only ever 1 Quote per Enquiry which converted in to a 'Booking' - which we would then use to create the tickets.

[recyan]

ERD is Entity Relationship Diagram.
In access, you will be able to work on it in "Relationships" under "Database Tools".
Getting the ERD right, is of utmost importance, before proceeding with other things, in any database.
In case, you have not done it so far, would suggest getting it done & submitting it here, for others to have a look & advice of any pitfalls.

Thanks

[gavlap]

ERD.doc I think I've attached it correctly... the [Travelmodule] is our 'Enquiries' Table. Thanks

[recyan]

The attachment ( ERD ) is what is required.
At first glance, looks like, more interaction will be required with you, to understand things.
Just hold on till someone comes across this thread.

Thanks