If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Seeking some expert advice.
Hello group! Let me start by saying 'I am a novice'. I have done some
things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
#2
|
|||
|
|||
You can contact me by removing this is not real from my reply email address.
-- Ken Snell MS ACCESS MVP "HD87glide" wrote in message ... Hello group! Let me start by saying 'I am a novice'. I have done some things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
#3
|
|||
|
|||
It may take a bit longer than sending the database, but who don't you
describe your database structure here so that all may benefit from the discussion? Besides, there are people who do consulting work, but it is a lot to start off asking somebody to do that for free. Sometimes people will offer to take a look after a while, but it is not really the place to start. You don't need every single bit of information. For instance, the Members table (tblMembers) may be described something like: tblMembers MemberID (autonumber PK, or primary key) FirstName etc. You don't need to show fields for Address, Phone, and so on. They don't alter the basic functioning of the database. You will need to provide some general information, such as whether a member can be in more than one league or on more than one team. Assuming a member would belong to only one league and to only one team, you may just need a team table (tblTeam): tblTeam TeamID (autonumber PK) MemberID (FK, or foreign key) League (enter the name of the league) tblScores ScoreID (autonumber PK) MemberID (FK) Week Score If scores are by team maybe the FK would be TeamID instead of MemberID. If you need both (team scores and individual statistics) that's another situation. Some of this stuff depends on exactly what you need to do. One thing you probably do not want to do is to build a table that contains a field for Week 1 score, Week 2 score, etc. By linking tblScores to tblTeam (or tblMember) you can establish a week-by-week listing without redundancy. If you want to track scores over the course of several seasons that is yet another wrinkle. None of these things are problems, but the answers will have a lot to do with how you design your database. "HD87glide" wrote: Hello group! Let me start by saying 'I am a novice'. I have done some things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
#4
|
|||
|
|||
Thank you for your responses! Below is the description of the DB.
ClubTable: ClubID (Primary Key-PK) Autonumber ClubInfo (address, phone, etc) ClubLeagueID (Foreign Key-FK) to LeagueTable LeaguesTable: LeagueID –PK Autonumber LeagueDesc (2005 Winter, 2005 Summer, 2005 Fall) LeaguesDetail: WeekID – PK Autonumber LeagueID – FK (links to Leagues table) LeagueWeek (1,2,3…14) LeagueDate (actual date of shooting) MembershipTable: MemberID – PK Autonumber MemberInfo (last, first, address, phone, email…) ClassID – FK (links to Calss table) TeamID – FK (links to Team table) LeagueID – FK (links to League table) TeamsTable: TeamID – PK Autonumber LeagueID – FK (links to LeagueID) ScoresTable: ScoresID – PK Autonumber MemberID – FK (links to membership table) LeagueID – FK (links to league table) ScoresDetail: ScoresDetailID – PK Autonumber ScoresID – FK (links to scores table) LeagueID – FK (links to league table) TeamID – FK (links to team table) MemberID – FK (links to membership table) WeekID Slow \ Timed These are the three scores that we need to track for the Rapid / 14 weeks. From these scores we will calculate 5 other Fields from these each week. Now, in theory, the process will be to input the league info, description and the 14 shooting dates. Then go to the club info screen and this is to be used as a control set to select which league is the current league. Now define the teams, and assign them to the current league. Now that we have the league, the dates, the teams, we enter the shooters and assign them to the league and to a team. That will be all the background work. If a shooter only shoots one league per year, then that shooter will be unassigned from all leagues and all teams but their data will stay in the DB. The same goes for a team, if that team does not exist for a league, then they will be unassigned. The progress / problems that I am having: 1) I have defined a main data entry form that will show the current league based off of the control record in the club info. 2) I have added sub-forms that then show the current teams for that league 3) I then added sub-forms to display the current shooters for that team, for that league. 4) The above three items are working fine. I have a problem in that I can not make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form. 5) In the original design I did not break out the scores, I had one record that had multiple fields slow1, slow2…slow14, timed1, timed2…timed14. I was able to make that work for me until I had to create reports that needed to add up all the fields and provide the 5 calculations for each record times the number of shooters…this just made Access stop responding and hang. Thus I followed a more proper DB design about breaking things apart and trying to let it run more efficiently. Anyone willing to add input is more than welcome! Thank you in advance! Jeff "BruceM" wrote: It may take a bit longer than sending the database, but who don't you describe your database structure here so that all may benefit from the discussion? Besides, there are people who do consulting work, but it is a lot to start off asking somebody to do that for free. Sometimes people will offer to take a look after a while, but it is not really the place to start. You don't need every single bit of information. For instance, the Members table (tblMembers) may be described something like: tblMembers MemberID (autonumber PK, or primary key) FirstName etc. You don't need to show fields for Address, Phone, and so on. They don't alter the basic functioning of the database. You will need to provide some general information, such as whether a member can be in more than one league or on more than one team. Assuming a member would belong to only one league and to only one team, you may just need a team table (tblTeam): tblTeam TeamID (autonumber PK) MemberID (FK, or foreign key) League (enter the name of the league) tblScores ScoreID (autonumber PK) MemberID (FK) Week Score If scores are by team maybe the FK would be TeamID instead of MemberID. If you need both (team scores and individual statistics) that's another situation. Some of this stuff depends on exactly what you need to do. One thing you probably do not want to do is to build a table that contains a field for Week 1 score, Week 2 score, etc. By linking tblScores to tblTeam (or tblMember) you can establish a week-by-week listing without redundancy. If you want to track scores over the course of several seasons that is yet another wrinkle. None of these things are problems, but the answers will have a lot to do with how you design your database. "HD87glide" wrote: Hello group! Let me start by saying 'I am a novice'. I have done some things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
#6
|
|||
|
|||
You were correct to break out scores into separate records in a separate
table. You might even wish to break out the type of score (slow, timed, rapid) to its own table and then have the actual scores be a child of that table. I am not following what you mean by "can't make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form"? What is the structure of that subform? What is its recordsource? (I admit that I'm a bit confused because you talk about multiple subforms, but it's not clear if they're all subforms of one main form, or are subforms of each other (stacked/nested). -- Ken Snell MS ACCESS MVP "HD87glide" wrote in message ... Thank you for your responses! Below is the description of the DB. ClubTable: ClubID (Primary Key-PK) Autonumber ClubInfo (address, phone, etc) ClubLeagueID (Foreign Key-FK) to LeagueTable LeaguesTable: LeagueID -PK Autonumber LeagueDesc (2005 Winter, 2005 Summer, 2005 Fall) LeaguesDetail: WeekID - PK Autonumber LeagueID - FK (links to Leagues table) LeagueWeek (1,2,3.14) LeagueDate (actual date of shooting) MembershipTable: MemberID - PK Autonumber MemberInfo (last, first, address, phone, email.) ClassID - FK (links to Calss table) TeamID - FK (links to Team table) LeagueID - FK (links to League table) TeamsTable: TeamID - PK Autonumber LeagueID - FK (links to LeagueID) ScoresTable: ScoresID - PK Autonumber MemberID - FK (links to membership table) LeagueID - FK (links to league table) ScoresDetail: ScoresDetailID - PK Autonumber ScoresID - FK (links to scores table) LeagueID - FK (links to league table) TeamID - FK (links to team table) MemberID - FK (links to membership table) WeekID Slow \ Timed These are the three scores that we need to track for the Rapid / 14 weeks. From these scores we will calculate 5 other Fields from these each week. Now, in theory, the process will be to input the league info, description and the 14 shooting dates. Then go to the club info screen and this is to be used as a control set to select which league is the current league. Now define the teams, and assign them to the current league. Now that we have the league, the dates, the teams, we enter the shooters and assign them to the league and to a team. That will be all the background work. If a shooter only shoots one league per year, then that shooter will be unassigned from all leagues and all teams but their data will stay in the DB. The same goes for a team, if that team does not exist for a league, then they will be unassigned. The progress / problems that I am having: 1) I have defined a main data entry form that will show the current league based off of the control record in the club info. 2) I have added sub-forms that then show the current teams for that league 3) I then added sub-forms to display the current shooters for that team, for that league. 4) The above three items are working fine. I have a problem in that I can not make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form. 5) In the original design I did not break out the scores, I had one record that had multiple fields slow1, slow2.slow14, timed1, timed2.timed14. I was able to make that work for me until I had to create reports that needed to add up all the fields and provide the 5 calculations for each record times the number of shooters.this just made Access stop responding and hang. Thus I followed a more proper DB design about breaking things apart and trying to let it run more efficiently. Anyone willing to add input is more than welcome! Thank you in advance! Jeff "BruceM" wrote: It may take a bit longer than sending the database, but who don't you describe your database structure here so that all may benefit from the discussion? Besides, there are people who do consulting work, but it is a lot to start off asking somebody to do that for free. Sometimes people will offer to take a look after a while, but it is not really the place to start. You don't need every single bit of information. For instance, the Members table (tblMembers) may be described something like: tblMembers MemberID (autonumber PK, or primary key) FirstName etc. You don't need to show fields for Address, Phone, and so on. They don't alter the basic functioning of the database. You will need to provide some general information, such as whether a member can be in more than one league or on more than one team. Assuming a member would belong to only one league and to only one team, you may just need a team table (tblTeam): tblTeam TeamID (autonumber PK) MemberID (FK, or foreign key) League (enter the name of the league) tblScores ScoreID (autonumber PK) MemberID (FK) Week Score If scores are by team maybe the FK would be TeamID instead of MemberID. If you need both (team scores and individual statistics) that's another situation. Some of this stuff depends on exactly what you need to do. One thing you probably do not want to do is to build a table that contains a field for Week 1 score, Week 2 score, etc. By linking tblScores to tblTeam (or tblMember) you can establish a week-by-week listing without redundancy. If you want to track scores over the course of several seasons that is yet another wrinkle. None of these things are problems, but the answers will have a lot to do with how you design your database. "HD87glide" wrote: Hello group! Let me start by saying 'I am a novice'. I have done some things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
#7
|
|||
|
|||
Hello Ken, the original design of the forms was to have a main-form from the
leagues table, whose selection was from the club table (control record). Then I applied a stacked sub-form from the teams table. This gave me all the active teams for that league. I then stacked another sub-form on top of that to display the active members of that team for that league. All of that is working fine. The problem comes when I try to tie the league week #, league date, slow, timed, and rapid onto another sub-form to display / enter the data. I have to figure out a way to tie together the week #, and league date to the membership and scores detail tables so that it will show the correct scores for the correct week. When I enter scores into the scores details access know what member I am using. I don't know how to automatically fill in the league id, the week #, or the league date on the scores record. Thanks, Jeff "HD87glide" wrote: Hello group! Let me start by saying 'I am a novice'. I have done some things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
#8
|
|||
|
|||
Hello Ken, the original design of the forms was to have a main-form from the
leagues table, whose selection was from the club table (control record). Then I applied a stacked sub-form from the teams table. This gave me all the active teams for that league. I then stacked another sub-form on top of that to display the active members of that team for that league. All of that is working fine. The problem comes when I try to tie the league week #, league date, slow, timed, and rapid onto another sub-form to display / enter the data. I have to figure out a way to tie together the week #, and league date to the membership and scores detail tables so that it will show the correct scores for the correct week. When I enter scores into the scores details access know what member I am using. I don't know how to automatically fill in the league id, the week #, or the league date on the scores record. Thanks, Jeff "Ken Snell [MVP]" wrote: You were correct to break out scores into separate records in a separate table. You might even wish to break out the type of score (slow, timed, rapid) to its own table and then have the actual scores be a child of that table. I am not following what you mean by "can't make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form"? What is the structure of that subform? What is its recordsource? (I admit that I'm a bit confused because you talk about multiple subforms, but it's not clear if they're all subforms of one main form, or are subforms of each other (stacked/nested). -- Ken Snell MS ACCESS MVP "HD87glide" wrote in message ... Thank you for your responses! Below is the description of the DB. ClubTable: ClubID (Primary Key-PK) Autonumber ClubInfo (address, phone, etc) ClubLeagueID (Foreign Key-FK) to LeagueTable LeaguesTable: LeagueID -PK Autonumber LeagueDesc (2005 Winter, 2005 Summer, 2005 Fall) LeaguesDetail: WeekID - PK Autonumber LeagueID - FK (links to Leagues table) LeagueWeek (1,2,3.14) LeagueDate (actual date of shooting) MembershipTable: MemberID - PK Autonumber MemberInfo (last, first, address, phone, email.) ClassID - FK (links to Calss table) TeamID - FK (links to Team table) LeagueID - FK (links to League table) TeamsTable: TeamID - PK Autonumber LeagueID - FK (links to LeagueID) ScoresTable: ScoresID - PK Autonumber MemberID - FK (links to membership table) LeagueID - FK (links to league table) ScoresDetail: ScoresDetailID - PK Autonumber ScoresID - FK (links to scores table) LeagueID - FK (links to league table) TeamID - FK (links to team table) MemberID - FK (links to membership table) WeekID Slow \ Timed These are the three scores that we need to track for the Rapid / 14 weeks. From these scores we will calculate 5 other Fields from these each week. Now, in theory, the process will be to input the league info, description and the 14 shooting dates. Then go to the club info screen and this is to be used as a control set to select which league is the current league. Now define the teams, and assign them to the current league. Now that we have the league, the dates, the teams, we enter the shooters and assign them to the league and to a team. That will be all the background work. If a shooter only shoots one league per year, then that shooter will be unassigned from all leagues and all teams but their data will stay in the DB. The same goes for a team, if that team does not exist for a league, then they will be unassigned. The progress / problems that I am having: 1) I have defined a main data entry form that will show the current league based off of the control record in the club info. 2) I have added sub-forms that then show the current teams for that league 3) I then added sub-forms to display the current shooters for that team, for that league. 4) The above three items are working fine. I have a problem in that I can not make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form. 5) In the original design I did not break out the scores, I had one record that had multiple fields slow1, slow2.slow14, timed1, timed2.timed14. I was able to make that work for me until I had to create reports that needed to add up all the fields and provide the 5 calculations for each record times the number of shooters.this just made Access stop responding and hang. Thus I followed a more proper DB design about breaking things apart and trying to let it run more efficiently. Anyone willing to add input is more than welcome! Thank you in advance! Jeff "BruceM" wrote: It may take a bit longer than sending the database, but who don't you describe your database structure here so that all may benefit from the discussion? Besides, there are people who do consulting work, but it is a lot to start off asking somebody to do that for free. Sometimes people will offer to take a look after a while, but it is not really the place to start. You don't need every single bit of information. For instance, the Members table (tblMembers) may be described something like: tblMembers MemberID (autonumber PK, or primary key) FirstName etc. You don't need to show fields for Address, Phone, and so on. They don't alter the basic functioning of the database. You will need to provide some general information, such as whether a member can be in more than one league or on more than one team. Assuming a member would belong to only one league and to only one team, you may just need a team table (tblTeam): tblTeam TeamID (autonumber PK) MemberID (FK, or foreign key) League (enter the name of the league) tblScores ScoreID (autonumber PK) MemberID (FK) Week Score If scores are by team maybe the FK would be TeamID instead of MemberID. If you need both (team scores and individual statistics) that's another situation. Some of this stuff depends on exactly what you need to do. One thing you probably do not want to do is to build a table that contains a field for Week 1 score, Week 2 score, etc. By linking tblScores to tblTeam (or tblMember) you can establish a week-by-week listing without redundancy. If you want to track scores over the course of several seasons that is yet another wrinkle. None of these things are problems, but the answers will have a lot to do with how you design your database. "HD87glide" wrote: Hello group! Let me start by saying 'I am a novice'. I have done some things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
#9
|
|||
|
|||
In order to provide suggestions on how you can fill in the data that you
want (league id, week #, league date), can you tell me where those values are stored in the database? or how you calculate them? It sounds as if your subform is correctly tieing them a member, so I'm guessing that we can use the member to find the league ID? But, it also seems more reasonable that you'd be entering scores for many members when the league ID, league date, and week # remain constant, so I'm thinking that you should be setting those in a form above the "score entry" subform. Remember, we cannot see your database, so your descriptions in words are what allow us to "see" it in our minds and to provide assistance. It'll be much easier if we can focus on a narrow point initially, with full description of the setup for that point, and then we can branch out from there. -- Ken Snell MS ACCESS MVP "HD87glide" wrote in message ... Hello Ken, the original design of the forms was to have a main-form from the leagues table, whose selection was from the club table (control record). Then I applied a stacked sub-form from the teams table. This gave me all the active teams for that league. I then stacked another sub-form on top of that to display the active members of that team for that league. All of that is working fine. The problem comes when I try to tie the league week #, league date, slow, timed, and rapid onto another sub-form to display / enter the data. I have to figure out a way to tie together the week #, and league date to the membership and scores detail tables so that it will show the correct scores for the correct week. When I enter scores into the scores details access know what member I am using. I don't know how to automatically fill in the league id, the week #, or the league date on the scores record. Thanks, Jeff "Ken Snell [MVP]" wrote: You were correct to break out scores into separate records in a separate table. You might even wish to break out the type of score (slow, timed, rapid) to its own table and then have the actual scores be a child of that table. I am not following what you mean by "can't make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form"? What is the structure of that subform? What is its recordsource? (I admit that I'm a bit confused because you talk about multiple subforms, but it's not clear if they're all subforms of one main form, or are subforms of each other (stacked/nested). -- Ken Snell MS ACCESS MVP "HD87glide" wrote in message ... Thank you for your responses! Below is the description of the DB. ClubTable: ClubID (Primary Key-PK) Autonumber ClubInfo (address, phone, etc) ClubLeagueID (Foreign Key-FK) to LeagueTable LeaguesTable: LeagueID -PK Autonumber LeagueDesc (2005 Winter, 2005 Summer, 2005 Fall) LeaguesDetail: WeekID - PK Autonumber LeagueID - FK (links to Leagues table) LeagueWeek (1,2,3.14) LeagueDate (actual date of shooting) MembershipTable: MemberID - PK Autonumber MemberInfo (last, first, address, phone, email.) ClassID - FK (links to Calss table) TeamID - FK (links to Team table) LeagueID - FK (links to League table) TeamsTable: TeamID - PK Autonumber LeagueID - FK (links to LeagueID) ScoresTable: ScoresID - PK Autonumber MemberID - FK (links to membership table) LeagueID - FK (links to league table) ScoresDetail: ScoresDetailID - PK Autonumber ScoresID - FK (links to scores table) LeagueID - FK (links to league table) TeamID - FK (links to team table) MemberID - FK (links to membership table) WeekID Slow \ Timed These are the three scores that we need to track for the Rapid / 14 weeks. From these scores we will calculate 5 other Fields from these each week. Now, in theory, the process will be to input the league info, description and the 14 shooting dates. Then go to the club info screen and this is to be used as a control set to select which league is the current league. Now define the teams, and assign them to the current league. Now that we have the league, the dates, the teams, we enter the shooters and assign them to the league and to a team. That will be all the background work. If a shooter only shoots one league per year, then that shooter will be unassigned from all leagues and all teams but their data will stay in the DB. The same goes for a team, if that team does not exist for a league, then they will be unassigned. The progress / problems that I am having: 1) I have defined a main data entry form that will show the current league based off of the control record in the club info. 2) I have added sub-forms that then show the current teams for that league 3) I then added sub-forms to display the current shooters for that team, for that league. 4) The above three items are working fine. I have a problem in that I can not make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form. 5) In the original design I did not break out the scores, I had one record that had multiple fields slow1, slow2.slow14, timed1, timed2.timed14. I was able to make that work for me until I had to create reports that needed to add up all the fields and provide the 5 calculations for each record times the number of shooters.this just made Access stop responding and hang. Thus I followed a more proper DB design about breaking things apart and trying to let it run more efficiently. Anyone willing to add input is more than welcome! Thank you in advance! Jeff "BruceM" wrote: It may take a bit longer than sending the database, but who don't you describe your database structure here so that all may benefit from the discussion? Besides, there are people who do consulting work, but it is a lot to start off asking somebody to do that for free. Sometimes people will offer to take a look after a while, but it is not really the place to start. You don't need every single bit of information. For instance, the Members table (tblMembers) may be described something like: tblMembers MemberID (autonumber PK, or primary key) FirstName etc. You don't need to show fields for Address, Phone, and so on. They don't alter the basic functioning of the database. You will need to provide some general information, such as whether a member can be in more than one league or on more than one team. Assuming a member would belong to only one league and to only one team, you may just need a team table (tblTeam): tblTeam TeamID (autonumber PK) MemberID (FK, or foreign key) League (enter the name of the league) tblScores ScoreID (autonumber PK) MemberID (FK) Week Score If scores are by team maybe the FK would be TeamID instead of MemberID. If you need both (team scores and individual statistics) that's another situation. Some of this stuff depends on exactly what you need to do. One thing you probably do not want to do is to build a table that contains a field for Week 1 score, Week 2 score, etc. By linking tblScores to tblTeam (or tblMember) you can establish a week-by-week listing without redundancy. If you want to track scores over the course of several seasons that is yet another wrinkle. None of these things are problems, but the answers will have a lot to do with how you design your database. "HD87glide" wrote: Hello group! Let me start by saying 'I am a novice'. I have done some things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
#10
|
|||
|
|||
The 'ClubTable' holds the club info (name, address, etc) and also a control
field. That field is the 'ClubLeagueID'. This just tells us what the current league is. The connection that I have between the 'ClassTable' and the 'MembershipTable' is a one to many, works OK. The connection between 'TeamsTable' is also a one to many, works OK. This is where I don't know which way to turn. The first table, 'LeaguesTable' is a master table which links to a details table 'LeagueDetails' which tells us the week number and week date. I think that there should be a connection between the 'MembershipTable' and the 'LeaguesTable' but no connection between the 'LeaguesDetail'. The second table, 'ScoresTable' is a master table which links to a details table 'ScoresDetail' which allows us to enter the weekly scores. Right now I have a connection between the 'MembershipTable' and the 'ScoresTable'. I am trying different things to try and make it work. The ideal form will have one team on a form, a subform will contain each team member for that team. I am trying to make another subform off the team form that will have the week number, week date, slow, timed, rapid, + 5 other calculated fields that are needed. I know that it is not correct to store calculated fields in a DB, but it almost makes sense to store the values as that record will not change once it has been entered into the system and during reports I would just have to read that team member to retrieve that info for a report....Does that sound correct? If you have further questions on the design or see where I went wrong please let me know, I have listed the complete DB design in the thread of this discussion group before. I am open toll all types of help and willing to make the changes or scrap everything to start over. Thanks for your help! Jeff "Ken Snell [MVP]" wrote: In order to provide suggestions on how you can fill in the data that you want (league id, week #, league date), can you tell me where those values are stored in the database? or how you calculate them? It sounds as if your subform is correctly tieing them a member, so I'm guessing that we can use the member to find the league ID? But, it also seems more reasonable that you'd be entering scores for many members when the league ID, league date, and week # remain constant, so I'm thinking that you should be setting those in a form above the "score entry" subform. Remember, we cannot see your database, so your descriptions in words are what allow us to "see" it in our minds and to provide assistance. It'll be much easier if we can focus on a narrow point initially, with full description of the setup for that point, and then we can branch out from there. -- Ken Snell MS ACCESS MVP "HD87glide" wrote in message ... Hello Ken, the original design of the forms was to have a main-form from the leagues table, whose selection was from the club table (control record). Then I applied a stacked sub-form from the teams table. This gave me all the active teams for that league. I then stacked another sub-form on top of that to display the active members of that team for that league. All of that is working fine. The problem comes when I try to tie the league week #, league date, slow, timed, and rapid onto another sub-form to display / enter the data. I have to figure out a way to tie together the week #, and league date to the membership and scores detail tables so that it will show the correct scores for the correct week. When I enter scores into the scores details access know what member I am using. I don't know how to automatically fill in the league id, the week #, or the league date on the scores record. Thanks, Jeff "Ken Snell [MVP]" wrote: You were correct to break out scores into separate records in a separate table. You might even wish to break out the type of score (slow, timed, rapid) to its own table and then have the actual scores be a child of that table. I am not following what you mean by "can't make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form"? What is the structure of that subform? What is its recordsource? (I admit that I'm a bit confused because you talk about multiple subforms, but it's not clear if they're all subforms of one main form, or are subforms of each other (stacked/nested). -- Ken Snell MS ACCESS MVP "HD87glide" wrote in message ... Thank you for your responses! Below is the description of the DB. ClubTable: ClubID (Primary Key-PK) Autonumber ClubInfo (address, phone, etc) ClubLeagueID (Foreign Key-FK) to LeagueTable LeaguesTable: LeagueID -PK Autonumber LeagueDesc (2005 Winter, 2005 Summer, 2005 Fall) LeaguesDetail: WeekID - PK Autonumber LeagueID - FK (links to Leagues table) LeagueWeek (1,2,3.14) LeagueDate (actual date of shooting) MembershipTable: MemberID - PK Autonumber MemberInfo (last, first, address, phone, email.) ClassID - FK (links to Calss table) TeamID - FK (links to Team table) LeagueID - FK (links to League table) TeamsTable: TeamID - PK Autonumber LeagueID - FK (links to LeagueID) ScoresTable: ScoresID - PK Autonumber MemberID - FK (links to membership table) LeagueID - FK (links to league table) ScoresDetail: ScoresDetailID - PK Autonumber ScoresID - FK (links to scores table) LeagueID - FK (links to league table) TeamID - FK (links to team table) MemberID - FK (links to membership table) WeekID Slow \ Timed These are the three scores that we need to track for the Rapid / 14 weeks. From these scores we will calculate 5 other Fields from these each week. Now, in theory, the process will be to input the league info, description and the 14 shooting dates. Then go to the club info screen and this is to be used as a control set to select which league is the current league. Now define the teams, and assign them to the current league. Now that we have the league, the dates, the teams, we enter the shooters and assign them to the league and to a team. That will be all the background work. If a shooter only shoots one league per year, then that shooter will be unassigned from all leagues and all teams but their data will stay in the DB. The same goes for a team, if that team does not exist for a league, then they will be unassigned. The progress / problems that I am having: 1) I have defined a main data entry form that will show the current league based off of the control record in the club info. 2) I have added sub-forms that then show the current teams for that league 3) I then added sub-forms to display the current shooters for that team, for that league. 4) The above three items are working fine. I have a problem in that I can not make the week number (1..14), the shooting date, or the slow, timed, rapid fields to show up in another sub-form. 5) In the original design I did not break out the scores, I had one record that had multiple fields slow1, slow2.slow14, timed1, timed2.timed14. I was able to make that work for me until I had to create reports that needed to add up all the fields and provide the 5 calculations for each record times the number of shooters.this just made Access stop responding and hang. Thus I followed a more proper DB design about breaking things apart and trying to let it run more efficiently. Anyone willing to add input is more than welcome! Thank you in advance! Jeff "BruceM" wrote: It may take a bit longer than sending the database, but who don't you describe your database structure here so that all may benefit from the discussion? Besides, there are people who do consulting work, but it is a lot to start off asking somebody to do that for free. Sometimes people will offer to take a look after a while, but it is not really the place to start. You don't need every single bit of information. For instance, the Members table (tblMembers) may be described something like: tblMembers MemberID (autonumber PK, or primary key) FirstName etc. You don't need to show fields for Address, Phone, and so on. They don't alter the basic functioning of the database. You will need to provide some general information, such as whether a member can be in more than one league or on more than one team. Assuming a member would belong to only one league and to only one team, you may just need a team table (tblTeam): tblTeam TeamID (autonumber PK) MemberID (FK, or foreign key) League (enter the name of the league) tblScores ScoreID (autonumber PK) MemberID (FK) Week Score If scores are by team maybe the FK would be TeamID instead of MemberID. If you need both (team scores and individual statistics) that's another situation. Some of this stuff depends on exactly what you need to do. One thing you probably do not want to do is to build a table that contains a field for Week 1 score, Week 2 score, etc. By linking tblScores to tblTeam (or tblMember) you can establish a week-by-week listing without redundancy. If you want to track scores over the course of several seasons that is yet another wrinkle. None of these things are problems, but the answers will have a lot to do with how you design your database. "HD87glide" wrote: Hello group! Let me start by saying 'I am a novice'. I have done some things in Access before, but I am in deeper than I thought on a project I said I would do. I belong to a local shooting club. We have 3 leagues a year, winter, summer, fall. In each league we have teams, in each team we have members, for each league there will be 14 weeks worth of scores. I have the DB defined but I am having a problem with forms/sub-forms on how to capture the data. What I am asking is for some help, someone to look at the DB and help me figure out where I went wrong and what I need to do to correct my errors. If you feel up to the challange, please reply and we can work out the details. Thanks, Jeff |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Advice on forms | Pehr Jansson | General Discussion | 1 | January 25th, 2005 04:12 AM |
Word expert needed | Terry Irwin | Formatting Long Documents | 5 | December 9th, 2004 01:06 PM |
Advice for preparing a long document | Sandra Jensen | Formatting Long Documents | 15 | November 25th, 2004 12:56 AM |
Word expert | Terry Irwin | Powerpoint | 3 | November 6th, 2004 06:07 PM |
Word of Advice for New Users | Nikos Yannacopoulos | New Users | 4 | May 20th, 2004 11:47 PM |