This is killing me I know there is 2

[sccofer]

This is killing me I know there is an elegant solution but I can't
figure it out. I have a simple query which gives me the following
results...

File Cost Center
1 2x2
1 3ee
1 1xc
2 1qw
2 2x2
3 1a1

I am trying to add a thrid column that will give me a sequential count of the number of cost centers associated with each unique file number. For the above example the result would be...

File Cost Center Result
1 2x2 1
1 3ee 2
1 1xc 3
2 1qw 1
2 2x2 2
3 1a1 1

I can get a total count for each file using DCOUNT but I am finding it is impossible to get a sequential count. I have been through the newsgroups and can't find a simple solution. This can't be that hard can it??? Any help anyone could offer would be most appreciated.
Thanks in advance.

Scott

 

[funkmonsteruk]

Hmm, tricky one... It's easy to do at report level - just check out the runningsum function in help - but query level is another matter altogether...

 

[CosmoKramer]

This should be simple.

Add a group header to your report for the File field. Set this header's visible property to No.

Add a text box to the detail section to display the running count. Set its control source to =1 and its Running Sum property to Over Group. This value will reset for each group.

That should do it.....

 

[dodge20]

see if this thread helps you any

thread701-552344

Dodge20

 

[PauloRico]

Paste this code into a new Module -

Global glngFileNo as Long
Global glngSequence as Long

Public Function GetSequence(FileNo as Long) as Long

If glngFileNo = FileNo Then
glngSequence = glngSequence + 1
Else
glngSequence = 1
End If

GetSequence = glngSequence

End Function

Then, for the Sequence column in your query, put -
Sequence: GetSequence([FileNo])

 

[PauloRico]

Error in last one.

After the else statement, you need to set the global file number to the current one, thus -

If glngFileNo = FileNo Then
glngSequence = glngSequence + 1
Else
glngSequence = 1
glngFileNo = FileNo
End If

Sorry about that.