relationships creating loop-back problem

[Piers]

Hi foks

Urrgghhh. Has anyone come across an issue with relationships where it sort-of knocks itself out of sink. My problem as abbreviated

Bookings tabl
Customer number from Cust tabl
event i

Events tabl
event id (from bookings
trainer nam
locatio

attendees tabl
event id (from events
delegate id (from delegates

delegates tabl
delegate i
delegate nam
customer id (from customer table

If you draw this out it forms a loop-back issue. I'm getting all sorts of join statement issues if I try to change anything. If I remove any one of the links ANYWHERE, everything works. I can't see why it's not working

What needs to be changed, or how should I change the database design

By all means please e-mail me, and I'll send you a picture of the relationships

Thanks, many, many thanks
Piers

 

[Tim Ferguson]

If you draw this out it forms a loop-back issue. I'm getting all sorts
of join statement issues if I try to change anything. If I remove any
one of the links ANYWHERE, everything works. I can't see why it's not
working.

There is an awful lot missing from your description (like the PKs), but
with a bit of guessing I think I see what you are doing. I do not
understand at all what you mean by "join statement issues", but again at a
guess, it may be because you have not made the semantics of the schema
really explicit.

For example, you might want to know "which customers sent delegates to
Location West?", or you might want to know "which customers had bookings
for an event in Location West?". These are two very different queries, and
as such need different joins: the first goes

Cust -< Delegates -< Attendees >- Events

and the second one is

Cust -< Bookings >- Events

It is not hard to set these up in the Queries Grid designer, although
Access might be getting in the way a bit by offering all kinds of helpful
defaults that you don't want.

One way to help this is to remember that _Relationships_ _are_ _Entities_
and should be named: for example a customer Orders a booking, and a booking
BelongsTo an event; while a customer Sends a delegate, who TurnsUp for the
attendance, and the attendance Honours the event. With this in place, the
query becomes a matter of (pretty quaint) English grammar:

- list the Customers who Ordered a Booking that BelongsTo a
particular Event.

- list the Events which were Honoured by an Attendance where there
Turned Up a Delegate who was Sent by Customer X.

and so on.

Hope that helps a bit


Tim F