Table Design 2

[Chris121]

Hi Folks,
I'm trying to help a football club physiotherapist with a database to record the training sessions each individual player attends. It needs to show each players total amount of training sessions in a selcted date range.

I made a simple table with the session date and details and a field for each player in the squad ie fieldname = playername with a yes/no checkbox for each player (I think this was a mistake)

Coming to the query, I can use a parameter query to specify the date range, but how do I select the individual player field? I could do a query for each player, but thats seems a bit long-winded and unnessecary. Can a 2nd parameter select the players field to be diplayed?

Do I have to go back to the drawing board and design the table differently?

Thanks
Christine.

 

[willir]

Chris121

I think you need a table for the players.

tblPlayer
PlayerID (primary key, autonumber)
PlayerFName (text, 15 to 25)
PlayerLName (text, 15 to 25)
or
PlayerName (text, 35 to 50, first + last, or vice versa)
+ anything else relavent for a player

Then in your session table, I don't know what you call it, so I will use tblSession, you would have as a minimum...

tblSession
SessionID (primary key, autonumber)
PlayerID (long integer, foreign key to tblPlayer autonumber)
SessionDate (date)
+ anything else you want to track for sessions

For a walk through on how I got to "here", you can review this thread...
thread700-628486 it may help to understand primary and foreign keys...
thread700-631032 on the session table...
The physiotherapist may want to track other things to help him or her have a beter understanding of the nature of player's issues. (For example, if there is more than one physiotherapist)

- Number of minutes a session lasted
- Reason player needs session, i.e., type of injury
- Physiotherapist, if more than one

Richard

 

[Chris121]

Thanks Richard
I did think of using a players table initially, but the idea came unstuck when designing an entry form for each training session. If 30 players atteded a session, there would have to be 30 records added to the table, and often there are 2 sessions in one day! I wanted to just create 1 record per session, showing a simple yes/no (from check-box on form) for each player.
Am I being a bit paranoid about creating a huge number of records? Can a single form produce 20-30 records or would the form have to be completed once for each player in attendance?

 

[GoDawgs]

It seems like no matter what you're going to have to enter the names of all the players at least once...either once to store them all in the players table or once to put a check by them in your idea. If that's the case then you should definitely create the players table.

One thing Richard might have been missing...if a player can attend multiple sessions (and multiple people are in one session...which is definitely the case) then you'll need an intermediate table between Session and Players that would just store the session id and player id.

As far as forms go, I would create a form based on the Session table that just has the session date and all that info, then create a subform that would be a list of all the players in that session (based on that intermediate table you created). I would make the subform datasheet view only (so it looks like a table) and make the player field a combo box...so basically all you'll have to do is type in the info for the session itself and then select from the combo box the 30 players that were there as long as they are already in the Players table. If not, then you'll have to add them to the table first, then go back and select them from the combo box. Hope that helps.

Kevin

 

[willir]

Chris121

It all depends on what you want to get out of the database. I always start with the report and information I want and work backwards.

The idea of using a relational database is to reduce errors and work by separating each entitiy from another. In the old flat file style database and in spreadsheets, you would have to enter the player's name for every session. This could lead to errors. "Jack O'Brien", "Jack OBrien", "Jack Obrian", "J. O'Brien" and "J O'Brien" would all show up differently. The report would show five different people whereas there is only one. By moving the player info to a separate table, the name is entered only once.

For a relational database, there are five rules for normilization which basically are guidlines for seperating data into what the tables.

Then there is common sense. For example, in a GL account, there are 1,000's of transactions every month. To caclualte the monthly balance, one has to run a query to get this number even if looking at the balance for five years ago. Some designers will create a "balance" file that stores the monthly balances. If there are any transactions for the month, the monthly balance is recalculated. This balance file can be considered to break the third normal form. But it adds a boost to performance, and it allows for the eventual purge of the transactions while maintain a summary view of the GL.

The idea of a database is to turn "data" into "information". I see people run into problems with Access running queries and reports -- they can not get this information, or need an incredibly long query to get their neumbers, data errors, work around, etc. These can / are traced to a design or schema issue.

Not too long ago, I had to fix a contact database for a non-profit organization. Here, they had used yes / no fields in one table to track various groups volunteers and staff belonged to. Then they want to send out just one mailer to a family. This objective was extremely problematic because of the flat file approach.

Another example can be seen in the query I pasted into this post...
thread701-627301 said all this, use what works for you.

Richard

 

[willir]

Christine

"when designing an entry form for each training session" ???

Are you designing a form for each session? Such as session type? As in for a sprained wrist, use this session form. For a pulled back, use another?

Richard

 

[Chris121]

Hi Richard,
Thanks and thanks for your input Kevin.
OK boys, I'll use a players table!! In trying to make things simple for me, I guess I was prepared to dump what I was taught about relational database design - Hanging my head in shame ;-)

Firstly, sorry for the confusion Richard, There is only to be one entry form. It will have it will have the following entries:- Date/time:Coach:warm up type:warm up time:session type:session time: and then players involved.
It's the players involved bit that I wanted to put yes/no check boxes next to the 25 possible names, rather than have 25 combo boxes to drop and scroll through to select each player involved.

Working backwards from the required result seems like a great idea, so here goes: I will need to run a query (Parameter query?) where I am prompted to enter or select a player name in a combo and enter a date range. When the query is run, it will show the sum of the number of sessions that player had attended between those dates. A report isn't needed, a print of the query result will be ok.

I'm not too sure what Kevin meant regarding the intermediate table either? The players table I now have has:-
playerID (primary key)(autonumber)
Squad No. (number)
Firstname (text)
Lastname (text)

I hope that's clearer now.

Christine.

 

[Chris121]

UPDATE
Hi again Richard & Kevin

I now have
Players Table (see above)

Training session Table:-
SessionID (autonumber)
Date (shortdate)
Coach (Text)
Warmup Type (Text)
Warmup Time (Short Time)
Session Type (Text)
Session Time (Short time)
PlayerID1 yes/no (Caption = Joe Soap)
PlayerID2 yes/no (Caption = Bert Smith)
PlayerID3 yes/no (Caption = Fred Bloggs)
(There are 25 to be entered, but I thought it best just to do 3 for now ,until it all works ok.

The entry form for the Training session table was simple enough to create now, and as I wanted, we have a checkbox for the yes/no on each of the 3 players entered. I've entered 3 sample records and the table seems to be updated correctly.

Unfortunately getting the query right isn't so simple, The Date range parameter query works fine, but instead of showing all 25 players, I want the parameter query to show a combo box where one player is selected and the result only shows the sessions attended by that player. Is this possible, or do I have to do a seperate query for each player?

Thanks Again,

Christine.

 

[willir]

Christine

I have been trying to post the following for the past couple of days, but was unsuccessful (Blaster worm impacting servers, firewalss and internet kind of thing). You have dealt with the most of the fields. The only thing to focus on is the way players are handled.
....

Christine.

I think this work out just fine Christine.

I believe Kevin was talking about a many-to-many relationship when he was discussing an intermediate table.

I suspect you do not need 25 combo boxes. Rather, have one combo box linked to the player table.

So here is the [\b] question we need to answer, before proceding...

Can a session have more than one player? (Your table design suggests yes)
and..
Can a player have more than session? (You already answered this one - yes)

If a session can have more than one player, then you have a many-to-many relationship, and you need the intermediate / relation / join / profile table.

tblSessionProfile [\b]
SessionID (long interger, foreign key, links to SessionID)
PlayerID (long interger, foreign key, links to PlayerID)
+ any session x palyer info you want to track
The primary key is SessionID + PlayerID

tblSession [\b]
SessionID (Primary key, autonumber)
SDate (date/time, medium date format, or other date format)
STime (date/time, medium time format, or other time format)
CoachID (oh, oh, looks like another table, long interger)
or
CoachName (see discussion)
WarmType (text, 4 to 15)
WarmTime (see discussion)
SessionType (text, 4 to 15)
SessionTime (see discussion)

Note: No player id

Discussion on coach: In theory, you should have a table for coaches. If you have just a very few coaches, you may get away with leaving their name in the session table and then have a combo box point to the same field to prevent data entry errors. Use the "Select distinct ..." instead of "distinct row". I think you would be better off in the long run with a table for coaches.

Discussion on Warmup and Session time: You already decided on this.


Richard

 

[Chris121]

Ok Richard,
Thanks again and have a star for your perceverence with me!- and not forgetting Kevin, one for him too.

I've changed my tables now to reflect your advice. Now I'm back to my dilemma with the entry form. This is completed on a paper form by the coach. (There are only 6 coaches and my form has a combo box with each name in it) It's a mirror of the Session table with all players listed and the coach fills it in with the session details then puts a tick by each player involved. The paper form is then passed to the Physiotherapist (who doubles as the IT guy!) for entry into the database. With one combo box listing all players in the players table, surely you can only select one player? What then? Create a new record for the next player - and so on until all players who attended are covered?
Still a bit confused
Christine.

 

[GoDawgs]

This is where the intermediate table comes in...that table will only store the session id and the player id...so it will look something like this:

session player
1 2
1 8
1 9
1 11
2 4
2 5

so basically there will be a record for every person in a session.

Now to the form...you need to create a subform on your main form. This subform will have the intermediate table as the recordsource and be linked to the main table by the session id (so every record created in the subform will store the session id that the main form is on). You need to create your combo box in the subform...so you are right, you can only select one player for each combo box, but this way you'll end up with a list of players...the subform will look like the intermediate table above except the combo box will substitute the actual player's name in for the player id. Hope that makes sense...

Kevin

 

[willir]

Christine

If you use the tick box approach, you may run into two problems down the road....
- The number of tick boxes are going to grow and grow as new players come on board. What do you do with the old palyers? If you substitute a new player for an exiting player, then the sessions for the exiting player will be assumed by new player. And in the future, how will you delete old data? You will be making changes to your database / table design to accomodate a normal process.

- Creating the reports and designing the SQL statements can get real rough.

In short, yes, you do create on "transaction" for each player who attends one session. This shows up in the "join" table as so neatly depicted by Kevin. However, now you will not have to change designs to accomodate transition. And you delete historical data with minimal impact.

On to the form design...
Kevin has explained this. For data entry, you will want to use the session table as the main table. This should be fairly easy using the Forms wizard. After the wizard has created the intial form, get creative -- add a title. But keep in mind where and how you want to display the players.

It boils down to real estate; some considerations...
- Most common approach is to have the main form on top and the subform at the bottom.
- Consider keeping the session information on the left side of teh form and the playn to display the player or players on the left. Why? You will probably be using a continuous form for the player subform and Access more easily displays a single column(s) of data than a double column type of thing.
- Maybe a tab form may work. Have the basic session info on top, additional session on the first tab and the players listed on the second tab.

Subform - I suspect you will less choices here. As indicated by Kevin, the subform will use tblSessionProfile.
- Probably want to make the form continuous.
- If linking to the session table, make the sessionID field invisible, and shrink it.
- The playerID field will best work with a combo box pointing to the player table.

Hint: Instead of creating a combo box, I prefer to do some "pre-work" in the table design. In the table design mode, select the player IF field. Near the bottom of the design screen, click on the Lookup tab. Change the display control from text box to combo box. For row source point to the player table. When in the row source field, click on the "..." button to run the query design wizard. Select player name for the 1st column, sort ascending, select PlayerID for the second column. (or if you are using last and first name, put the PlayerID in the 3rd column). For bound column and column count, change the default from "1" to "2". ... Now when ever you use the PlayerID from from this table, Access will create a combo box for you! Note: You have to do this before you create your subform.

Back to the subform...
Tighten up the form as required and save the form. Open the main form, the session form in design mode - put this window in the back ground. Now click on the tblSessionProfile object in the form list, and drag the object into the main form. This will insert the subform into the main form. You will have to tidy up a bit - placement, sizing, etc.

One last step...
You need to link the subform and main form using the session ID. (Remember, the session ID is hidden on the subform, but it is still there) Bring up the properties for the subform. If the properties window is not open, right click on the subform and seelction properties. go to the data tab. Since you have already created your relationships, Access should depict the parent and child linkage using the SessionID field.

Now, when the Physiotherapist / IT guy needs to create a session, here will need to create the session and save it. (oh, oh, you will need a save button on the form), he can then enter the player info in the subform. Since this will use a combo box, the name will appear after typing a few letters of the player's name.


You are done!


But then you can always add a bit more...

Go at the data from the player to session. Create the main form using the player table. Create the subform using tblSessionProfile (or you can copy), but this time, make the player ID invisible and make the session ID visible.


Food for thought...
- Are you going to use one field for the player's name or have one field for the last name and a second for the first name (or vice versa)? If so, you will need to accomodate this in the lookup combo box and the subform. Note - You probably need two combo boxes - one for the last name, one for first name. You can "hide" a column by using the column width for a field set to zero.

- How are you going to reference the session? You have asession ID, but this may be less than friendly after the first 100 sessions or so... You can create a session number, or using the coach + date, etc.

You are smoking now!
Richard

 

[Chris121]

I've seen the light!!
Thanks loads you guys, I'm gonna get stuck in to this now. I may be back though!
Christine.

 

[Chris121]

Houston - we have a problem!!

I thought I'd followed the advice given to me thouroughly, but I guess I must be missing something.

I did the "pre-work" in the table design and created the player combo box there - all appeared well and good.

I created the form and inserted the subform without a hitch.

Next methinks, I'll use the form to enter some sample data,so I can get the query done.

The session data went in perfectly, but when I came to selecting a player from the combo box (which displayed all the players as it should) I got an error message about the wrong data type? It looks like the combo box is trying to store the player.Firstname.Lastname in the Join Table instead of Just the playerID number.
I went back to look at the combo box in the Join table where the "pre-work" was done. Selecting a player there gives the same error.
I tried undoing the pre-work and made thecombo box just on the subform - same result.

Guys!! what did I do wrong?

Christine :-(

 

[GoDawgs]

Shouldn't be too tough...the combo box has a few properties, one of which is "Bound Column"...basically that is just what column is actually going to be stored, no matter what you are actually showing. The bound column starts with 0 and goes up...so if in your query design view for the combo box the Player ID field is the 3rd field from the left, make the bound column =2...that way it will store that ID instead of the name. Make sense? Play around with it, you'll get it.

Kevin

 

[willir]

GoDawgs

Kevin is correct. The column using to display the primary / foreign key, in this case, PlayerID. is the bound column.

You can fix this problem on the combo box for the form. And you can correct the entry in the table lookup combo box (although now that the combo box for the form has been created, the change in the table will not be reflected in the form.)

Richard

 

[Chris121]

Well... I've been experimenting with this for hours now. With the Bound Column set to 2, it keeps giving me the "data type" error like I mention above.

Strange as it may seem, with the bound column set to 3, it works as it should, the problem now being that when selecting a player, two columns drop down showing firstname:lastname (as it should) Upon making a selection however, only the firstname stays in the field. It IS storing the Player ID number though, as my working query proves.

Here is a close look at the PlayerID lookup info from the Join Table:-
Display Control - Combo box
Row Source Type - Table/Query
Row Source - SELECT Players.FirstName, Players.LastName, Players.EmployeeID FROM Players ORDER BY Players.LastName;
Bound Column - 3
Column Count - 2
Column Heads - Yes
Column Widths - Empty
List Rows - 25
List Width - Auto
Limit to List - Yes

A possibility does occur to me - I am using Access 2002 sp2. Have MS in their wisdom, decided to chane the Bound column count to begin with 1 as opposed to 0?

If so, it would give us a reasom for why it works with Bound Column set to 3.

It doesnt help with problem #2 though - why does the field only diplay the firstname field when the combo box drop-down display both firstname and lastname.

Any Ideas?

Christine.

 

[willir]

Christine

The data type error when binding column two was caused by the fact that column 2, Players.LastName, is a text field, and the foreign key will have been defined as a long interger. You may wish to set th ecolumn count from 2 to 3.

As per seeing only the first name in the field...
Create a second comb box (hint: copy the orignal). We will use the one of "Fromat" properties to control which field is displayed...

Your SQL statement for the row source is...
SELECT Players.FirstName, Players.LastName, Players.EmployeeID FROM Players ORDER BY Players.LastName

In the properties view, for the "Format" tab, there are three columns to understand.

Column count - should be three
Column widths - discussed later
List width - controls width of drop down, can leave as "auto" or set to a size you want.

The "Column widths" property is a very nice feature. By setting the column width, you make the column invisible.

To see the first column only, use...
1";0";0"
I assume 1" for the name will work out okay.

To see the second column only, use...
0";1";0"

I think this addresses your second issue.


Moving on...

You have several options in how you may wish to setup your form.

- You can keep the second combo box visible with but prevent users from entering it. On the Data tab property, you can set the "enabled" property to "No". To prevent this field from being changed, set the "Locked" property to "Yes".

- You can change the order of the second combo box by tweaking the order of the columns in the query -- the system needs the PlayerID but is not concerned by "information" fields.

Richard

Richard