table relating to itself?

[cuttsy]

I have a table of guests that holds all of their details such as name; address; medical info; family background; etc. What i need to do is to hold on the database if they would like to share a room and if so, who with.

I thought it would be possible to lookup the names of guests in the same table but access tells me otherwise.

I thought about creating a linked table of my guests table but when I link person A to person B then person B is not linked to person A which I would like to be the case.

I thought about creating a table called tblShare to hold 2 names from tblGuest but im not to sure how I would make this work. I think I may have the same situation as above. If I go to person A and link them to person B would this show up on Person B? I'm not too sure.

I will try this out now. If anyone has any other suggestions or some guidence on this I would be very gratefull.

 

[Mile-O]

I have a table of guests that holds all of their details such as name; address; medical info; family background;

You managed to store medical info in the same table as guests despite these being two separate entities? Your database design is not normalised. You need a further two tables to simulate the many-to-many relationship here. One for medical info, and one for linking guests to medical info.

 

[cuttsy]

If I may be more specific.

My table holds 3 yes/no fields disability, mobility, learning difficulty and then a text field for ellaboration.

I understand you telling me that this needs to be in a seperate table. Is this a many-to-many?

I thought it was one-to-many.

 

[cuttsy]

I have decided how to go about my original problem.

Thank you for your time.

 

[Mile-O]

My table holds 3 yes/no fields disability, mobility, learning difficulty and then a text field for ellaboration.

It's a repeating group based on three fields that do not depend on the guest key thus they are a prime indication that a new table is needed.

Is this a many-to-many?

Yes, as one guest may have many medical problems and one medical problem may apply to many guests.

I thought it was one-to-many.

That would still require a second table.