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

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

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

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

Ken, Thank You for your response. I have posted the information as a reply
like 'BruceM' has suggested so that others may benefit from my problem as
well.

I also have to admit that I do not understand what you mean by 'is not real'
from your address...I don't see it.

Thanks,
Jeff


"Ken Snell [MVP]" wrote:

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

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

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

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

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

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