 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2007Too many table fields
I have been working with Access for almost 2 years now and I feel I have an excellent comprehension of database normalization. I'm trying to design a system to track and update the depth chart for a sports team. Specifically 4 lines of 5 positions each, 4 lines of 4 positions each, and 4 lines of 3 positions each for each team. The idea is to be able to view all these lines on a single form for any given team and change players using combo boxes. (Perhaps using tabs for the different groups of lines). It seems to me this might be one of the few situations for a table with a lot of fields. A field for each of the positions. However, it also seems like it goes against good database structure. Any ideas? >I have been working with Access for almost 2 years now and I feel I have an >excellent comprehension of database normalization. [quoted text clipped - 12 lines] > >Any ideas? I'd say it does indeed go against good database structure. Each player has some specific attributes: a Team, a Line, a Position. It's still a one (team) to many (player) relationship. You could certainly have Subforms based on queries, selecting a particular position; the players in that position could be displayed in sort order by line. Alternatively, or in addition, you could have subforms based on lines, showing all the positions for the first team, the second team and so on. Having 48 bound combo boxes on a form would be a) confusing and b) really hard to manage! John W. Vinson [MVP] So I guess, there would be another look-up table that housed each position and each line, probably 40+ records that were then linked to each playerID accordingly. That way a single player could occupy mulitple spots on the same depth chart, which is a must. In one table though, if a new team is added each position is already reserved with a null value. In a relationship database you'd have to have some type of append query to force the new team to display the unoccupied roster spots. > >I have been working with Access for almost 2 years now and I feel I have an > >excellent comprehension of database normalization. [quoted text clipped - 26 lines] > > John W. Vinson [MVP] >So I guess, there would be another look-up table that housed each position >and each line, probably 40+ records that were then linked to each playerID [quoted text clipped - 5 lines] >some type of append query to force the new team to display the unoccupied >roster spots. I can't see why an empty "placeholder" would ever be needed. If there is no placekicker, there simply would be no placekicker record in the table. John W. Vinson [MVP] The idea is that the user can go to a new "Team" and all of the positions on the depth chart exist, he/she just needs to select a player to fill in the position. If everything is relational then the user has to know all of the positions because there is no way to see what you are missing without comparing what you have added with a complete depth chart (either in your head or on a sheet of paper). Perhaps with an append query all of the positions could be added instantly with a default (or null) player ID. That way the user could see all of the depth chart positions and keep the database relational. I'll try that, thanks. > >So I guess, there would be another look-up table that housed each position > >and each line, probably 40+ records that were then linked to each playerID [quoted text clipped - 10 lines] > > John W. Vinson [MVP] >The idea is that the user can go to a new "Team" and all of the positions on >the depth chart exist, he/she just needs to select a player to fill in the [quoted text clipped - 8 lines] >with a default (or null) player ID. That way the user could see all of the >depth chart positions and keep the database relational. That would be one possible reason to have such "dummy" records... like the draft, "A Player to be named later". John W. Vinson [MVP] and if A. and B. were not bad enough with ~50 fields you could easily bump up against the 2k record size character max depending on your field lengths.....  SignatureNTC > I have been working with Access for almost 2 years now and I feel I have an > excellent comprehension of database normalization. [quoted text clipped - 12 lines] > > Any ideas? the limits in Access is why I moved to SQL Server and Access Data Projects a long long long time ago > and if A. and B. were not bad enough with ~50 fields you could easily bump > up [quoted text clipped - 20 lines] >> >> Any ideas? in departmental, small business and non-profits - - I've always been able to meet their need with Access......I like the product for it's affordability; often they have it as part of their Office anyways....I have no complaints against Access , at least in comparison to any other software I've every seen - they all have some quirks....but size is size and I would only go to SQL server if the issue is file size and transaction volume...... SignatureNTC > the limits in Access is why I moved to SQL Server and Access Data Projects a > long long long time ago [quoted text clipped - 23 lines] > >> > >> Any ideas? "Tom Wimpernark" is another of A.aron K.empf's aliases. This person posts only to bash MDB files, and anyone who uses them. To him there is only one answer, whatever the question: ADP bound to SQL/Server. John W. Vinson [MVP] Aaron Kem.pf is posting under a new alias. > the limits in Access is why I moved to SQL Server and Access Data Projects > a long long long time ago [quoted text clipped - 23 lines] >>> >>> Any ideas? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.