is there countif in access !

[ahmed kamal]

hello guys,
i wonder is there countif in access query like excel !
i have a table that contains names like
[TABLE="class: outer_border, width: 131"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: left"]Name[/TD]
[TD="align: left"]Result I want[/TD]
[/TR]
[TR]
[TD="align: left"]Sarah[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: left"]John[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: left"]Mick[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: left"]Sarah[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: left"]Sarah[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: left"]John[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

i need a formula that count like that in result column, is that possible in access

 

[alansidman]

You will need to build a query. Here is a SQL statement for that query. Assumed your table to be named "tblSample"

SELECT tblSample.Name, Sum(tblSample.[Result I want]) AS [SumOfResult I want]
FROM tblSample
GROUP BY tblSample.Name;

 

[ahmed kamal]

I tried that code but the result was like that

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]SumOfResult I want[/TD]
[/TR]
[TR]
[TD="align: left"]Sarah[/TD]
[TD="width: 64"]3[/TD]
[/TR]
[TR]
[TD="align: left"]John[/TD]
[TD="width: 64"]2[/TD]
[/TR]
[TR]
[TD="align: left"]Mick[/TD]
[TD="width: 64"]1[/TD]
[/TR]
</tbody>[/TABLE]

but i want query show a result like below, hope you will help me

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: left"]Name[/TD]
[TD="align: left"]Result I want[/TD]
[/TR]
[TR]
[TD="align: left"]Sarah[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: left"]John[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: left"]Mick[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: left"]Sarah[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: left"]Sarah[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: left"]John[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[xenou]

Looks like you want what is called a running total. You can only do that in MSAccess reports.

 

[alansidman]

Running Sums: Look at this link

https://support.microsoft.com/en-us...ing-to-a-field-in-the-previous-or-next-record

 

[alansidman]

Maybe this?

SELECT tblSample.Name, Count(tblSample.Name) AS CountOfName
FROM tblSample
GROUP BY tblSample.Name;

 

[xenou]

SELECT tblSample.Name, Count(tblSample.Name) AS CountOfName
FROM tblSample
GROUP BY tblSample.Name;

That would be the same as Post 3. Except Post 3 should have showed sums, not counts. In other words, it shows a single value (count) for each name, not a running count per row for each name. It is possible to get running sums using the methods posted in the link....but...

the butts:

• you need to have a key with ordering implied (1,2,3...) in order to find the "last record" or "previous record" in a meaningful way and in fact to make the running sum possible. Also requires some understanding of SQL beyond the basics (whereas it seems that hardly anyone bothers to even learn the basics now).
• Code solution is great but better for advanced Access users.

If there is no primary key you could create a temporary table with an autonumber id, and use that to create ordering and uniqueness for each row, as a basis for building a query.