Relating a Record to another record int eh same table

[czarjosh]

I am seeting up a database for people registering for a conference. I want to be able to assign roommates on the form from a drop down menu, but I want the data to be people in the same table. WHat is the appropriate way to relate two records together in the same table.

Josh

 

[willir]

Josh

A typical approach to a "self join" is the boss and employee.

EmployeeTbl
EmployeeID - primary key
EmployeeName
SuerpvisorID - foreign key, but points to the EmployeeID

A person is their own boss would use their own employee ID as their supervisor ID.

But I do not think this is what you are looking for. Can we explore some design issues for a moment??

ContactTbl
ContactID - primary key
ContactName
etc

BuildingTbl
BuildingID - primary key

RoomTbl
RoomID - primary key
BuildingID - foreign key to BuildingTbl

BedTbl
BedID - primay key
RoomID - foreign key to RoomTbl

But how do you confirm a registration? There are various solutions. Here is one to consider... Create a registration table.

RegistrationTbl
RegID - primary key
ContactID - foreign key to contact table
PartnerID - foreign key to contact table
BedID - foreign key to BedTbl
RoomID - foreign key to RoomTbl

With this approach...
- contact reserves a bed
- contact + partner reserves a room

The relational savy guys will note that this design kind of breaks the rules of normalization. On the registration table, the RoomID can be used to retrieve the RoomID. However, this is the only way I can see to easily to have a group book a room.

A limitation to this design is if a group of people want to book a dormatory situation. In this case, you would have create a separate table, say PartnerTbl. Same idea, but a little more complicated.

Another limitation is what do you do if a group makes the reigstration? In this case, I suggest you treat each member within the group as a registrant.

A couple of things on the registration table.
- If you make the index for ContactID unique, no duplicates, you will prevent a person from being registered twice
- If you make the index for the RoomID unique, no nulls, you will prevent a bed from being booked twice. But this may cause problems if you have a registrant that you don't want to assign to a room until you research the situation further - special needs (handicap access, room mates, etc). If you make the RoomID unique, no nulls, then you have to book the bed.

Some logic would have to be developed to handle all this, but I think the design would work.

This design does not handle payment, course schedules (if structured), but it does address your specific question.

Richard
-

 

[scriverb]

Because you are only creating a 1 to 1 relationship here to the same table just create a new field in your table(i.e. Registration). Call it Roommate and make it a number(LongInteger). Now on your form create a combobox named cboRegistrants with the following SQL as the RowSource:

Select A.Name, A.RecNumber FROM Registration as A WHERE A.RecNumber <> FORMS![[i]yourformname[/i]]![RecNumber] Order by A.Name;

This requires the Registration table to have a field called RecNumber and be an AutoNumber field.

Now set the combobox properties as follows: columns 2, Bound Column 1; column widths 0;2

In the AfterUpdate Event Procedure put the following VBA code:

Dim db as DAO.database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("Registration", dbOpenDynaset)
rs.FindFirst "[RecNumber] = " & Me![cboRegistrants]
If NOT rs.NoMatch then
   rs.edit
   rs("Roommate") = Me![cboRegistrants]
   rs.Update
else
   MsgBox "No Match Found -- However Impossible!!!"
end if
rs.close
db.close

You now have both records in the database updated with each others RecNumber value. Whenever you are printing receipts or reports you must put the Registration table in the query twice and link the first instance of the table from the RecNumber field to the Roommate field with a LEFT join. This will make both records available for display at the same time. Here is an example of a query to list roommates:

Select A.Name as Registrant_1, IIF(NOT IsNull(B.Name), B.Name, "No Roommate Assigned") as Registrant_2 FROM Registration as A LEFT JOIN Registration_1 as B ON A.RecNumber = B.Roommate 
ORDER BY A.Name, B.Name;

Post back if you have any questions.




Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]