not counting breaks, but numbering them: 1,2,3,4,5,...

[bertenbert via AccessMonster.com]

Hello guys,

Could you please help me with the following.

I have a table where I record all activities performed by an employee. These
activities consist of either work or a break.

The table has 3 columns: name employee, number employee, activity employee

Example

John 455 break
John 455 work
John 455 break
John 455 break
Filip 655 work
Filip 655 work
Filip 655 break
Filip 655 break

The employee names are grouped because I have sorted this column "ascending"

I would to create a query in which the breaks are numbered and in which
numbering starts from "1" each time with a new employee.

Example

John 455 break BREAK 1
John 455 work
John 455 break BREAK 2
John 455 break BREAK 3

Filip 655 work
Filip 655 work
Filip 655 break BREAK 1 (this must be break 1 and not break 4: another
employee means start numbering from 1 again)
Filip 655 break BREAK 2

I really would like to have the breaks numbered. What I don't want is the
following:

John 455 break BREAK 3
John 455 work
John 455 break BREAK 3
John 455 break BREAK 3

Filip 655 work
Filip 655 work
Filip 655 break BREAK 2
Filip 655 break BREAK 2

I really want to number the breaks: 1,2,3,4,5,...

I thank you all very much and I hope my post is not to confusing.

Suggestion of myself:

I could use an IIfstatement: If there's a break the result is "1", if there's
no break the result is "0".

And then I use DSum (running sum)

John 455 break "1" BREAK 1 (="1")
John 455 work "0" ("1"+"0" = "1") result would be "1", but can
solve this myself
John 455 break "1" BREAK 2 ("1"+"1" = "2")
John 455 break "1" BREAK 3 ("2"+"1" = "3")

DSum function would look like this:

DSum("[break]";"name of table";"[autonumber]<="&[autonumber]&"")

And indeed this works.

One more problem: HOW CAN I MAKE THE NUMBERING START FROM 1 AGAIN WITH A NEW
EMPLOYEE.

When activities of Filip come in table, breaks should start from 1.

 

[Allen Browne]

1. Open the report in design view.
Add a text box to the Detail section, and give it these properties:
Control Source: =IIf([activity]='break',1,0)
Running Sum: Over Group

2. Open the Sorting And Grouping box.
Select the EmployeeID (or employee name) field.
Set the Group Header property to Yes.
Access adds a new section to the report.
(You can make it zero-height if you won't want a blank line between
employees.)