How to count transactions per employee (debit and credit entry as one)

Pawelski_33

Forums

The attached is the mock up database I created to support my query.
What i want to achieve is to count the number of transactions (field JOURNAL_NO) inputted by each employee (column POSTED_BY). So the transaction is counted as 1 if there are 2 identical records for fields:
-POSTED_BY (employee)
-JOURNAL_NO (transaction 1,2,3, etc. where 1 transaction has the same number for debit and credit entry)
-JOURNAL_TYPE
-TIME (each transaction entry for debit and credit will have the same time).
1 transaction is one entry for debit and 1 for credit -  tried to sort it out with the duplicate key function but it doesnt exactly shows what i wanted to see.
************************************************************************************************
I also wanted to show sum of debit/credit per each employee but I think i sorted it out with the following code:
Dim TargetDb As Object
        Dim Arr_FieldsToSummarize() As String
Dim Arr_FieldsToSummarizeTot() As String
Dim FieldToSumm As Integer
Dim FieldToTot As Integer
 
                'Defining Database to be used in this procedure
                Set TargetDb = MasterDb
                
'Fields to summarize - Preparer ID
FieldToSumm = 1
ReDim Arr_FieldsToSummarize(FieldToSumm)
Arr_FieldsToSummarize(0) = UserChoice(5)
 
'Fields to summarize to total - DR and CR
                FieldToTot = 2
                ReDim Arr_FieldsToSummarizeTot(FieldToTot)
                Arr_FieldsToSummarizeTot(0) = UserChoice(2)
                Arr_FieldsToSummarizeTot(1) = UserChoice(3)
 
                Call PerformSummarize_and_Export(TargetDb ,"Preparer_Summary", Arr_FieldsToSummarize(),Arr_FieldsToSummarizeTot(),FieldToSumm,FieldToTot)
 
                'Closing open database - Preparer_Summary
                ActiveDatabase = Client.CurrentDatabase.Name
                Client.CloseDatabase  ActiveDatabase
 
  , where UserChoice is the field name specified by the user through the dialog box i.e. UserChoice(5) is POSTED_BY field and UserCHoice(2) is Debit column and so on
 

Brian Element Fri, 07/27/2018 - 08:09

Hi Pawelski,

Sorry for not getting back to you on this one sooner.  For the first part could you not do it as two summaries.  The first summary would be by Posted By, Journal No, Journal Type and Time.  You could probably summarize the transaction amount to see if there are any out of balance as all the transactions I would think would be 0.

Then you do a second summary only by Posted By, this will give you the number for each employee of journals entries that they have created in your database.

I think this is what you are looking for, let me know if I misunderstood.

Thanks

Brian