Table design and viewing on form

[Hardhit]

Hello,

I have a database which I use to keep scores during a shooting contest.
In this database I have a form that's linked to a table to enter the shooter
scores.
In the current table I have 60 fields to collect the shot scores for each
target.

I would like to simplify this table and I have an idea on how to do this.

I would make a design as follows.

MatchID SCOREID REEKS S1 S2 S3 S4 S5 S6 S7 S8 S9 S10
Totaal

1 1 1 9 9 9 9 9
9 9 9 9 9 90
1 1 2 9 9 9 9 9
9 9 9 9 9 90
1 1 3 9 9 9 9 9
9 9 9 9 9 90
1 1 4 9 9 9 9 9
9 9 9 9 9 90
1 1 5 9 9 9 9 9
9 9 9 9 9 90
1 1 6 9 9 9 9 9
9 9 9 9 9 90


540

This would mean that the table consists of only 14 fields instead of
currently 64 fields.
A field that is also in the table but not shown in the above shown table is
the ShooterID.

So I have 1 row with each 10 shots, the Row total and the match total can be
calculated so these I would keep out of the table.
The total match consists of 60 shots.
How would I make a form to fill this ? The 60 shots need to be visible in
the same screen.

Regards,
Peter

 

[Klatuu]

What you really need is 4 tables
tblShooter - Contains info about the shooter
ShooterID - PK
ShooterName
etc.

tblMatch - Indentifies the match being shot
tblMatchID - PK
MatchName
MatchDate
etc.

tblMatchScores - Relates the shooter to the match. Not all shooters in your
database will be shooting in every match. Since a shooter shoots many
matches and a match has many shooters, you need a junction table to resolve
the many to many relationship. This is the table
MatchShootID - PK
MatchID - FK to tblMatch
ShooterID - FK to
ShotLine - Shows the order of the shots. 1-10, 11-20, etc.

Now, you can use a query that relates the Match and Shooter info as the
record source for a main form. Then show the records from tblMatchScores in
a subform.

tbl

 

[Evan Keel]

Hello,

I have a database which I use to keep scores during a shooting contest.
In this database I have a form that's linked to a table to enter the shooter
scores.
In the current table I have 60 fields to collect the shot scores for each
target.

I would like to simplify this table and I have an idea on how to do this.

I would make a design as follows.

MatchID SCOREID REEKS S1 S2 S3 S4 S5 S6 S7 S8 S9 S10
Totaal

1 1 1 9 9 9 9 9
9 9 9 9 9 90
1 1 2 9 9 9 9 9
9 9 9 9 9 90
1 1 3 9 9 9 9 9
9 9 9 9 9 90
1 1 4 9 9 9 9 9
9 9 9 9 9 90
1 1 5 9 9 9 9 9
9 9 9 9 9 90
1 1 6 9 9 9 9 9
9 9 9 9 9 90


540

This would mean that the table consists of only 14 fields instead of
currently 64 fields.
A field that is also in the table but not shown in the above shown table is
the ShooterID.

So I have 1 row with each 10 shots, the Row total and the match total can be
calculated so these I would keep out of the table.
The total match consists of 60 shots.
How would I make a form to fill this ? The 60 shots need to be visible in
the same screen.

Regards,
Peter

Three tables will do:

Shooters
ShooterID (PK)
ShooterName
etc..

Matches
MatchID (PK)
MatchDate
etc..

ShooterMatches
ShooterID (PK)
MatchID (PK)
TargetNumber (PK)
TargetScore

This allows for:

A Shooter competes in many Matches
A Match has many shooters
A Match can have any number of Targets
A Shooter competing in one Match will have one score for a target

 

[Klatuu]

That would be the best table structue.
The OP would then need to come up with a plan to get all scores shown on a
form for one shooter.
Using a subform would work, but the shots would be shown vertically rather
than horizontally.

 

[Hardhit]

Dave,

Would this mean that I need to define 60 scoring fields in the
tblMatchScores ?
I currently have this setup like this but I was wondering if this could be
simplified ?

Regards,
Peter

 

[Klatuu]

Well, you could, but that would not be normal database design.
The correct design would be what evan posted. Then use a subform to display
your score fields.

 

[Hardhit]

The problem then is that the field are in vertical alignment and I need the
field to show as

R1 10 10 10 10 10 10 100
R2 10 10 10 10 10 10 100
R3 10 10 10 10 10 10 100
....
R6 10 10 10 10 10 10 100

Total 600

How would I bring that into a form ?

Regards,
Peter

 

[Klatuu]

I know from your original post what you wanted, that is why I suggested a
denormalized table that has ten fields per record for scores.

For 60 shots, then you need 6 records per shooter per match.

 

[Evan Keel]

That would be the best table structue.
The OP would then need to come up with a plan to get all scores shown on a
form for one shooter.
Using a subform would work, but the shots would be shown vertically rather
than horizontally.

How about a pivot. Not knowing Access all that well, in pseudo code it would
be:

List by match, by shooter across target targetscore. I am sure Pivot allows
something like this.

Evan

 

[Klatuu]

Pivot table produces an uneditable recordset.

 

[Evan Keel]

Pivot table produces an uneditable recordset.

Oh, OP needs to edit in place. Didn't understand that. So what the guy needs
to do is use a list view control in report view and treat it like a 2
dimensional array. Using VBA you can check for changes. Ugly, but do-able.

Evan

 

[Klatuu]

That is a bit contorted.
There are times when a denormalized table is in order.
I think this is one of them.

 

[Hardhit]

Evan, Dave,

Thanks for the help and information but I will go for Dave's ID and use a
de-normalised table with 10 fields for shots and use a lineID.

Thanks and Regards,
Peter

 

[Evan Keel]

That is a bit contorted.
There are times when a denormalized table is in order.
I think this is one of them.

I humbly disagree. Display requirements should never take precedence over
sound design guidelines.

Evan

 

[Evan Keel]

Evan, Dave,

Thanks for the help and information but I will go for Dave's ID and use a
de-normalised table with 10 fields for shots and use a lineID.

Thanks and Regards,
Peter

IMHO, big mistake and will lead to major headaches later.

Evan