Help please

[the only man]

i need help with doing relationship in Access. i have created the table but, the tables consists of compound, composit and candidate keys and i am getting stuck how to do the realationship. i have the EER diagram of how it should join with the attributes.

you can add me on msn zahir56@hotmail.com

 

[ScottGem]

I do not recommend compund or composite keys for this very reason. I recommend that you use an autonumber as your Primary key for each table, then use that as your foreign key in the related table.

 

[the only man]

i have pm you please add me

 

[RV]

Don't pm, simply follow up on Scott's advise.

RV

 

[ScottGem]

As RV said, just follow the advice I gave you.

 

[the only man]

i build the database but cozing problems please help any one. i can email database file to have a look at.

 

[ScottGem]

Detail the problems and we'll try to help.

 

[the only man]

errrmmmm.........................

i got the relationship diagram and when i implemented the tbales with relatioship i doesnt do what it supposed to do.

 

[the only man]

relationships

relationship


Customer(customerid, name, address, age, tel#, gender)
Booking(book#, customerid, start, end, party, cost)
SelfCatering(accomm id)
Hotel(accomm id, board)
AccommBook(book#, accomm id, start, end, cost)
Season(title, start, end)
Available(title, accomm id, price)
Flight(flight#, date, cost, time, source, destination)
Route(source, destination, time)
FlightBook(book#, flight#, date, number)
Group(group#, cost, ac)
car(make, model, group#)
CarBook(book#, group#, start, end, ac)

 

[the only man]

i got tblAccommodation, wit Accomm_id(AutoNumber), Description(Text), Self Catering(Yes/No), Hotel(Yes/No) attributes.

Also i got 2 tables for tblSelf Catering wit Accomm_id(Number) and tblHotel Accomm_id(Number), Board(Lookup Wizard[Bed n Breakfast, Half, full board]).

so diagram looks like this;
tblAccommodation

tblSelfCatering tblHotel

sort of hierachy...

so my Question is: i have input the data in tblAccommdation i.e.
Record 1 = 1, Hotel Plaza, N, Y (N and Y is Tick Box so N is left empty and Y means ticked)
Record 2 = 2, Cafe, Y, N

so once i have ticked i want that ID to appear in my table for i.e. Record 1 is Hotel so it shud save Accomm_id 1 into the tblHotel.

how do i do that??

Also the tick boxes, allows to select either 1 or both in tblAccommadation, it shud only allow one tick for each record, either hotel or self catering,

plz help me out

 

[ScottGem]

Setting up relations does NOT automatically populate foreign keys. That's not the way relations work. A record should be added to a child table only when there is data to go in that record. Generally, in a situation like yours, you use main/sub forms to enter data into parent and child records at the same time. When you use a subform, the linked (FK) field is automatically filled in when you add any other data to the record.

If you are not using subforms then FKs are filled in using a combo or list box to select the parent record.