Relationships Headache (1 Viewer)

[Gimlett]

Hello,

I've built some satisfactory forms for data entry but I can't determine the right way to go about the relationships. As I entered runners into an event using a subform based on runners and results and a main form based on events, I found I could duplicate runners per event which renders it useless.

Fundamentally I need to enforce referential integrity (or at least ensure) to;

1. Enter only one runner per event
2. Many events at many venues but only one event per date
3. How to connect results with runner and events or ensure this can't be mixed up. Are results connected with event or member or how do I describe to both if necessary?

Sorry if I'm asking too much, I did some searching but couldn't find a suitable example.

Many Thanks

Chris

 

[Ranman256]

you need a table to store the roster of the event and what place they came in,
keyed (*) so there’s no dupes

tEventRoster:

*EventRef
*RunnerRef
Place

 

[Gimlett]

you need a table to store the roster of the event and what place they came in,
keyed (*) so there’s no dupes

tEventRoster:

*EventRef
*RunnerRef
Place

Thanks Ranman, is that like a many to many join table between events and runners ?

 

[Pat Hartman]

tblEvents needs a unique index in addition to the autonumber PK. Open the table in design view and then open the indexes dialog. On the first empty line, give the index a unique name and choose the first field - RunnerRef. Choose Unique as the index type. Then go to the next row, leave the index name blank and choose the second field - EventRef. Save it.

Also add the relationship between EventRef and tblEvents

 

[plog]

1. What is in tblEvents.Races? How is a race different than an Event?

2. I wouldn't store each part of the time in separate fields (H, M, S). Either use a Date/Time or store everything as seconds.

3. Position probably shouldn't be stored. I bet its not independent of event, AgeCat and H/M/S--it can be calculated therefore it should be in a query and not be stored in a table.

4. What happens when a runner moves to a new AgeCat? New entry in tblRunners? It would be better to store DOB in Runners but if you don't have that I understand, but my question about moving AgeCat still holds.

 

[Pat Hartman]

Also, the relationship between Venues and Events is on the wrong fields

 

[LarryE]

Hello,

I've built some satisfactory forms for data entry but I can't determine the right way to go about the relationships. As I entered runners into an event using a subform based on runners and results and a main form based on events, I found I could duplicate runners per event which renders it useless.

Fundamentally I need to enforce referential integrity (or at least ensure) to;

1. Enter only one runner per event
2. Many events at many venues but only one event per date
3. How to connect results with runner and events or ensure this can't be mixed up. Are results connected with event or member or how do I describe to both if necessary?

Sorry if I'm asking too much, I did some searching but couldn't find a suitable example.

Many Thanks

Chris View attachment 105364 View attachment 105365

Using some logic and reason, it seems you have:

1. Clubs that may have multiple members (Runners)
2. Each Runner may participate in multiple races at each Venue
3. Each Venue sponsors various Events or Races (Each Event has an EventName, an EventAgeCategory, an EventGenderCategory etc.)
4. Each Runner in each Club at each Event has a Result(Place, Time)

So:

1. Clubs are connected to Runners
2. Runners are connected to Venues
3. Venues are connected to Events (or Races if you prefer)
4. Events (or races) are connected to Results

 

[Pat Hartman]

The schema the OP posted was correct except for the errors already mentioned.

 

[arnelgp]

Sorry if I'm asking too much, I did some searching but couldn't find a suitable example.

if you will search here "athletics", you will find a sample db.

 

[Gimlett]

Hi All,

Thanks for all this, I need to go away and assimilate this info and put these suggestions into practice. Sorry I didn't reply, I am having a spot of trouble when logging in, I get the message something went wrong then reset my password and still doesn't work. Then magically this morning it's working so not sure what's happening but it is a known thing or just me?

Many Thanks

 

[Gimlett]

Pat, I have tried your index suggestion and rearranged the relationships as your advice, I think this is more satisfactory but I can still add the same runner again at the same event probably because I have misunderstood or haven't applied the changes correctly. Please have a quick gander at the enclosed shots. Thank you.

Larry:

1. Correct
2. Races has gone although there are multiple races per event this is due to various categories, this is cross country so there is only one race per athlete unlike athletics meeting where you may have one runner does 100, 400 1500 etc. Although I tried to accommodate these age groups and seperate genders as well, It was getting too complicated for me but I'm not sure if this will come back to bite me when I try to include them later.
3. Yes exactly as above
4. Correct

hopefully the new relationship layout reflects your suggestions.

I don't want to introduce the time issue at the moment. I defy anyone to teach me how to work with Access time date format without splitting times into seperate fields and using total seconds for Pbs / average etc. I don't believe it's possible but what do I know? Maybe another day.

 

[Pat Hartman]

You don't have a table that defines an event anymore. You changed it. The Event table cannot contain a runnerref. That would limit the event to a single runner. Remove runnerref from the event table. Put the unique index on the two fields in the junction table which is tblResults. And you probably want to add a unique index to the tblEvents on the name + date.

ALWAYS use autonumbers as your PK even when you have a natural key available. It will simplify your life. Then add a unique index on the natural key which in this case would be the name + date.

Also, if you have an autonumber in a table, it should ALWAYS be defined as the PK otherwise it has no reason for being in the table.

 

[Gimlett]

Thanks Pat,

I wanted to use the natural key in table events as the primary key but guess that's not possible so I use Autonumber as Primary key as you say and works fine. I created a unique index as you suggest on the natural key name and date. I'not sure if I have understood completely correctly, please could I trouble you just to check this snippet and correct as you see fit then I think that's me done for the moment and I thank you kindly and wish you a happy Christmas and to all on the forum

 

[Pat Hartman]

You need to create a compound key as I described. Each field by itself is not unique. It is the combination of the two fields that is unique. Just remove the name "NaturalK2" to make both fields part of the same index. The first name in the compound index has the name for the index, the subsequent rows have blank index names indicating they belong to the previous index. You can include a maximum of 10 fields in any index or PK (which is a special unique index)

Also, remove the index on VenueRef. When you create a relationship between two tables, Access automatically creates a HIDDEN index on the FK. That makes this index a duplicate. You really do not want to have two indexes on the same field.

 

[Gimlett]

All sorted now, Thank you for the help.

 

[Pat Hartman]

You're welcome
Merry Christmas, Happy Hanukah

 

[GregDataReno]

First principle --- avoid the use of autonumbering keys where possible - if a natural key exists, use it to save on space and indexing.

Now, since many runners compete in many events, that's a "many to many" relationship - this is perhaps a 'Scoreboard" table. We need to discover what makes the runner in an event unique. Clearly, the runner and the event in which the runner competed is unique - so we need an associative table, a joining table, to hold each instance of the runner and their event.

The runner and the event in this associative table can be uniquely identified by inheriting the primary keys from the runner table and the event table primary key. This new primary key is perfect - there is no need to confuse things by adding a (redundant) autonumbering primary key to this associative table.

Once you create this associative table and then create the relationships and then use these tables in a form, Access will honour these relationships by automatically carrying into that associative table the primary keys from the runner table and the event table; this automatic assignment of primary key values is a feature of Access often overlooked since it's not well documented, yet it's a crucial behaviour that needs to be understood by Access developers so that best-practice table design is honoured in forms.

Another principle that helps in this problem is to understand that an entity relationship model is a static representation at a point in time. This helps to recognise 'entities' - in this case we have a "runner who is or will be competing in event" fact with its own specific attributes that describe what happened to a runner in an event. This associative table is also classed as a 'weak' entity in that each instance of "runner who is or will be competing in event" relies entirely on the existence of facts in two other tables - the 'runner' and event' entites - for it to exist - no runner or no event and there's no instance of runner competing in an event

Good luck
Greg

Hello,

I've built some satisfactory forms for data entry but I can't determine the right way to go about the relationships. As I entered runners into an event using a subform based on runners and results and a main form based on events, I found I could duplicate runners per event which renders it useless.

Fundamentally I need to enforce referential integrity (or at least ensure) to;

1. Enter only one runner per event
2. Many events at many venues but only one event per date
3. How to connect results with runner and events or ensure this can't be mixed up. Are results connected with event or member or how do I describe to both if necessary?

Sorry if I'm asking too much, I did some searching but couldn't find a suitable example.

Many Thanks

Chris View attachment 105364 View attachment 105365

 

[mike60smart]

Hey

Why oh why give such wrong advice.
Every table should have an Autonumber PK
Who told you to avoid using Autonumbers?

 

[Gasman]

First principle --- avoid the use of autonumbering keys where possible - if a natural key exists, use it to save on space and indexing.

Yes, because space is still pretty tight these days?

FWIW I have always used surrogate keys.
Those 'natural' keys have a habit of changing further down the road.

 

[Pat Hartman]

First principle --- avoid the use of autonumbering keys where possible - if a natural key exists, use it to save on space and indexing.

I'm guessing that you don't use Access FE's much. A single field natural key won't be a problem but a multi-field natural key will prevent you from selecting rows using a combo or listbox which is why I always recommend autonumbers for PKs with Access. The combo and listbox require a SINGLE unique identifier for selection purposes. They are not capable of creating a multi-field FK.