help with relationships

[Romeo Totti]

i have got a slight problem with my relationships in access. I am trying to set the relationships in my database to help me with double booking.

I am trying to use the same relationships as on http://www.advanced-ict.info/databases/clash.html.

Instead of the patient and doctor tables, I have got a court and member table, the rest is similar.

I have set the relationships between Court and court number and it has been set as one to many, however, the relationship between tbl_members and tbl_booking has been set as one to one (i want one to many) and the other 2 relationships will just allow me to have a simple relationships.


thanks in advance
dan

 

[SydneyGeek]

Access has a default behaviour for its relationships. If you link from a Primary Key field (it'll be bold in the Relationships layout) to a non-key field you will get one(Primary) to many(related field). If you link two primary keys you will end up with a one-to-one relationship. Generally this is a no-no. IF you are trying to set up a system for booking members into courts (tennis?), then you need something like
MEMBER ID (Primary key, Members) to Member ID (Number field, Bookings) and COURT NO (Primary key, Courts) to Court No (Number field, Bookings). In addition, you would have Time Slot and Date fields in Bookings.

Before doing that, though, go to the Bookings table in Design view (with NO relationships to Bookings). Ctrl-Click on the Court, Date and Time Slot fields (in the left margin), and then click the key icon on the toolbar. This creates a compound primary key which: (a) prevents you booking a court without both a date and time, and (b) prevents double-booking, as primary keys must be unique.

Now build the relationships as detailed above and give it a spin.

Denis

 

[Romeo Totti]

right ive sorted two of my relationships, now the other 2 relationships are only allowing it be be undeterminate. And is coming up with an error regarding no unique index found. please help

 

[SydneyGeek]

OK, back track a bit -- sorry, but I just downloaded the example that you put the link to.
In the booking system, these will be the comparative tables:
Your table Their table
Members tbl_patients
Courts tbl_doctors
Bookings tbl_appointments
Availability tbl_availability

Table		Key Field
Members		Member ID (Autonumber)
Courts		Court ID (Number, Long Integer)
Bookings	Time & Date & Court ID (Date/Time,Date/Time, Long Integer)
Availability	Time & Court ID (Date/Time, Long Integer)

In Bookings, Time, Date and Court ID are a compound key
In Availability, Time and Court ID are a compound key.
(See previous post for creating compound keys).
Now set the relationships as per diagram in the example.

Denis