Using MS Access/Soccer Database

Advertisement


Question
Hi,
Apologies for the rather vague starting point of this question, but I am looking for extensive support in my attempt to create a db which will house a number of tables, each supporting a number of queries.

Table 1 - Fixtures / Results - this lists fixtures and results, competition type, attendance, ad-hoc notes

Table 2 - Opposition - this lists opponents in a list format

Table 3 - Players - this lists players who have featured for the club and the seasons

Table 4 - Competition Type - this lists competitions in a list format

I would then like to integrate each of the tables, so that when adding an entry to the Fixtures / Results table, it links to both the Opposition and Competition table. If a required entry isn't found, the user has to add them to the relevant table.

From here, each fixture would be given an id. This id should then link to an import form. The form should allow the user to add the players who started the match (11 of them), along with those who are substitutes (up to 7). There should be fields for goals scored by player name, yellow and red cards by player name. In addition, there should be a field for substitutes and who they replaced. On this page, there should be a simple box where details of the opposition can be added.

Reports and Queries will be required, but for now I wondered if this project is one you would be able to assist in? I have very basic knowledge and can send you my file so far so you can see what level I am at to determine whether or not you would like to get involved...my questions could range from the complicated to the most basic. I have MSAccess books and CDs, but one-to-one contact is invaluable.

I look forward to hearing from you, and appreciate it if you have read this far.

Kind regards,
Chris Jones

Answer
I would be happy to try to help. Frankly, though I don't quite understand tables 1 and 4. I'm assuming your perspective here is from a team, not a league. So I would imagine a structure like this:

tblPlayer
PlayerID (PK Autonumber)
FirstName
Surname
PositionID (foreign key)
other contact info

tblMatch
MatchID (PK Autonumber)
MatchDate
LocationID (FK)
OpponentID (FK)
Attendance
CompetitionTypeID (FK)
Results

tblMatchPlayer
MatchPlayerID (PK Autonumber)
MatchID
PlayerID
Goals
RedCards
YellowCards
Substitute
SubstituteID

tblOpponent
OpponentID (PK AutoNumber
TeamName

tblLocation
LocationID (PK Autonumber)
Location

tblPosition
PositionID (PK Autonumber)
Position

From there You should be able to get reports of all matches and player records etc.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.