[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2010 correctly configure tables and relationships to retrieve all records

Posted on 2014-01-02
7
Medium Priority
?
284 Views
Last Modified: 2014-01-13
Hi,

I have a functioning Access 2010 DB that I use to keep track of basketball teams for scouting purposes. The basic table concept/design is:
Year>School>Team>Team Summary>Game>Quarter Scores>Player Points>Players

So in any given year a school has a team (players and coaches) that play games and report scores for both the team and the players. Here's the problem.

When setting the relationships, The game table has two entries, one for the Home team, one for Visitor. I can only link from Team Summary to either Home or Visitor in the GameID table but have not been able to work out a design that would allow me to get both home and visitor records as part of the 'normal flow'.

I have been using queries to get to this data but wonder if I have overlooked a re-design that would return both home and visitor records as the result of a relational design rather than making use of queries. I have attached a pdf with the relational map.

Thanks,

Den
2013-2014-Relationship-Report.pdf
0
Comment
Question by:froshcoach
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 13

Accepted Solution

by:
Norm Dickinson earned 1500 total points
ID: 39751883
Database normalization is the key to relational databases and what sets them apart from simple flat files. You seem to have gotten to a level of normalization that is one step shy of what you need to work with the data in a meaningful way, so a little redesign is probably in order. For some good tips and an overview, see http://office.microsoft.com/en-us/access-help/database-design-basics-HA010341617.aspx.

In this case you may want to rethink the division of home team / visiting team, and instead create a separate entry to reflect each game, from each team's perspective instead of just from the home team's perspective. Sure, it will give you twice as many "games" to track, but it will let you design the relationships you need. Relational databases are great at storing lots of information like that without taking up lots of extra room, so it should remain efficient.
0
 

Author Comment

by:froshcoach
ID: 39752034
tqfdotus,

I had considered separating the game table into two tables but I am still faced with the same problem.

In the current design, if I want to get all of the games played by Team A, I need to interrogate all records in the GamesID table  but the problem is looking for two fields in one record. If I split the table into two I need to check all records in two tables. I'm not sure how I can get this to cascade.

I tested splitting the game table into a home and visitor table but in datasheet view, when I click on the + sign to expand the next level after TeamSummary, I am presented with a dialog to select which table should be next in the path so I can select either home or visitor.

This would appear to be an inherent design problem as there is a split path between the team table and the next step to the game data, either home/visitor field in a record or home/visitor table. Perplexing.

Thanks tqfdotus, hope I'm not missing something simple.
0
 
LVL 13

Expert Comment

by:Norm Dickinson
ID: 39752067
Maybe if you keep the same design you have now, but give games a meaningful identifier based on whether you are recording them from the home perspective or the visitor perspective it could work. For instance, whatever you call a game now, add a single letter to the end of it to record it from the home perspective and then copy all the data to a new record, changing only the single letter designation and switching the relevant info to reflect the other team. That way when you look it up or perform calculations on the data it will be able to consolidate the information the way you want it.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:froshcoach
ID: 39752346
tqfdotus,

No matter how I look at it, this particular scenario has an either/or logic that does not lend itself to the relational nature of the DB that relies on a unique result based on a prior selection.

I just tested by combining home/visitor into one field called team with an extra yes/no field called home. This has the effect of returning all games that a team has participated in but doubles the size of the games table, two records for each game and which requires a complete redesign of forms and queries. It seems, however, that this may be the only option unless a clever solution can be found.

Thanks again,

Den
0
 
LVL 13

Expert Comment

by:Norm Dickinson
ID: 39752362
In effect you will not be doubling the size of the game table if you only record information related to one team for each "game," but instead you will have twice as many "games" with half as much information in each.
0
 

Author Closing Comment

by:froshcoach
ID: 39777017
a general but accurate assessment of the issue
0
 
LVL 13

Expert Comment

by:Norm Dickinson
ID: 39777265
Thanks. The problem with relational databases is that if you start out on the wrong foot and have data recorded, forms and reports created and generally are using the database, it is extremely difficult to change the fundamentals without having to re-tool the rest. That would be one big advantage of a flat-file database design, albeit probably the only advantage.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question