Access query help

T

Treeny

New Member
Joined
Sep 23, 2003
Messages
39
Hi,

I have a table containing two columns of data as follows:

player1 walks
player1 runs
player1 sprints
player1 sprints
player1 jogs
player2 jogs
player2 runs
player2 walks
player2 runs

etc..............

What I need is query that will total up the number of actions for each player. For example from the above data I would like the query to look something like this:

walks runs sprints jogs
player1 1 1 2 1
player2 1 2 0 1

Can someone explain how to create a query that will do this for me.

Thanks in advance
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
What about a Totals query? The SQL would look something like this

SELECT Table1.Player, Table1.Activity, Count(Table1.Activity) AS CountOfActivity
FROM Table1
GROUP BY Table1.Player, Table1.Activity;

and the output like this

<HTML><BODY><TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION>Query1</CAPTION><THEAD><TR><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Player</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Activity</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>CountOfActivity</FONT></TH></TR></THEAD><TBODY><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>player1</FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>jogs</FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000>1</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>player1</FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>runs</FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000>1</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>player1</FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>sprints</FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000>2</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>player1</FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>walks</FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000>1</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>player2</FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>jogs</FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000>1</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>player2</FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>runs</FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000>2</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>player2</FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>walks</FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000>1</FONT></TD></TR></TBODY></TABLE></BODY></HTML>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Linked Excel table in Access - field values change as I navigate?
Replies
5
Views
485
welshgasman
welshgasman
K
  • Question
Access - How do you change a shared drive path name to not include individual user so others can run the queries?
Replies
2
Views
400
welshgasman
welshgasman
J
  • Question
Access query not updating excel pivot properly
Replies
1
Views
622
Micron
M
tourless
  • Question
Pain in the Access
Replies
6
Views
841
alansidman
A
JenniferMurphy
  • Question
How to structure a variable product rating database
Replies
5
Views
833
mdmilner
M

Forum statistics

Threads
1,221,827
Messages
6,162,200
Members
451,753
Latest member
freddocp

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top