CODEWITHSUNDEEP
sqldatabasems-access
user3186940
user3186940

Reputation: 9

How do I increase a counter by 1 for each repeated value?

As part of my course in university I have to make a database in Microsoft Access which is somewhat limiting me on what I'm trying to do. I have a table that has the information of whether a player in a team was present for a fixture or not using the values "P", "R", and "M" (Played, Reserves, Missed). I want to make a query that counts a value of 1 for each value of P or R and a separate one for M, so that when I make a report that prints off a membership card, it shows the amount of fixtures they've played in and the amount of fixtures that they have missed.

Sorry if this isn't clear, I'll try to explain myself further if you ask but I'm not very good with this stuff. Thank you.

Edit: I'll use screenshot links if that's okay, here is the Fixture Attendance entity that shows if a member of a team attended a game or not. I'm making a membership card based off this one. I want to be able to display the No. of fixtures played by the member and the No. of fixtures missed based off the values in the above entity and use that information in a form I'm going to create. That will be a subform inside my Membership Card form.

I'm presumably really bad at explaining this - I understand Access is rarely used in the real world so I'm not sure why I'm doing this in the first place and don't feel like I'm getting any real knowledge of working with databases.

Upvotes: 0

Views: 85

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I am guessing that you want something like this:

select playerid, sum(iif(fixture in ("P", "R"), 1, 0)) as NumPR,
       sum(iif(figure = "M", 1, 0)as NumM
from table t
group by playerid;

The key idea here is putting the conditional part (iif()) inside the sum().

Upvotes: 1

wvdz
wvdz

Reputation: 16641

CASE WHEN can be used to translate the codes into 1's and 0's. Then use SUM with a GROUP BY to sum them.

SELECT player_id, SUM(Played), SUM(Reserve), SUM(Missed)
FROM
(SELECT player_id,
  CASE WHEN present = 'P' THEN 1 ELSE 0 AS Played,
  CASE WHEN present = 'R' THEN 1 ELSE 0 AS Reserve,
  CASE WHEN present = 'M' THEN 1 ELSE 0 AS Missed
FROM fixtures)
GROUP BY player_id;

Upvotes: 0

radesix
radesix

Reputation: 6262

You should use the COUNT function.

http://office.microsoft.com/en-us/access-help/count-data-by-using-a-query-HA010096311.aspx

Upvotes: 1

Related Questions