Full Version: Help needed with database
UtterAccess Forums > Microsoft® Access > Access Forms
DCXtreme
Help Needed With Database
-------------------------------------------------------------------------------
I have constructed a seven table database for dealing with holiday cottages.
The seven tables are:
Bookings
Cottages
Customers
Ratings
Owners
Regions
Sales Reps
I have created the 7 tables and created the primary keys (BookingID,CottageID,CustomerID,RatingsID,OwnersID ,RegionsID,RepID)
The ratings table because cottages come under different ratings (Family, Sporting etc..).
I need to create a form which
*Adds a new booking, and also adds the new customers details
*Checks the avalibility of the cottage for the chosen week
*allows the user to choose a rating from a combo box, which then filters the cottages availible for selection in that category.
If anyone could help me with this I would be very grateful, I have emailed my lecturer but he is not replying and the assignment needs to be in on the 12th!
dannyseager
Welcome to UA!!
would merge the owners and customers into 1 table (people) with a yes no field to indicate whether a person is an owner, a customer or both.
I would then have a cottageRating table which acts as a junction table (enabling a cottage to have many ratings (it could be sporting and for a family)
ScottGem
First we do not do homework assignments here. What we will do is critique your work and make suggestions for improvements.
o you need to create your forms and attach a zipped version of your file to a note here. We can then look at it and tell you whether its OK or what's wrong.
Two things I will say. A Reservations database is one of the hardest to build because of ot the variables of time. It sounds like the assignment may have been simplified to only allow bookings on a weekly basis. That does make things a lot easier. The only thing that is needed in your Bookings table about the Customer is the CustomerID. All other info about the customer can be gotten through a join.
HTH
DCXtreme
Ok thanks, Yes a booking lasts a week and only a week if that makes sense, for multiple weeks you must make multiple bookings, i'll attach what i've done so far.
ScottGem
Well I took a look at what you have and you have a lot of work to do.
) Its not advisable to use spaces in object names. That can cause problems.
2) Your relationships are wrong. Each booking is done by a Customer. Therefore CustomerID should be a foreign key in the Booking, not Booking ID a FK in Customer.
3) I agree with what Danny said about having one people table and putting Owners, Customers and Sales Reps as people with a flag to identify what they are.
4) Your rating table is denormalized. Using checkboxes like that constitutes a repeating group. wehat you should have is 2 tables like:
tblAmenities
AmentiesID (PK Autonumber)
CottageID (FK)
AmenityID (FK to tluAmenties)
tluAmenties
AmenityID (PK Autonumber)
Amenity
Each Amenity that a cottage has should be a separate record in tblAmenities. the tluAmenities table is a lookup table listing all the amenities available.
This also means the Ratings are a separate issue and should be kept separate from Amenitities
5) Region in the Cottages table should be a FK related to your Regions table.
Once you clean up these issues, you can start on the design of your forms. The only form you have is done incorrectly. Bookings should be a subform of Cottages.
Aquadevel
Here's a link to a hotel flow-chart design that may help you,
http://www.databaseanswers.org/data_models/hotels/index.htm
Good luck,
Aqua
DCXtreme
Ok I did what you said. Seemed to be ok except when i checked relationships i now have customers_1 aswell!, problems! Should i get rid of the Ratings table and just put the rating in the cottages table, seeing as the amenities are now in a seperate table?
I've attached the updated database again, thanks so much for your help.
P.S I have not yet put all the customers/owners etc into one table, I am not familiar with "flags" could you fill me in?
Jon
ScottGem
Much better! Though I'm not a fan of using naming conventions for fields, but that's a personal preference. You didn't take BookingID out of Customers by the way.
would change tblRatings, into tluRatings. Make it a lookup table with a foreign key in the Cottage table.
A flag is simply a field that identifies a record in some way. You would simply add a field for PersonType. I would then create a tluPersonTypes to hold the possible values.
The reason why you got the second version of the customers table is you had duplicate relations. Once you combine into one people table, you will need to have "aliases" for example to relate a Salesrep to an owner.
DCXtreme
Wow.. i never knew i had so many problems in my database to begin with!
ew things all the time, "Aliases" sound complicated to me!, i've now merged the two tables into tblclients and fixed the other things you said about, although where to go from here i do not know!
Jon
ScottGem
Alias simply means to rtefer to the same table using a different name. To use circular links you have to use aliases.
From here you want to start designing your input forms. One thing to remember is wherever you have a Foreign key, you should use a combobox to select the value from the related table.
DCXtreme
OK, i really am a dumbo with forms! I've created one but have no idea how to be able to select different cottages and i wont even get on to the subject of validating the dates, nightmare!
Edited by: DCXtreme on Fri Jan 6 16:32:10 EST 2006.
DCXtreme
Hey Again, I've updated my database and I think its nearly done, all that is missing now are some relationships in the relationships menu and the "aliases" needed to make the rep table work, and of course.. the forms sad.gif
dannyseager
See attached.
I've fixed what I thought needed fixing. frown.gif
[*] I've corrected your naming convention (you had added str before every field, str = string)
[*] I've removed the lookup's at table level... they are a problem you can do without (if you have them then you never see a true representation of the data. - Do the lookups at form level)
[*] I've moved your reps, owners and customers into 1 table.
[*] I've added an area table
There are also lots of other little things...
The only change I've made which people might not agree with is that I have added a slight repeating group in the person table... In this case I don't think it's an issue
DCXtreme
Ok..WOW, yeah i knew the naming convention was not perfection, looked good to me though - what do i know tongue.gif
would feel bad about you doing all that but I had actually spent hours on it last night trying to change it into something good and did get somewhere..so i dont fel like i havent done all the work, i got to where you did, its just sloppier!
I've had a look over the database and everything seems perfect
I just have 2 follow up questions, if i may!
1. I set it out so that the ID & Name would display in most tables, so that the user has an idea of what they are selecting, would I be able to have it like this again? I dont want to mess anything up, infact its not essential, I would like to have it like that in the forms however
2. Ahh yes forms, my original problem!, I have been looking into date validation and have found some lines of VB code which would help in validating if a cottage is booked for a certain week (7days) but I have no idea how to apply this, i lack common sense!
dannyseager
1.) The user doesn't see the tables.
hey only see the nice looking forms and reports.
You can make the field a combo box on the form and let the user see what they are selecting... just keep that at form level.
If you need a demo of this let me know.
2.) Can you post the code? I could write some but if you already have some we might as well start from there.
ScottGem
First, We have seen that you are trying to do the work, whihc is why Danny was willing to go the extra step and clean things up a bit.
would still make a couple of more changes. First I would add SalesRepID to the Cottage table as per your other question, so that you know what rep presents what cottage and therefore what owner. Second, I would not have three yes/no field to indicate Person type. I would have one field and then a lookup table for the type.
Now getting on to forms.
Use the wizard to create forms to enter data to each table. This will help familiarize yourself with how forms work. Make sure you replace all your foreign keys with Comboboxes using the combobox wizard. Once you have then this, you can start fine tuning your forms and building subforms to deal with creating bookings.
For preventing duplications, that would be easy since you are dealing with weekly bookings. A booking should have a start date (either a Sunday or Monday or whatever), that would be the date stored in DTBookingWeek. You would then create a unique multi-field index on DtBookingWeek and lngCottageID in the Bookings table. This would prevent booking a cottage twice for the same week.
DCXtreme
Ok, I've made the changes you recommended scott, I created a few forms and turned the FKeys there into combo boxes using the wizard, the problem I have is that the ComboBox does not change the data displayed. For example if i goto record one and select Customer, then goto record 2 Customer is still selected, it does not show the information already inputted into the tables.
I'm sorry but I dont quite understand how I would go about the date validation, unique multi field indexs are spanish to me!
Updated file attached, thanks again
ScottGem
One thing you have to start doing is using Access Help. If you look up Mutli-field indexes you will find out what they are, how they work and how to create them. If we use any terms that you are unfamiliar with, look it up. If you still don't understand feel free the ask for clarification. A unique mutli-field index means that the combination of fields has to be unique. In your system you can only have one record for each cottage for a booking date. By setting a unique index on the combination of those 2 fields, you prevent double booking.
Is for the controls you didn't bind them. You left them as Unbound. This separates them from the field int he table. That's why changing records doesn't reflect them. When using the wizard you were prompted if you want to store the selected value in a field, you apparently chose the other option (Save for later use) insterad. To bind a control to a field, slect the field as the control's data source.
DCXtreme
Thanks alot, sorry, I will look up the terms you used, bad habit of mine to keep asking for help, thanks for the tip on the unbound combo boxes, i'll get that fixed now.
DCXtreme
Ok sorry to bother you guys again but i've discovered a small problem which I didnt realise I had to account for.
rep can only be allocated to one region, but currently I can have a rep looking after cottages in more than one region, i'm baffled by this, dont quite know how to query it so that only the reps for a specific region show up in the form when I go to add a cottage, because i dont select the cottage region until i'm already in the form!
Updated mdb included.
ScottGem
This is a standard technique called cascading or synchronized combos. Basically what you need to do is filter the SalesRep combo based on Region. For that you will need the RegionID as a FK in the People table. You then need to change the RowSource of the SalesRep combo to reflect the Region. Something like:
SELECT PeopleID, [Surname] & ", " & [Forename] As Fullname
FROM tblPeople
WHERE PersonType = 4 AND Region = Forms!formname!cboRegion
Orderstandard technique called cascading or synchronized combos. Basically what you need to do is filter the SalesRep combo based on Region. For that you will need the RegionID as a FK in the People table. You then need to change the RowSource of the SalesRep combo to reflect the Region. Something like:
SELECT PeopleID, [Surname] & ", " & [Forename] As Fullname
FROM tblPeople
WHERE PersonType = 4 AND Region = Forms!formname!cboRegion
ORDER BY Surname, Forename;
The syntax:
=Forms!formname!controlname
Allows you to reference a value inthe specfied control on the specified form.
DCXtreme
Thanks for that bit of code, I think it will prove useful in what I am trying to do next, I just read through the spec again and while a region can only have one rep, a rep can have many regions so I dont need the code for what I orginally thought.
I have abit of a challenge for you anyway, on my bookings form I need to be able to select a rating from a combo box and have the rating selected filter out only the cottages with that rating in the below combo box, very similar to the code above I would imagine, except RatingID is not a foriegn key in Bookings and I cant see where I would store the information, for example in the above code you refer to information which is in the current table, I need to check information which is in the cottages table (the rating ID)
Grr
ScottGem
It not a problem. And you are right its very similar. When you select a cottage you are doing so from a combo that queries the Cottage table. If you need to filter the cottages according to a certain rating, then you just need to add a combobox to your form to select the rating. This is where what you learned about bound and unbound controls comes into play. All controls on a form do not need to be bound to a field in the table the form is bound to. You can have unbound controls if you need to. This is a case where you need to.
o just add an unbound combo to select the rating and reference that in the cottage combo.
DCXtreme
Ok, I tried that, infact i've been trying for quite a while and i've come up blank!
This is my code that I tried in the Bookings form
SELECT tblCottages.LngcottageID, tblCottages.strCottagename
FROM tblCottages
WHERE tblCottages.LngRatingID=Forms!frmBookings!cboRating
Orderd that, infact i've been trying for quite a while and i've come up blank!
This is my code that I tried in the Bookings form
SELECT tblCottages.LngcottageID, tblCottages.strCottagename
FROM tblCottages
WHERE tblCottages.LngRatingID=Forms!frmBookings!cboRating
ORDER BY [LngcottageID], [strCottagename];
I'm at a loss to see where I went wrong with this code, but after I try and run the form no matter which rating i select i only can choose one cottage, the one that was on the record in the first place!
ScottGem
What you are going to have to do here is use 2 forms. One for entering data and one for viewing data. I playyed with form and found that if you are filtering the combo by region, even if you have already selected the Cottage it suppresses the display.
I've add a form to Add bookings. And modified the existing form. I also set the multi-field index for you.
DCXtreme
Thats terrific, I'm glad it wasnt something very simple or i'd have been mad with myself.
I've checked my relationships and everything seems fine, I'm not missing anything glaringly obvious am i? Just want to know before I print them out!
Jon
ScottGem
I think things are pretty good now.
DCXtreme
Hi there, thanks again so much for all your help, just wanted to say thank you as I seem to have things tied up now, I think i've actually learnt more about access here than from my tutor! :-\
see that the multi field index is working in as much as it will say if the cottage has been booked on a selected day, but it does not check for the 7 days ahead, the full week. Is there any way of doing this?
Jon
ScottGem
I discussed this earlier. That's why I focused on the issue of their only being weekly rentals. If you can only rent the cottages for a week starting on a specific day, there is no need to check its daily occupancy. If the week starts on Sunday and the Cottage is rented on that date, then its assumed to be rented for the subsequent week.
You might want to restrict the date entered to the start of week day (Set a verification to Weekday(StartDate) = 1)
If you change the parameters to allow weeks tro start on any day or to allow rentals for less than a full week, you greatly increase the level of complexity of the app.
DCXtreme
Ahh i see, i missed that part, you're absolutly right, i was thinking about how to limit it to the start of the week but didnt have a way to do it, again, thanks
ScottGem
Let us know what grade you got wink.gif
DCXtreme
I will do tongue.gif
still cant get the date validation to work, even after entering what you told me to in the validation box! [censored]
NocOps321
hey guys, i have a question for you, im new to access and am having trouble locking a scroll field.......i want to give you a little info about what i am try-n to do..... i have an access database that i pulled in some info from another database, in the window of the main screen i have a box to pull up the info from the other DB and i was hoping to lock the scoll wheel from moving to new info in that field to something othere then what i look up?
Thank you for you time
Thomas
ScottGem
Thomas,
ts not a good idea to piggy back on someone else's thread unless your question specifically relates to that thread.
Please repost your note in the Forms forum and we will try to help.
ScottGem
I just tried it onthe table level and it worked fine.
DCXtreme
I got "The expression is typed incorrectly, or is too complex to be validated.
atabase attached.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.