How would you create a trip enquiries form?

[Sharky II]

Hi guys

I am making (again) a database for a travel agency. One feature which is needed is a trip enquiry form - ie, a customer calls up and says hi i'm interested in going to italy, spain, germany, america, and new zealand. I need some way of recording this.

It doesn't matter which trips the customer chooses to go on in the end - i just want to record which trips they were initially interested in - that way when a new super-deal comes out for spain, the company can contact everyone who has shown interest in going to spain. The thing is that:

a) you can't just have checkboxes and a list of countries (i wish you could though as this would be easiest) because this wouldn't allow the company to edit/add to the list of trips available without messing about with access forms,

b) the user must be able to register interest in more than one trip

c) the trips which the customer has shown interest in have to be searchable by the search by query method

Any ideas as to how to approach this? I was thinking of having all the available trips in a list box, then upon clicking the trip it puts this into a table of trips that the customer is interested in - if you get me? But then would you have to have a table for EACH customer or, even a table for each trip???

I'm a bit puzzled as to how to pull this off, yet make it look professional and easy to use? Any ideas?

Thanks guys, i need your help on this one

Edd

 

[Sharky II]

Hi

Just an update to show yo umy line of thinking

I've made it so there is a many to many relationship between customer and tblEnquiries - and there is a linking table called tblEnquiriesDetails which has the customerID and enqiruiesID as foreign keys etc etc

Now, i also then created a form called frmTripEnquiries for the tblEnquiriesDetails table and - this is where it gets messy - i've changed the enquiriesID to a listbox and the query as all the items in the enquiries table so that you can see which trip you are choosing - i.e. 'selecting a trip/enquiry' is just an elaborate way of choosing the enquiryID.

Furthermore, i've also got a listbox which displays a query created called qryCustomerEnquiries - this has the customerID and all the details of the enquiries - basicaly created to show which trips that customer has enquired about.

If possible, i need a way of making it so that when you select a trip from the drop down, you don't change the trip that is selected, but a NEW trip is added to the list of trips the customer has enquired about. I could do this by using the navigation button of * but that would be VERY messy and not very intuitive. I want it so that you just select from the drop down and it goes into the list of enquiries. What i could do is set the default view of the form as 'continuous forms' and so everytime you select a trip a new form comes up (underneath) and you can choose loads of trips for the customer - but i think that is kinda messy.

Oh, and i'd want to be able to remove stuff from the list - resumably by clicking the item then clicking a remove button? How would i wire up a button to do this for me?

If anyone could help i'd REALLY appreciate that, i'm pretty stuck

Cheers guys

Edd

 

[Sharky II]

anyone?

 

[Sharky II]

I've got a packet of jaffa cakes sitting here for anyone who can help

 

[JoeBeach]

I've got a packet of jaffa cakes sitting here for anyone who can help

As you have a table for Customers, and assuming separate table for Trips, try this.

You’ll use your tblEnquiries table with its customerID and enquiriesID, by having a separate record for each enquiry/customer. As example, the tblEnquires’ records will be store the ID’s for Paris and Smith, and Cancun and Smith, and Paris and Jones, and so on.

To make it work, you need the help of a subform. Create a tabular/continuous form for the tblEnquires placing the customer ID and enquiresID on it, and making these fields not visible. Then place a combo field on the form linking to the trip name field of your Trip table. Use the wizard on this, and when asked where to save the chosen info, choose the enquiresID.

Clean up the form, getting rid of record navigation displays and make sure the combo field is the only one you can land on, then save it. Go to the appropriate form for the customer and create the subform using the little tab form you just created, linked by the customerID.

That’s about it. From the main form when creating a customer or editing a profile you can add one or more of these trip preferences. The result is the tblEnquires populated, where you can create queries about who originally wanted to go to Cancun, etc.

That takes care of your b) item and c) item. As far as a) it’s up to you whether you want to make the combo limit-to-list, make an add to trip list option, etc. You can add an action to go to an add trip form if you want.

Hope that helps or at least gives you more ideas.

 

[Mile-O]

But then would you have to have a table for EACH customer or, even a table for each trip???

One table for customers. One table for locations. One junction table between them to simulate the many-to-many relationship.

 

[JoeBeach]

Re: Re: How would you create a trip enquiries form?

One table for customers. One table for locations. One junction table between them to simulate the many-to-many relationship.

Thank you. That's it in a nutshell. You use the subform input to generate the records in the junction table that consists of the ID#'s for the customer and the ID# for the trip preferred by the customer.