Populating ID in multiple tables

[Dega]

I am trying to get "BookingID" in table tblBookings populated in tblRooms and possibly more tables if I need it.



The reason for this is that one customer may book several trips per year and this Access project worked well for a single booking, but gets very awkward to view and work with multiple bookings. (reports etc)

I have tried changing the relationships previous to this screen shot, but have failed to get the BookingID over to tblRooms.

I have attached two pdf files showing relationships and the interface I am developed so far.

Thanks

[Access_Blaster]

Have you seen this model.

http://www.databaseanswers.org/data_...s_physical.htm
http://www.databaseanswers.org/data_...ons_dezign.htm

[Dega]

Thanks,

I have seen this model and it's a hotel booking system which does not work for tour operators.

[orange]

You have a structure issue.
Your set up relates Room Booking to CustId (tblRooms field CustId)
The Room assignment etc should be related to BookingId and include DateFrom, DateTo, room details(smoking, beds,etc)

As per Access Blaster there is a lot in those models that you could use. You don't need to adopt someone else's model lock/stock and barrel, you van take what you need to meet your requirements.

There is another model, business facts and download option here
http://www.databasedev.co.uk/hotel_r...ata_model.html

Here are some references on Normalization.
http://forums.aspfree.com/attachment...achmentid=4712 excellent overview
http://www.rogersaccesslibrary.com/T...20Nutshell.doc list of steps for design

[Dega]

Thanks for the reply.

I had looked at this model before but I do not understand what the
tblLINK_BookingsRooms and the tblLINK_RoomsFacilities do.

tblLINK in general is what I don't get, and how or why to use a linked table.

Most of this model will work as I will use at least 4 tables (tblCustomers,tblBookings,tblRooms,tblGuests).

[orange]

Thanks for the reply.

I had looked at this model before but I do not understand what the
tblLINK_BookingsRooms and the tblLINK_RoomsFacilities do.

tblLINK in general is what I don't get, and how or why to use a linked table.

Most of this model will work as I will use at least 4 tables (tblCustomers,tblBookings,tblRooms,tblGuests).

The term LINK used by the author is NOT the same as a Linked Table in Access terms. His LINK tables are junction tables. These are tables that serve to remove many to many relationships into 1 to M relationships.

see http://en.wikipedia.org/wiki/Junction_table or
http://www.foresightsoftware.com/Acc...tionTables.htm
for more info

[Dega]

I am having unproductive time attempting to create another improved database.

Linked below is one db I did previously (demo.accdb) which worked fine for reports etc, but I need to be able to expand this database so the same customers can book in the same database several times a year. Data has been removed for privacy but feel free to ad your own.


I had to use this db on a per trip basis which has its limitations as several different databases were needed during the year.

Also attached are the requirements for the db but I am stuck on relationships and linking master/child fields.

Would be grateful for any help and looking at other db models has not worked out ... yet.

Thanks

[Dega]

Working with a Main form (tblCustomers) and two subforms (tblBookings) & (tblTours) throws errors as TourID will not appear in tblBookings.

Thanks

[orange]

Can you attach an mdb copy if your database? I do not have Acc2007 or 2010 so can not open an accdb.

[Dega]

Orange

I have uploaded a zipped file with the .mdb database.

demo.zip

You may have to look at the db objects under Unassigned Objects.

frmCUSTOMERS is what your looking for ...

[orange]

I looked at your database. I tried to use your frmCustomers to add some data... a Customer and a Booking, but I do not see how it's suppose to work.
I'm not sure why you named a field TodaysDate... Is this the BookingDate? TodaysDate has a different meaning every time you view the Form.

I am proposing a different set of relationships that will allow multiple Bookings per Customer. However, with out Business rules/facts and some meaningful test data, this revised structure may not be suitable to you.
see Dega_rel.jpg attached.

I don't know if you have a current operational database or not. If you design a new database to allow multiple bookings per customer, then you will have to move your current data to the new structure, which would be a separate task. To be of any more assistance, I would need some sample data, some business facts.

In the proposed structure you wouldn't need CustId in the tblBookings since it can be derived from tblCustBookings. With an adjusted structure, some of your queries and forms will change.

[orange]

Seems I have double posted my response during an edit.

It also seems that some of the fields in tblBookings related to Tour should be in tblTours (duraction, date..).

[Dega]

Today's date should have been the date the booking was taken ... but I was short on time then started getting busy.

The operational database I have is the same as the copy you have seen, but only good for one destination (trip). It did the required reports (hotel rooming list) pick up points in cities, customs, etc but with limitations.

I have customer data and moving it over (tblCustomers) to a new structure is not a issue as that table will not change, but I do plan to start from a new db that can book customers for multiple trips as many people do go often during the year,

Have looked at the structure you provided and will look at this later on today, and can give you some data and business facts. Thank you for replying and taking the time to look at this and if there is anything you need otherwise ...

I have until Dec 17 to get something working in the way of multiple bookings and I expect that I will be adding more functionality at a later time.

Cheers

[orange]

Great, I'll wait for your facts/data. If you have suggestions/comments etc on the proposed structure as it is, please forward them also.

[Dega]

Orange

Attached is a db used last year with data in it and a overview of what the database does and needs to do.

Thanks