Travel Agency Database - advice and help appreciated!

[Sharky II]

Hi guys

I've been lucky enough to recieve loads of help from WayneRyan with my database. We both thought it would be useful to cut the DB down to size so i can post it up so that others can have a look at it and offer help/advice as i'm struggling with it a lot!

Basically....

I have to create a DB for a travel agency which allows them to store customer info, trip info, and the links in between! The DB is basically customer driven and needs to display customer info on the 'main' page.

The user should use the DB as such:

1) Telephone call is taken where user enters the customers' details (name, age, address etc) and then ticks boxes of the trips that the customer is interested in (this is the point of all those tick boxes).

2) a week or so later (once travel agency has recieved a detailed written Booking Form back) other details are entered into the DB (such as trip info/insurance info/payment info/tshirt size/bike frame size/ etc).

3) the user should be able to search for any customer in the db, and also display all the trip (and other) info when a customer is displayed after a search. also (and what is causing the hassle), is that the user should also be able to search for all customers going to Spain, or all customers who are going on a particular date, or all customers who have tshirt size XL etc.

This is a prob because i originally had a customer table and a trip table - there was a one to many relationship - but there were problems when i wanted to search for a customer by a field in the trip table (ie if we searched the trip table for all people going to spain, only the CustomerID which linked the two tables came up - i needed to display all teh customer details however).

Sorry if this seems really detailed but i'm pretty lost and any advice at all would be great.

Thanks again.

 

[WayneRyan]

Edd,

There are many fields in the tblTripDetail that should be
moved into the Customer table. You're probably the best
judge of which ones could change per trip and which ones
stay the same for the customer.

I'd just study it for now and, at least for the forum, not post
another db until you get "some" changes.

Wayne

 

[Jack Cowley]

First thing you need to do is normalize your tables. Delete all the tick boxes from the Customer table. Make a new table similar to this:

tblCustomerTrips
CustomerTripsID (PK and autnumber)
CustomerID (FK)
DestinationID (FK)
TripID (FK) (from your table tblTripDetail)
...other data that you might need...

You need to link tblTrips to tblDestinations as well. You have used TripID in two different tables and this will not work. Change the name of one of them.

I am NOT an expert on relationships, so the above may not be exactly what you need and hopefully someone will correct any of my mistakes. But I do know that you need to get rid of the checkboxes in the customer table and create a table similar to the one above.

hth,
Jack

 

[Sharky II]

Hi guys, thanks for the replies.

Wayne i think it might be anal but most of the things in there could, possibly, change from trip to trip. The chances are slim, but people could change nationality (and thus passport details), tshirt size, shoe size (if a kid is booked the size could change v. rapidly) etc. Actually maybe i'm talking rubbish. Because they won't change v often the user could just change the info on teh main customer form directly. Ok i'll carry on making them changes

Jack, sorry for the stupidity, but that new table, tblCustomerTrips, will that be to replace the tickboxes ro what? How is that suppsoed to work and what will it do? I've created it and created teh links like you want but dont' know what to do with it now?

Because the tickboxes have very little significance (they just show who has been interested in what, a silly touch the company insisted on) do they really need to be moved about, or is leavign them there blasphemous in DB design?

I was thinking of getting round the need of having a many-to-many relationship by creating a relationship like in the picture attached?

Edd

 

[Jack Cowley]

I was not quite sure what you were up to so that was my shot at trying to make the db a bit more normalized. Your new setup should work as the tblTtripDetails is a many to many relationship and is necessary since One customer can go on Many trips and One trip can apply to Many customers.

As for the tick boxes in the customer table - They will never work for you as you cannot easily find out how many customers showed interest in particular trips. If the company insists on this then you need to make table, similar to one you have now (tblDestinations) that uses the CustomerID as a foreign key.

Your new 'junction' table (tblTripDetails) is the basis for a form/subform where the customer is the main form and your junction table and related tables are the subform(s). With this setup you can see existing trips for a customer and you can add new trips. You will, of course, need forms to verfiy space on a trip, dates available, etc.

"Booking" databases can become fairly complex rather quickly so you really must have your tables fully normalized or the bigger it grows the more difficult it is to get or add the data you need.

As I stated earlier, there are others here that are much better qualified than I to help you normalize your structure. If you are not clear about normalization there are a number of articles and tutorials on the subject. You will find one
here, here and here. (I hope I didn't duplicate any!) And you can search the MS KB for more information.

Good luck with your project!

Jack

 

[Sharky II]

Thanks for your help

I've foudn away around my search problem (ie if you search for a trip you can't find out which custoemrs are going).

Because of the relation i have used, i can create a query with loads of fields from the customer, tblTrips and tblTripDetails tables, and then this will allow you to search for trips, and it will bring up the relevant customers - non?

What i'll do is normalise the DB - all that remains is to sort the checkboxes out - although, you said that you cannot easily find out how many customers showed interest in particular trips - but surely you can just use the filter on the main customer form, and only tick (for example) Sahara, and the filter will bring up all customers which are interested in that trip - no? I do understand normalisation and normal form etc (amusingly i'm a 3rd year comp sci student) but i'm very new to access and i'm finding it hard to adjust to it and it's quirky ways!

I don't want to use the filter system however, the clients find it FAR too difficult to use so i'll have to develop a search facility that works just like it, but without having to click the filter button (ie it opens up a blank form where you fill teh fields with criteria and then click a 'search' button and then it 'applys the filter').

I might just do that and that way it isn't so complicated (and i can get my small mind around it!)

Any other advice appreciated

Edd

Cheers

 

[Jack Cowley]

It is getting late for both of us, you in particular, so let me say this about the tick boxes in the Customer form; what do you do if they want to add trips to Hong Kong, Sinapore, San Francisco and London?

I will be around tomorrow so if you have questions just let us know...

Jack

 

[Sharky II]

yup you're right

i'll carry on working for a bit and so hopefully tomorrow i can report an update

thanks for you help mate, get a good nights kip.

Edd

 

[Jack Cowley]

Edd -

It is still early here (8:30pm) but it must be 4:30am for you... Time for you to get some 'kips'! Access is not conquered in a night!

Jack

 

[Sharky II]

Indeed it's not - but in my quest to become king of access i have discovered the wonders of caffeine based drinks! this means that my sleep patterns have now changed from the norm (like, SLEEPING AT NIGHT) to the more irregular 7am-1pm sleeping shift.

My eyes have also turned an attractive shade of red also.

Upwards and onwards!

 

[Jack Cowley]

Good Morning Edd! You must have been up for hours by now... Good luck with your project, if indeed you are back at it...

Jack

 

[Sharky II]

Hey jack!

I kinda got a new problem

(NB i'm using a different form layout than the one in the file above)

Ok, i'm making a search form using the usual method of creating unbound fields etc etc. I am making an exact copy of the main form and it opens in a popup with blank fields and they do an afterUpdate customer11.Requery to update the

i am using the following query method:

SELECT Customer.* FROM Customer WHERE ((([Customer].[FirstName]) Like "*" & [Forms]![customerSearch]![srchFirst] & "*") AND (([Customer].[LastName]) Like "*" & [Forms]![customerSearch]![SrchLast] & "*") AND (([Customer].) Like "*" & [Forms]![customerSearch]![Srchemail] & "*"));

except that i have many more fields than just three (srchFirst, srchLast and Srchemail here).

Now the problem is that it's an AND statement. I get problems because i have to go to the main customer form (customer11) and obviously add the query as the record source (this is how wayne taught me to do it ).

Now, this is fine apart from the fact that - [b]because it's an AND statement, in the main form, if a user does not have one field filled in (eg if you don't know the users email address but all teh other details are there) then the users details will not be displayed in the main form - and obviously can not be searched for either. [/b]

If i use an OR statment or none at all, then it obviously all works but then i can't use my search query (i can't search then). If i set both the search form query to use "OR's" and the customer form query to use "OR's" then the search won't work.

My system would work perfectly if teh users details were always entered fully and every field was always filled in, but what if a user doesn't have a computer or mobile phone - then their details will not be displayed by the customer11 form - BAD DESIGN!!!!

So does anyone know how i can get around this??

Cheers

 

[Jack Cowley]

Edd -

You came to the right place! You will find exactly what you are looking for right here.

It works a treat in all situations...

Jack

 

[Sharky II]

Quote from that URL: "Advanced: Requires expert coding, interoperability, and multiuser skills. "

EEK!!!!!!!!

upon first glance i have no idea how this site is related to what i'm doing - so i am about to get stuck in

 

[Jack Cowley]

It looks intimidating, but I can assure you that you can do it. Just stick with it and once you sort it out it will become one of your favorite bits of code...

Just take each bit of code a step at a time and it will work for you... Heck, even I figued out how to do it so you certainly can!

Be brave and dive in....

Jack

 

[WayneRyan]

Edd,

The search example is just one of many ways to search. You
did not notice that the unbound search fields default to "*" and
the query uses LIKE. They do not have to fill in all (or even any)
fields to do a search.

Looks like you are having fun, there's a lot of info out there to
digest huh?

Wayne

 

[Sharky II]

fun is certainly one way of describing it!

*ahem*!

i don't know what the FLUCK TO DO!

 

[WayneRyan]

Edd,

Fix up your form and you'll have a new baseline.

Wayne

 

[Sharky II]

sorry i don't understand?

 

[Sharky II]

jack,

when i try to adapt that code to fit my needs/form, i get compile error: user defined type is not defined, with

Dim db As DAO.Database

highlighted in the sql editor.

i don't really know anyting about data access objects so what do i gotta do to make it work?

cheers

ps i hate access