Referential Integrity and Indexes

[racquetballguy]

Hi
I have created a database for my kids to keep track of their swim team races. We use this to keep track of their speeds by stroke, fastest times, averages, etc.

I am trying to add a new module, pulled from a form, created from a query, that will calculate if they have qualified for district championships or not. The module works fine, but I cannot write the calculated results back to the table. I think the problem is with my table relationships.

As set up I cannot write any data from my query results back to the table. I need to change some of my data fields to unique indexes to accomplish what I wish, but I don't see how to do that with my current structure.

I have four (4) tables:

tblSwimmer
SwimmerID (key)
GirlorBoy
etc

tblSwimTeam
SwimTeamID (key)
SwimmerID
AgeSwimmingAs
etc

tblIndividualRaces
IndivRacesID (key)
SwimTeamID
Stroke
etc

tblQualifyingTimes
QTID (key)
Sex
Age
Stroke
etc

As each swimmer can be on more than one swim team (different seasons) I have a one to many relation between tblSwimmer.SwimmerID and tblSwimTeam.SwimmerID, and as each swimmer will have many races for the team I have a one to many relation between tblSwimTeam.SwimTeamID and tblIndivRaces.SwimTeamID.

When I create one to one relationships between tblQualifyingTimes:sex and tblSwimmer:GirlorBoy, tbl QualifyingTimes:Age and tblSwimTeam:AgeSwimmingAs, and tblQualifyingTimes:Stroke and tblIndivRaces:Stroke I get the correct results from my query but am unable to write anything back to my table. My problem is that Access wants the columns from the parent tables to be unique indexes, but I don't see how this is possible.

I have also tried writing a subquery to match up the correct QTID with IndivRacesID, but run into the same problem as above when I try to write this to my form. I have also tried putting QTID in tblIndividualRaces but as it is null in that table initially I would have to write to that column.

Is there a better way to set up my relationships? Does anyone see a different workaround?

Thank You
Ivan

[MMcCarthy]

What is your key in tblQualifyingTimes tying it to one of your other table. (eg Foreign Key of SwimmerID)

Hi
I have created a database for my kids to keep track of their swim team races. We use this to keep track of their speeds by stroke, fastest times, averages, etc.

I am trying to add a new module, pulled from a form, created from a query, that will calculate if they have qualified for district championships or not. The module works fine, but I cannot write the calculated results back to the table. I think the problem is with my table relationships.

As set up I cannot write any data from my query results back to the table. I need to change some of my data fields to unique indexes to accomplish what I wish, but I don't see how to do that with my current structure.

I have four (4) tables:

tblSwimmer
SwimmerID (key)
GirlorBoy
etc

tblSwimTeam
SwimTeamID (key)
SwimmerID
AgeSwimmingAs
etc

tblIndividualRaces
IndivRacesID (key)
SwimTeamID
Stroke
etc

tblQualifyingTimes
QTID (key)
Sex
Age
Stroke
etc

As each swimmer can be on more than one swim team (different seasons) I have a one to many relation between tblSwimmer.SwimmerID and tblSwimTeam.SwimmerID, and as each swimmer will have many races for the team I have a one to many relation between tblSwimTeam.SwimTeamID and tblIndivRaces.SwimTeamID.

When I create one to one relationships between tblQualifyingTimes:sex and tblSwimmer:GirlorBoy, tbl QualifyingTimes:Age and tblSwimTeam:AgeSwimmingAs, and tblQualifyingTimes:Stroke and tblIndivRaces:Stroke I get the correct results from my query but am unable to write anything back to my table. My problem is that Access wants the columns from the parent tables to be unique indexes, but I don't see how this is possible.

I have also tried writing a subquery to match up the correct QTID with IndivRacesID, but run into the same problem as above when I try to write this to my form. I have also tried putting QTID in tblIndividualRaces but as it is null in that table initially I would have to write to that column.

Is there a better way to set up my relationships? Does anyone see a different workaround?

Thank You
Ivan

[racquetballguy]

Hi - Thanks for the reply

I had set up a one to many relationship between QTID in tblQualifyingTimes and the foriegn key of QTID in tblIndividualRaces. But as the qualifying info is all new, the QTID in tblIndividualRaces is currently null, and each new entry would be null as the children are entering their race information. I can pick out the correct QTID and times using a query where I have deleted the relationship between QTID in tblQualifyingTimes and the foriegn key of QTID in tblIndividualRaces but I need a way of witing the QTID in tblIndividualRaces. Or a way of just taking the times associated with the QTID and using them in my module.

Thanks
Ivan

[MMcCarthy]

If a Qualifying Time relates to tblIndividualRaces then does it not also relate to tblSwimTeam.

Hi - Thanks for the reply

I had set up a one to many relationship between QTID in tblQualifyingTimes and the foriegn key of QTID in tblIndividualRaces. But as the qualifying info is all new, the QTID in tblIndividualRaces is currently null, and each new entry would be null as the children are entering their race information. I can pick out the correct QTID and times using a query where I have deleted the relationship between QTID in tblQualifyingTimes and the foriegn key of QTID in tblIndividualRaces but I need a way of witing the QTID in tblIndividualRaces. Or a way of just taking the times associated with the QTID and using them in my module.

Thanks
Ivan

[MMcCarthy]

To clarify, is this logic correct

A swimmer is on a swim team
A team is in an individual race
A team has a qualifying time in that individual race

Hi - Thanks for the reply

I had set up a one to many relationship between QTID in tblQualifyingTimes and the foriegn key of QTID in tblIndividualRaces. But as the qualifying info is all new, the QTID in tblIndividualRaces is currently null, and each new entry would be null as the children are entering their race information. I can pick out the correct QTID and times using a query where I have deleted the relationship between QTID in tblQualifyingTimes and the foriegn key of QTID in tblIndividualRaces but I need a way of witing the QTID in tblIndividualRaces. Or a way of just taking the times associated with the QTID and using them in my module.

Thanks
Ivan

[racquetballguy]

To clarify:
1. Each swimmer can be on more than one swim team (different seasons).
2. For each swim team, each swimmer will swim many individual races.
3. During a season, each race may be swum multiple times.
4. There is only one qualifying time associated with each individual race.
5. The same qualifying time may be used with each new race.
6. The selection of which qualifying time to use is dependent on a). Is the swimmer a boy or a girl b). The age of the swimmer and c). the stroke for that race.
7. The way the program is currently set up, the swimmer enters if they are a boy or girl only one, in tblSwimmer.
8.Each new season, the swimmer enters the age they are swimming as only once, in tblSwimTeam.
9. After their individual races the swimmer enters the race data including stroke, in tblIndividualRaces.
10. I then want Access to select the correct qualifying time from tblQualifyingTime to send to my module.
11. The module calculates if they qualified for regional championships or not and also lets them know the time difference between their race and the qualifying times.

I have created two different queries, one bases on joins and the other using the "In" commandto get the correct qualifying times or QTID. Both of these work only if there is no table relationship between tblQualifyingTimes and the other tables.

What I want to do is use either QTID or the times associated with it in my module to calculate and update records in tblIndividualRaces.

Thank You
Ivan

[MMcCarthy]

Firstly, because each swimmer can be on more that one team and each team has more than on swimmer the relationship between tblSwimmer and tblSwimTeam is many to many. You will need to create a join table with a double primary key made up of SwimmerID and SwimTeamID.

The tblQualifyingTimes needs to have a relationship both to the Swimmer and the Race. That is a Foreign key to tblSwimmer (SwimmerID) and a Foreign Key to tblIndividualRaces (IndivRacesID).

Although a team swims a race the qualifying time is the swimmer therefore the foreign key in tblIndividualRaces shouldn't be a foreign key to the SwimTeam in this table. I would suggest setting up a transaction table that would replace the table tblQualifyingTimes. As long as it holds the foreign keys described above it would allow you to join all records.

To clarify:
1. Each swimmer can be on more than one swim team (different seasons).
2. For each swim team, each swimmer will swim many individual races.
3. During a season, each race may be swum multiple times.
4. There is only one qualifying time associated with each individual race.
5. The same qualifying time may be used with each new race.
6. The selection of which qualifying time to use is dependent on a). Is the swimmer a boy or a girl b). The age of the swimmer and c). the stroke for that race.
7. The way the program is currently set up, the swimmer enters if they are a boy or girl only one, in tblSwimmer.
8.Each new season, the swimmer enters the age they are swimming as only once, in tblSwimTeam.
9. After their individual races the swimmer enters the race data including stroke, in tblIndividualRaces.
10. I then want Access to select the correct qualifying time from tblQualifyingTime to send to my module.
11. The module calculates if they qualified for regional championships or not and also lets them know the time difference between their race and the qualifying times.

I have created two different queries, one bases on joins and the other using the "In" commandto get the correct qualifying times or QTID. Both of these work only if there is no table relationship between tblQualifyingTimes and the other tables.

What I want to do is use either QTID or the times associated with it in my module to calculate and update records in tblIndividualRaces.

Thank You
Ivan

[racquetballguy]

Thank You for your reply

This was first written for only my daughter so the one to many between swimmers and swim team made sense. But now that we have several kids on the team using it you're right, I need a many to many relationship.

Regarding the qualifying times, I have tried the transition table previously and again just now and am able to maintain referential integrity that way. But the problem is that since the data in tblQualifyingTimes is not generated by the user, it is a lookup table only, Access must determine which qualifying time or QTID to use for each individual race - IndividualRacesID. Until Access does this calculation the QTID record associated with the IndividualRacesID in the transition table is not populated.

The only ways I have come up with so far to calculate QTID are using queries where there is no table relaionship between tblQualifyingTimes and the other tables. I can get the correct QTID but cannot get Access to populate the null QTID associated with the IndividualRacesID.

I have assumed it was because of the lack of referential integrity in my queries, but I could be wrong. I am trying to get this to work as easily and seamlessly as possible since these are kids entering the data.

Again, any help is appreciated
Ivan

[MMcCarthy]

If you have a triple Primary Key on the Transaction table comprising QTID, SwimmerID, IndivRaceID then Access will provide the referential integrity when the Qualifying time is updated. On the add new record to this table you will have to provide a swimmer and race or you cannot add the record.

Thank You for your reply

This was first written for only my daughter so the one to many between swimmers and swim team made sense. But now that we have several kids on the team using it you're right, I need a many to many relationship.

Regarding the qualifying times, I have tried the transition table previously and again just now and am able to maintain referential integrity that way. But the problem is that since the data in tblQualifyingTimes is not generated by the user, it is a lookup table only, Access must determine which qualifying time or QTID to use for each individual race - IndividualRacesID. Until Access does this calculation the QTID record associated with the IndividualRacesID in the transition table is not populated.

The only ways I have come up with so far to calculate QTID are using queries where there is no table relaionship between tblQualifyingTimes and the other tables. I can get the correct QTID but cannot get Access to populate the null QTID associated with the IndividualRacesID.

I have assumed it was because of the lack of referential integrity in my queries, but I could be wrong. I am trying to get this to work as easily and seamlessly as possible since these are kids entering the data.

Again, any help is appreciated
Ivan

[racquetballguy]

I guess I got hung up on trying to have the query itself have some sort of referential integrity, but what you suggest is much better. Thank you for your help.
Ivan

[MMcCarthy]

The thing to remember is your tables all have to have referential integrity you can't impose it a query level.

I guess I got hung up on trying to have the query itself have some sort of referential integrity, but what you suggest is much better. Thank you for your help.
Ivan