Dyslexic Table Relations

[Dave Titan]

I'm building a new database for an organisation. It will have many facets.

I'm new to making tables, I've always just one or more unrelated tables and used queries to get the results I want.

I'm also dyslexic and while the whole Idea of relationships makes sense, I can't put it into practice - Here's the prob-

I've a list off 50 Clubs, each with contact details of one person.

I've 12 Sports, Some clubs do only one some do all 12.

Each sport has a co-ordinator. Ususally a co-cordinator is the contact of the club.

What I'd like to do is have table "clubs" have all the cotact details. And have table "Co-cordinators" just have the name and club listed.

Then create a form that shows only the Sports co-ordinators, but also all their relevant details from table"clubs" ie address telephone.

I can do this with a query. But just wonder if creating relationshps between tables would make life easier????

I hope this makes sense

Thanks

 

[The_Doc_Man]

The way I would do this is to build at least three tables.

Table 1: Clubs
Club Name
Some sort of code number that is unique per-club.
Address, phone, other info specific to the club but not to any one sport.

Table 2: Sports
Sport name
Some sort of code number that is unique per-sport.
Other info specific to the sport but not to any one club.

Table 3: ClubSports
Club code number
Sport code number
Any other info that depends on both the club and the sport.
(Entry is made if and only if the club participates in that sport.)

Now link the clubsports table to the clubs table through the clubcode field, many (clubsports) to one (club.) Link the clubsports table to the sports table, many (clubsports) to one (sport). The business about coordinators is the wildcard in this layout. If a club has more than one coordinator for a given sport, you can put the coordinator's name in the clubsports table. If a club has only one sports coordinator no matter what, that name could go into the club table.

Now, if you were REALLY being picky and had personnel rosters for the clubs, you would have a person table. That table would show the person's name and an ID number.

If the person can only belong to one club, then the clubcode can be used as the link. In that case, the clubsports table can use the person's ID rather than the name, and you would establish yet another relation, this time between Persons and ClubSports, one (Person) to many (ClubSports).

Does that help?

 

[Dave Titan]

Thanks a lot Doc,

First thing tomorrow morning(it's Ireland here) I'll give that a go, and I'll let you know how I got on as soon as I know myself

Thanks again for the time

 

[Dave Titan]

I've finally managed to get a some tabels together in a relationship!!!! Took me long enough though.

It's not quite what I expected though. the "+" Symbol drills down to show you the address on the related table.

So in table 1 I have filled in contacts and address in table 2 I have the sports with fields containing no contacts and addresses. Both tables have a common code

What I'd like to happen is Table 1 is related to table 2 and all those contacts and address in table 1 will appear in the blank records or the same named field???????

Secondly, and I'd say the above will answer this this question to. I have a Sport form and Affiliate form. I'd like due to the related tables to see the filled in addresses on the sprt form, but they remain blank, somewhat defeating the purpose of the relationship??

Can you help with this one?????