Count Records in Table

[ootkhopdi]

HI All

i have a Table named Payments

Which contains payment details to employee in an year

i can get total payments in year by Dcount Function
but i want to get result in this table's Filed "Payment_No"
that will shows no of payments till current payment..
giving more clearly as above

EMPID NAME PAYMENT DATE PAYMENT PAYMENT SL TotalPmnt
A01 A 27-10-2014 4500 A01/1 4500
B01 B 27-10-2015 4500 B01/1 4500
A01 A 30-10-2014 1800 A01/2 6300
A01 A 15-11-2014 2000 A01/3 8300

Please tell me how can solve this prob...

thanks in advance

 

[sneuberg]

I think you can do that with DCount in an query expression. Something like:

No Payments: DCount("*","[Payments]", "[EMPID] = '" & [EMPID] & "' AND [DATE PAYMENT] <= #" & [DATE PAYMENT] & "#")

This will make the query slower so you might consider indexes on the EMPID and DATE PAYMENT if you don't already have them

 

[ootkhopdi]

Count Records

Hi

i have a table containing following fields

S.No. EMPID Item Date Count Item/Empid
1 A01 Apple 08/09/2016 A01/01
2 A01 Banana 10/09/2016 A01/02
3 B01 Apple 10/09/2016 B01/01
4 A01 Apple 10/09/2016 A01/03

in this table i want as count item/ Empid is incresing as item related to Empid A01,,
but i can't do same

how can i do it
if any item add in this table then count item/ empid should changed as record id

please help me..

in simple language or stepwise

thanks in advance

 

[ootkhopdi]

How can i use it

 

[The_Doc_Man]

Re: Count Records

Before we walk into this, a question comes to mind that may make this easier - or harder.

What is the origin of that "S" column that on the left of your preferred output?

In general, you do NOT want to do this to a table. Modifying a table entry because of having other intervening table entries is generally considered a poor design because it invites (or even DEMANDS) a lot more overhead and table visitation.

Doing dynamic counts via a QUERY, on the other hand, is a lot easier, less table churning occurs, and most of the time your forms and reports don't care that it is a query vs. a table.

 

[ootkhopdi]

Re: Count Records

Oh...
Sorry i think i can't explain

Column "S" is not a column i.e. S.No.(Serial Number) a column which having total no of entries..

 

[ootkhopdi]

can i add a column as count record of EmpID

 

[ootkhopdi]

Re: Count Records

can i add column in table or in a query to count total records as EMPID (Count of EMPID) till row

 

[The_Doc_Man]

Re: Count Records

In general, if your column S.No exists in a table, then you can do a query using it. I'm going to use my own field names for the example but you can figure it out I'm sure:

SELECT SERNUM AS "S.No.", EMPID, ITEMNAME AS "Item", EMPID, "/", 1 + DCOUNT( "[SERNUM]", "ITEMTABLE", "[EMPID] = '" & EMPID & "' AND [SERNUM] < " & CStr( [SERNUM] ) ) AS "Count Item/EmpID" FROM ITEMTABLE ORDER BY SERNUM ;

The idea is that the DCount looks at all serial numbers limited to the current EMPID and counts only that subset. As long as SERNUM remains unique, this sort of thing might work.

 

[The_Doc_Man]

Take a look at your other post. My suggested solution to that other post might help here.

 

[pbaldy]

I merged your threads since both had responses and they seemed the same.

 

[static]

Public Function numrecs(tbl As String, Optional where As String) As Long
    numrecs = DBEngine(0)(0).OpenRecordset("select sum(1) from [" & tbl & "] " & IIf(where <> "", " where " & where, ""))(0)
End Function

eg

Debug.Print numrecs("table1")
Debug.Print numrecs("table1", "id between 2 and 5")

 

[ootkhopdi]

Thanks to all ,
i got solution with with sneuberg's solution..

 

[sneuberg]

That solution won't work right if your dates are in dd/mm/yyyy format for reasons explained here. If that's the case I suggest add the follow code to a module

Public Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. [email]allen@allenbrowne.com[/email], June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

and change the expression to:

No Payments: DCount("*","[Payments]", "[EMPID] = '" & [EMPID] & "' AND [DATE PAYMENT] <= " &  SQLDate([DATE PAYMENT])  )

 

[ootkhopdi]

thanks once again

but previous code is working fine with my data