count accross columns

[Purfleet]

Hi

I have a large spreadsheet (1200 rows and 25 columns wide) with all the
games my football team have played, arranged like

Game,Season,Date,Opponents,Competition,Division,Type,Result,Score(us),Score(them)
Venue,Notes,Manager,Goals 1,Goals 2,Goals 3,Goals 4,Goals 5,Goals 6,Goals
7,Goals 8,Goals 9,Goals
10,Goals 11.

In the goals coumns (Goals 1 thru to Goals 11) are the names of the people
who scored i.e Glenn Poole, Adam Parker etc etc.

I need to look up the season column and then count the goals columns (Goals
1 thru to Goals 11) so that the table looks somehting like

Players Glenn Poole Adam Parker
Season
2003-04 5 3
2004-05 2 1

I can create a query that counts the number of time Glenn Poole appears in
Goals 1 but i need creat one that counts all Glenn Poole's in goals 1, goals
2 etc giving me one total

 

[JohnFol]

You say and describe a spreadsheet. Is this in Excel?

 

[Purfleet]

It was created in excel (as the data input was easier), but as i couldnt find
the answer to some questions i have imported it to Access now

 

[Rick Brandt]

It was created in excel (as the data input was easier), but as i
couldnt find the answer to some questions i have imported it to
Access now

The design for a spreadsheet is seldom a good design for a database. Propery
set up (as a database) this data looks like it needs to be spread over several
tables as there should be a table per "entity". So you would have a table
for...

Games
Teams
Players
Venues
etc...

A "wrong and ugly" solution would be to create a UNION query that combines all
of the Goals columns into a single column which would allow you to get your sum.

A "Proper" database for doing sports tracking is actually a fairly complex thing
to set up. If you really wanted to do it right you are looking at a pretty big
job.

 

[SteveS]

Hi

I have a large spreadsheet (1200 rows and 25 columns wide) with all the
games my football team have played, arranged like

Game,Season,Date,Opponents,Competition,Division,Type,Result,Score(us),Score(them)
Venue,Notes,Manager,Goals 1,Goals 2,Goals 3,Goals 4,Goals 5,Goals 6,Goals
7,Goals 8,Goals 9,Goals
10,Goals 11.

In the goals coumns (Goals 1 thru to Goals 11) are the names of the people
who scored i.e Glenn Poole, Adam Parker etc etc.

I need to look up the season column and then count the goals columns (Goals
1 thru to Goals 11) so that the table looks somehting like

Players Glenn Poole Adam Parker
Season
2003-04 5 3
2004-05 2 1

I can create a query that counts the number of time Glenn Poole appears in
Goals 1 but i need creat one that counts all Glenn Poole's in goals 1, goals
2 etc giving me one total

So you know that the design of your table is not the "right" design. But
changing the design would take lots and lots and lots of work. But there may be
a way to get what you want without changing the design. Not the best way, but ...

You could make a query to count the goals for each player, then count the
number of goals, but this would mean you would have to modify the query(s) for
EACH person that scores a goal; which also means that the query has to include
EVERY player that can possibly score a goal.


What you can try is:

(You'll have to change the table and field names to your names)

1) Create a new table, I called it FB_Temp. It has three fields:

NAME TYPE
---- -----
Game NUMBER
Season Text
Player Text

NO primary key.

2) Create a new form ( you could use an existing form). Create a button and
paste in the following code in the ON_Click event:

'******* begin code ********8
Dim strSQL As String
Dim rs As Recordset
Dim rs_temp As Recordset
Dim i As Integer

CurrentDb.Execute "DELETE FB_Temp.Season, FB_Temp.Player, FB_Temp.Game FROM
FB_Temp;"

strSQL = "SELECT Game, Season, Goal1, Goal2, Goal3, Goal4, Goal5, Goal6,
Goal7, Goal8, Goal9, Goal10, Goal11 FROM FOOTBALL;"

Set rs = CurrentDb.OpenRecordset(strSQL)

Set rs_temp = CurrentDb.OpenRecordset("fb_temp")

If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
' loop thru the main table
While Not rs.EOF
' the temp table
With rs_temp
' the nu of goal
For i = 1 To 11
.AddNew
!game = rs!game
!season = rs!season
!Player = rs.Fields("goal" & i)
.Update
Next
End With
rs.MoveNext
Wend
End If
End Sub

'************ end code **********8

3) Create a new query. Change to SQL view. Paste the following:

TRANSFORM Count(FB_Temp.Player) AS CountOfPlayer
SELECT FB_Temp.Season
FROM FB_Temp
GROUP BY FB_Temp.Season
ORDER BY FB_Temp.Season
PIVOT FB_Temp.Player;

Save it as qryCountPlayerGoals

BTW, this is a crosstab query.


-------
Click the button you created in step 2. The code deletes all records in the
temp table, then adds new records from the main table from fields Season,
Player and Game. (I put in Game in case you wanted to get Player goals per game.)

Now open the above query. You should see each season (row heading) and player
name across (column headings) with the number of goals.

EACH time you want to see the player goals, you need to run the code. If you
set the form record source to the crosstab query and have the button in the
form header, it would be easy to click the button and see the results.


HTH