Seeking some expert advice.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
(e-mail address removed)
 
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).
 
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
 
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
 
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>
 
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
 
Your table structure does not match "naturally" with what you want to do on
the form/subform/subsubform setup. The tables do not link together in the
same relationships that you want to show on the form structure.

You want your form to be this:
Team (main form)
Members (subform) on team
ScoreDetails (subsubform) for each member


Your table structure is this:
League
Team

Teams and Leagues in separate tables
Members

Members and Leagues in separate tables
Scores
ScoresDetails

See this inconsistency in the relationships? You duplicate relationships in
many tables (if a member is part of a team, and that team is part of a
league, then, if you know the member, you also know the team and league --
but you just need to have the TeamID field in the Members table; you don't
also need the LeagueID field, because you get that from the Teams table).
And you don't have a direct relationship as you envision for the form setup.

You're trying to overcome this structural problem by adding all kinds of
additional, inappropriate foreign keys in the ScoreDetails table as a
result: LeagueID, MemberID, TeamID. The "real" foreign key in this table is
ScoresID, which relates back to the ScoresTable table, and from there you
relate to members, team, league, etc.

Now, it probably is possible to make the form that you envision, using your
current table setup. But it will involve a fair amount of programming to go
get queries that will give you the desired records, to write foreign key
values into fields in the subform and the subsubform, etc. In other words,
you won't be able to use ACCESS's built-in features that do this for you
when the form/subform/subsubform is structured as your tables are
structured. Sometimes, this is ok, though. But other times, it's telling you
that your database design is not the best you could have.

What is the intent of the ScoresTable table in your database? It appears to
be used just to get a single "foreign key" value for the ScoreDetails table
instead of using composite primary key; and then your ScoreDetails table
goes ahead and uses those same fields anyway. So perhaps you should delete
ScoresTable and use the MemberID field as a foreign key (and as part of a
composite primary key) in the ScoreDetails table.

I trust that this will help you in your thinking ...
--

Ken Snell
<MS ACCESS MVP>
 
Ken, I can follow what your are saying to a point. The one thing that your
design is lacking, is that I want to keep multiple years worth of data in the
database. So, I need to have the scores tied to the ‘LeagueID’ and to the
‘MemberID’. That way down the road I will have the ability to go back and
select a league from three years ago and re-print all of the personal score
sheets.

I have removed the ‘ScoresTable’ and re-named the ‘ScoresDetail’ back to
‘ScoresTable’ and have the following keys:
ScoresID – PK
MemberID – FK
LeagueID – FK

The ‘ClubTable’, ‘LeaguesTable’, ‘LeaguesDetail’, ‘MembershipTable’,
‘TeamsTable’, have stayed the same.

Any other suggestions to try?

Thanks,
Jeff
 
Sounds as if you need a "Rosters" table of some type, where you can store
the individual memberships of each league for each year. Then you could use
that rosterID value as the linking field to your scores data.

Rosters table would be a junction table, something like this:
RosterID (PK)
LeagueID
YearID
MemberID

Then RosterID could be the foreign key in the Scores table.
 
Ken, I just wanted to say 'Thank You' for all the help that you have provided
to me on this project. The scope of the project seems to be over my head. I
have followed your suggestions and made the changes, but I still end up with
more and more questions.

I must say that I have learned a lot more about Access trying to complete
this task. Thanks again for you help!

Jeff
 
ACCESS is a great software, but does come with a steep learning curve. Hang
in there.... you'll reach the summit eventually!
 
Back
Top