Help Needed With Database

[DCXtreme]

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!

 

[Malcy]

Hi
As a starter for 10 you need to devise a query to drive the form. The query should draw all relevant data from the various tables.
I think you need to look at a range of forms to do the differing activities and you need separate queries to drive each form.
Can any one cottage have more than one rating? If it cannot, then simply add a field to cottages table and drop the ratings table.
The good thing about this is that once you know you query is pulling the right data then you can start to build the form.
HTH and good luck!

 

[DCXtreme]

How can I go about using the query within the form, I am very VB illiterate!

The ratings table contains information like wht facilities are in each cottage so i think it would be hard to put this into the cottages table.

I'm stumped

 

[Malcy]

Hi
The query becomes the record source for the form. If you click on New Form you get a pull down box to select where the records come from. Select your query and click on design view and then it will let you drag fields onto the form.
As far as ratings go, if there is a matching record in cottages and ratings on a 1:1 basis then I would put the ratings info into fields in the cottages table. There is a logic since it is a direct 1:1 relationship.
Does this help you move on?

 

[DCXtreme]

Well lets see, I've made alot of changes to the database now, such as taking out the spaces in object names and combining tables to make things easier, maybe someone could look into my database and give some advice.

To the above poster, thanks, I will try to sort out a query for the booking form now, but the whole thing seems so complex i'm not sure if i'll be able to get it to work correctly.

My database can be found @ http://jonroberts.redirectme.net/database.zip

(I hope!)

 

[Malcy]

Hi
CARE - Download this zip to a different location or rename it since I have made amendments and additions to it!!!
Hope this helps a little. I have built a booking form for you - its not pretty but has the beginnings of functionality and hopefully will help you to learn.
There is some data validation but you will need to build more - hopefully it is clear.
The one bit I did do was to build a check to see if the cottage is booked and if it is then it cannot be double booked. BUT this will only work if your booking days are consistently a Friday since there is nothing to say how long the booking is for - might want to develop this. If it is always going to be a Friday you need to do a bit of data validation to ensure that the date entered is a Friday - I can help again if you want.
The record is added to tblbookings using ADO
A couple of points on your tables - I would only use str to kick off a field name if the field type is text. If it is a date I would use dtm and if a long integer (including autonumber) I would use lng.
I have changed tblbookings to illustrate.
I personally find using lookup in tables confusing. I like to see a number where I expect to see a number. I can always write a query and create a view if I need to know what that number means - but perhaps I am a bit stupid!
Give a shout if struggling to develop.