Count Records

[DBFIN]

We bill each customer four equal installment invoices. The installment data is stored in a table (please see file attachment). I need to assign an installment sequence number for each invoice in the table. Each customer would have installment sequence number of 1,2,3,4 without exception.

How can I assign the installment sequence number for each customer using a standard expression or function. I won't be using any code to accomplish this. I've tried DCount but can't get it to work.

 

[DBFIN]

Help !!!!!!!!!!!!!

 

[ajetrumpet]

in all honesty, you're question is not clear. And secondly, yelping for help here will not get you very far.

I am seriously just being honest...how about a little more information on this so we can give you some help?

 

[raskew]

Hi -

Here's a neat little function that assigns rank (sequence). You can see more at Source.

Option Compare Database
Option Explicit

Dim WfID As Variant
Dim WRank As Long

Function GetRank(fID) As Long
'source: http://www.access-programmers.co.uk/forums/showthread.php?t=94070
'author: mStef

  If fID = WfID Then
    WRank = WRank + 1
    GetRank = WRank
  Else
    WRank = 1
    GetRank = 1
    WfID = fID
  End If
  
End Function

I created tblInstallment with fields:
PolicyID - Text
InstallID - Number
PymtAmt - Currency

...and filled it per your attachment.

Used this query to rank the InstallId

SELECT tblInstallment.PolicyID, tblInstallment.InstallID, getrank([PolicyID]) AS rank
FROM tblInstallment
ORDER BY tblInstallment.PolicyID, tblInstallment.InstallID;

Output:

PolicyID	InstallID	rank
DSU 000531892	1000	1
DSU 000531892	1001	2
DSU 000531892	1002	3
DSU 000531892	1003	4
DSU 000647981	685	1
DSU 000647981	686	2
DSU 000647981	687	3
DSU 000647981	688	4

HTH - Bob

 

[The_Doc_Man]

This exact question was answered in another post (different post) a couple of days ago.

I smell either an impatient questioner or a new school project.

 

[Rabbie]

This exact question was answered in another post (different post) a couple of days ago.

I smell either an impatient questioner or a new school project.

Didn't you know Doc. School projects nowadays are designed to see if students can find the answer on the internet.

 

[raskew]

Or, could be one of those:

"Immediate help needed, I need to resolve this by midnight tonight or by job is in jeopardy? Please help me!"

Bob

 

[DBFIN]

Thanks everyone for your expert advise. It has been greatly appreciated.

 

[DBFIN]

FYI, my job is not in jeapordy. Quite the opposite.

 

[Rabbie]

Or, could be one of those:

"Immediate help needed, I need to resolve this by midnight tonight or by job is in jeopardy? Please help me!"

Bob

In that case, Bob, the wise poster will say so. In my experience telepathy is not an effective means of communication. The fact there had been a similar post the previous day also raises suspicions of a class assignment