Scoring Sheets!?

[screechyd]

I am in charge of a Dodgeball League for more than 150 kids. I have all the
information put into tables in access 2003.

The Tables are organized by division (3rd & 4th Grade, 5th & 6th Grade,
etc...) so that on one table are all the 3-4 grade players. I have 4 total
tables for 4 divisions.

Each column on the tables are: Last Name, First Name, Team

For each game every week, I have to print out Scoring Sheets. As i have it
right now, I have the scoring sheet created using Tables in Word 2003, and I
have to Type in all the names and team names. The scoring sheet consists of
the two team names with area to write in team scores, and then another table grid listing each teams' players and space to write in how many outs each player makes each game.

This process is very long, as there are 8 games with 16 teams playing every week.

My question is this: Is there a way to have Access fill in all the names and
team names on a scoring sheet? I'd like to have it so I just have to type in
which teams are playing each other, and have it print out the scoring sheet,
completely filled in.

Do I have to design some type of 'report' in access? or can I somehow import
the Access data into the Word document (with the data going into the Word
Table grids)?

Please Help me!

 

[statsman]

It would be easiest to create a report in Access.

Create a query which contains first name, last name and team from your table. In the criteria of the team field enter the following:

[Enter Team:]

The query will prompt you to enter a team. Run your scoresheet report from the query.
Use the line or box function from the toolbox to create the areas on the report where the stats will be written in.

 

[screechyd]

Thanks for the reply Statsman...

A few things further, though

Doing what you suggested gets me a list of one teams' players. Each score sheet needs to have both teams on it, with the list of players

Let me try to concoct one on here...

Team 1 Team 2
game 1 score game 1 score
game 2 score game 2 score
game 3 score game 3 score


Team 1 Team 2
john A (table grid for scores) Bob B (table grid for scores)
Joe B (table grid for scores) Bill G (table grid for scores)
Jill C (table grid for scores) Blake H (table grid for scores)




In each place it says "Team 1" and "Team 2", I need the team name to be written, and for each of the Names, I need them to be the player names. Where the scores are should be a table grid where the Refs can write in the scores as the games finish.

Perhaps this can give you a better idea of what I am looking for.

Please and thank you for the help!

 

[liddlem]

Hi Skreechyd
Just some other thoughts here.

In the "Players" table, I would include 2 fields. - Grade and Division.
This is so that when players move to the next grade/division (At the end of the school year) you should be able to run an update query which
A. Promotes the players to the next grade and
B. Promotes only top grade (from the previous year) to the next division.


As for displaying the scoring grids...
I presume that you have a couple of other tables that store
1. GameID / GameDate / TeamID (A) / TeamID (B) / AScore / BScore
2. PlayerID / GameID / Score / TeamID (maybe?)

In order to create the grids each week, I would first match the teams against one another. (on a form that is based on Table 1 above)

Then (using a crosstab query that joins games and players on the GameID) you should be able to generate both the report and the data input form accordingly.

Hope this helps.

 

[screechyd]

"In the "Players" table, I would include 2 fields. - Grade and Division.
This is so that when players move to the next grade/division (At the end of the school year) you should be able to run an update query which
A. Promotes the players to the next grade and
B. Promotes only top grade (from the previous year) to the next division."


Great Idea! I would have never thought of that! I'll have to look into that one!


"
As for displaying the scoring grids...
I presume that you have a couple of other tables that store
1. GameID / GameDate / TeamID (A) / TeamID (B) / AScore / BScore
2. PlayerID / GameID / Score / TeamID (maybe?)

In order to create the grids each week, I would first match the teams against one another. (on a form that is based on Table 1 above)

Then (using a crosstab query that joins games and players on the GameID) you should be able to generate both the report and the data input form accordingly.
"


That seems a bit mroe in depth than I have knowledge... I am a 'noob' to Access...

Is there some references that can explain to me how to do that?

 

[liddlem]

Hmmm- Ok - Lets see.
Im making an assumption that your problem is with the crosstab query?

Do you know how to create a pivot table in Excel?

 

[screechyd]

"
Hmmm- Ok - Lets see.
Im making an assumption that your problem is with the crosstab query?

Do you know how to create a pivot table in Excel?
"

I know how to make it... Not really sure how it is used...

 

[liddlem]

Well - A crosstab query works the same way.

Lets give these tables some names.
TblFixtures = GameID / GameDate / TeamID (A) / TeamID (B) / AScore / BScore
TblPlayerResult = PlayerID / GameID / Score / TeamID (maybe?)

So first make a normal select query to join TblFixtures to TblPlayerResult (Join them on the GameID field.
Put all fields into the query.
Lets call that "QryFixPlay" (or whatever you like)

So the QryFixPlay query shows
GameID / GameDate / TeamID (A) / TeamID (B) / AScore / BScore /PlayerID / GameID / Score / TeamID

Now create another query (CrossTab) which sources its data from QryFixPlay.
If I understand you correctly, your Crosstab query should look something like

..............TeamID(A) TeamID (B)
GameID
PlayerID
PlScore

 

[screechyd]

Ok this seems to be getting way too complicated for me...

I figured out a way to get the two lists by typing in the two team names...
Make a query for Team 1, and another query for Team 2 (both listing the players names

Then put a two list boxes in a report, where each are directed to the two queries above...

that gets me a list of the two teams' players and i can use the Lines in the toolbox to create the table grids...

But now my question is this...

How do i get the Team Name inserted into the same report in two different places (each team name will be on the score sheet in two places)

something like this:

Bulldawgs Ligers
info info

Bulldawgs Ligers
[listbox from above] [listbox from above]



Is there an easy way to do this? or it going to get way too complicated? I dont really have the time to create more tables and such, so i kinda gotta use what i have... (which is one table for each division, with all the information - names, teams, grades, etc...)

 

[liddlem]

OK - Thats sound like its working.

The problem here is that a report (or Form etc) is based on a SINGLE underlying Table / Query.
What you may need to do is create a 3rd query that connects Qry1 and Qry2 (all Fields)

Then make the Report based on Qry3 and put in the fields


[HEADER SECTION]
............ Qry1.Team Qry2.Team {Group by GameID ?}
[BODY SECTION]
.............Qry1.Player Qry2.Player
[FOOTER SECTION]
Date / Time etc


Can you see this working?

 

[screechyd]

sorry... I'm not really following you with the 3rd query idea

(please forgive me, I'm learning )

 

[liddlem]

Dont worry - Im also on the learning curve.

As I understand it, a report MUST source its data from ONE table or Query.
In other words, In order to get the report to distinguish between the TeamA and TeamB players, you need to join them using a 3rd query.

 

[screechyd]

Well i got it to give me two different lists using two different queries...

the two list boxes were each pointing to a different query (though the two queries contained the same data - the names and teams they are on)

So when i would open the report, It would first ask me "Team Number 1:" Then after i type in the first team, it would ask me "Team Number 2:" then i would get the two listboxes filled with the two different player lists of each team 1 &2


All i need is to have the Team Name put into the report - as a label to the list of player names...

 

[liddlem]

And so .....I learn some more!
Thank you!

What happens if you drag the Team names into the header section of the report?