Table design question - somewhat OT

[MikeB]

I created a small program to track the kids in my Elementary School
Chess Club.

It was not too hard, I had a table of Games and a table of Players.
Now I"m facing a new wrinkle and I don't know how to accommodate this.
Perhaps the assembled wisdom here can give me some insight.

We need to split the kids into several groups. There is a small group
that needs very basic training on how to move chess pieces, etc.Then
there is a larger group that needs to be taught stuff about opening,
strategy, combinations and end-games. There are too many of them to
teach it to all at once, so we thought to take them in rotation. Those
not selected for "advanced" (and I use the term very loosely)
instruction will be playing games. I need to make sure that all the
kids are rotated through the advanced instruction, so I need a field
somewhere in my database that I can use as a counter to tell me how
many times each kid has received advanced instruction. I would also
like to track the kids that are receiving basic instruction in some
form so I can give them credit for that and eventually advance them
into the more advanced group.

How can I structure my tables, or add a new table to track the
activity of each player on each club day? Is this a new table with a
field for each player for each club day? Sounds cumbersome. Add some
counter field to the Players Table? Doesn't sound too right either.

I'm stuck.

 

[vbasean]

Assuming your student table has an Id StudentID
create another table
-Event
fields:
-StudentID (related to the student table)
-EventLevel
-Date

you now have a sub related table to students
the following function to a query or a form textbox, etcetera
DCound("[EventLevel]", "[Event]", "[EventLevel] = 'Advanced' AND [StudentID]
= " & [StudentID])

this will show a total per student of 'Advanced' attendence
if you change the 'Advanced' to another criteria you keep track of each level

you can create a crosstab query that counts each level (Keeping your levels
consistent in data entry)
1) create a query from your student's table and your event table with the
student name from 'Students' and the date and level from 'Events' called
'qryStudentEvents' save it.
2) create a crosstab query like below
TRANSFORM Count(qryStudentEvents.Date) AS CountOfDate
SELECT qryStudentEvents.StudentName, Count(qryStudentEvents.Date) AS [Total
Of Date]
FROM qryStudentEvents
GROUP BY qryStudentEvents.StudentName
PIVOT qryStudentEvents.EventLevel;

 

[Tmarkos]

I would set up an "event" table that tracks for that day what level of
activity and how many hours it took place for each child. Then you could
group the records by both the child and activity and add the hours.

 

[vbasean]

I missed the 'Group' part (thanks Steve)
you could add a field to the 'Student' Table named 'Group'

add to your 'qryStudentEvents' the 'Group' Field

change your crosstab query to
TRANSFORM Count(qryStudentEvents.Date) AS CountOfDate
SELECT qryStudentEvents.Group, qryStudentEvents.StudentName,
Count(qryStudentEvents.Date) AS [Total Of Date]
FROM qryStudentEvents
GROUP BY qryStudentEvents.Group, qryStudentEvents.StudentName
PIVOT qryStudentEvents.EventLevel;
now you're grouped by 'Group' and you have a count by 'Student Name' of each
Level
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Assuming your student table has an Id StudentID
create another table
-Event
fields:
-StudentID (related to the student table)
-EventLevel
-Date

you now have a sub related table to students
the following function to a query or a form textbox, etcetera
DCound("[EventLevel]", "[Event]", "[EventLevel] = 'Advanced' AND [StudentID]
= " & [StudentID])

this will show a total per student of 'Advanced' attendence
if you change the 'Advanced' to another criteria you keep track of each level

you can create a crosstab query that counts each level (Keeping your levels
consistent in data entry)
1) create a query from your student's table and your event table with the
student name from 'Students' and the date and level from 'Events' called
'qryStudentEvents' save it.
2) create a crosstab query like below
TRANSFORM Count(qryStudentEvents.Date) AS CountOfDate
SELECT qryStudentEvents.StudentName, Count(qryStudentEvents.Date) AS [Total
Of Date]
FROM qryStudentEvents
GROUP BY qryStudentEvents.StudentName
PIVOT qryStudentEvents.EventLevel;
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I created a small program to track the kids in my Elementary School
Chess Club.

It was not too hard, I had a table of Games and a table of Players.
Now I"m facing a new wrinkle and I don't know how to accommodate this.
Perhaps the assembled wisdom here can give me some insight.

We need to split the kids into several groups. There is a small group
that needs very basic training on how to move chess pieces, etc.Then
there is a larger group that needs to be taught stuff about opening,
strategy, combinations and end-games. There are too many of them to
teach it to all at once, so we thought to take them in rotation. Those
not selected for "advanced" (and I use the term very loosely)
instruction will be playing games. I need to make sure that all the
kids are rotated through the advanced instruction, so I need a field
somewhere in my database that I can use as a counter to tell me how
many times each kid has received advanced instruction. I would also
like to track the kids that are receiving basic instruction in some
form so I can give them credit for that and eventually advance them
into the more advanced group.

How can I structure my tables, or add a new table to track the
activity of each player on each club day? Is this a new table with a
field for each player for each club day? Sounds cumbersome. Add some
counter field to the Players Table? Doesn't sound too right either.

I'm stuck.

 

[Ken Sheridan]

Think of it in terms of the entity types involved. Firstly you have two
categories of instruction, Basic and Advanced, so start with a table for
this, InstructionCategories say. I know two rows for a table sounds rather
unnecessary, but these are data, and it’s a fundamental principle of the
database relational model that data are stored as values at column positions
in rows in tables and in no other way.

The question now arises as to whether you have different areas of
instruction within each category. If so then you should have a table fore
this, InstructionAreas say (you can use whatever names you like of course,
but it helps if they are descriptive of the real world entity types). This
will have a column InstructionArea and a (foreign key) column
InstructionCategory. You may wonder why the InstructionCategories table is
needed if the values are also in InstructionAreas. The answer is that it
enables you to protect the integrity of the data by enforcing referential
integrity between InstructionCategories and InstructionAreas, which prevents
invalid InstructionCategory values being inserted into InstructionAreas.

You now just need a table to model the many-to-many relationship between
Players and InstructionAreas, PlayerInstruction say. This will have two
foreign key columns, PlayerID and InstructionArea along with other columns to
represent the attributes of each player/instruction area activity, e.g. the
date and any sort of assessment values you might be using.

So to see how many advanced instruction sessions each player has undertaken
for instance you'd join three of the tables like so:

SELECT Players.PlayerID, FirstName, LastName,
COUNT(*) AS AdvancedInstructionCount
FROM Players, PlayerInstruction, InstructionAreas
WHERE Players.PlayerID = PlayerInstruction.PlayerID
AND PlayerInstruction.InstructionArea = InstructionAreas.InstructionArea
AND InstructionCategory = "Advanced"
GROUP BY Players.PlayerID, FirstName, LastName;

You can detail each player's instruction, both basic and advanced, by a
similar query, but this time not grouped:

SELECT Players.PlayerID, FirstName, LastName, InstructionDate
InstructionAreas.InstructionArea, InstructionCategory
FROM Players, PlayerInstruction, InstructionAreas
WHERE Players.PlayerID = PlayerInstruction.PlayerID
AND PlayerInstruction.InstructionArea = InstructionAreas.InstructionArea;

As you can presumably already return those players who are playing games
along with the date of the game, you can put both queries together in a UNION
query to combine the two. The first four columns from the above would tally
with those from a 'games' query but not the last two, so you'd need to
introduce Nulls as constant columns in each query to handle this. In a
simplified form it would be done like this:

SELECT a, b, c, NULL AS x, NULL AS y, h, j
FROM A
UNION
SELECT a, b, c, d, e, NULL, NULL
FROM B;

The a, b and c columns tally in each. The NULLs in the first half cover the
d and e column in the second half and those in the second half cover h and j
columns in the first half.

By ordering the UNION query by date you'll get the games rows and
instruction rows per date together.

Finally, do you need to be able to return a date where no players were
either playing games or receiving instruction? If so you need to introduce a
ClubDates table of all club dates. This can then be LEFT OUTER JOINed to the
result table of the UNION query so as to return a row with the date and Nulls
in all other columns for those dates where no players were either playing
games or receiving instruction.

Ken Sheridan
Stafford, England