[RESOLVED] serial no in datareport

[Akshaya]

Hi friends,

How to generate automatically serial nos. in datareport.
Suppose if it displays 100 employee's payment report,serial no.should be generated from 1-100.Please help me?

[amrita]

Add a autonumber field to the table and bind that to the Serial Number

[Akshaya]

Thanks.
Is it possible if we merge two tables also?Please explain how to do!

[Akshaya]

But if we want to print report for deptwise,can we use auto serialno?

[ganeshmoorthy]

instead you can consider generating Serial Number at the query result itself...

[leinad31]

How are you setting up the report at the moment? Through the data environment or at run-time through set datasource = recordset? And what's the SQL that fills the command object/recordset?

[Akshaya]

using query,how to generate ?Please give one example.

	
	
Private Sub cmdOk_Click()
    On Error Resume Next
    If Txtdate = "" Then
        flag = MsgBox("Display Entire Issued Report?", vbYesNo + vbQuestion, "Materials System")
        If flag = vbYes Then
            If Rs.State = adStateOpen Then
                Rs.Close
            End If
            Rs.open "Select * from Sale where qty>0", conn
            Set IssdateReport.DataSource = Rs
            Load IssdateReport
            IssdateReport.Show
            Unload Me
        Else
            Exit Sub
            Txtdate.SetFocus
        End If
    Else
        If Rs.State = adStateOpen Then
            Rs.Close
        End If
        Rs.open "select * from Salesnew where date between '" & Txtdate.Text & "'and '" & txtdateto.Text & "'", conn
        Set IssdateReport.DataSource = Rs
        Load IssdateReport
        IssdateReport.Show
        Unload Me
    End If
End Sub

[leinad31]

I see your getting a subset based on dates... you might want to read this first http://www.vbforums.com/showthread.php?t=450993 and decide if its feasible or not to pursue a subquery solution.

You can also try printing out your data with Excel or a better reporting tool since the data report is too basic.

[Akshaya]

Thanks.
Is it possible to include serial no. in datareport through data environment?

[Al42]

Is the serial number the number of the record in the report, or is it the number of sale? (IOW, should a particular sale have the same serial number every time it appears on a report, or should the numbers start over for each report?)

[MartinLiss]

Moved.

[Akshaya]

Thanks.
Serial number should come according to the no.of rows retrieved for that particular report.

For example,if we want to display the no.of employees(ex.125 emp.) in a particular dept. , serial no.s should be generated from 1-125 in a report.

[amrita]

I think the only way to add a serial number is to make it a part of the query.Check this

SELECT (select count(a2.emp_no) from tblEMP as a2 
where a1.emp_no<=a2.emp_no) AS [SL No], 
a1.name, a1.address FROM account AS a1 
GROUP BY a1.emp_no, a1.name, a1.address
ORDER BY a1.emp_no DESC

[leinad31]

Yes amrita, as an aggregate query (count) that is a subquery... for a1.emp_no<=a2.emp_no to work properly as intended, refer to link in post #8... also, such an implementation will also involve additional overhead moreso with lots of returned records.

[ganeshmoorthy]

using query,how to generate ?Please give one example.

Check my signature for "Add Serial Number / Row Number to SQL Queries" to add serial / row number to a query

[Akshaya]

Thanks friends.
I used the following query.It is working fine.
But Serno comes in desc order by default.How to arrange in asc. order?

SELECT Code, Name, grade,remarks,(SELECT COUNT(*) FROM Customer C2 WHERE C2.code >= C.Code) AS SerNo FROM Customer C

[Akshaya]

Thanks friends.It is working fine in this code.

SELECT code, name, grade,remarks,(SELECT COUNT(*) FROM Customer C2 WHERE C2.Code >= C.Code) AS SerNo FROM Customer C  order by c.code desc

[shakti5385]

Thanks friends.It is working fine in this code.

SELECT code, name, grade,remarks,(SELECT COUNT(*) FROM Customer C2 WHERE C2.Code >= C.Code) AS SerNo FROM Customer C  order by c.code desc

Thanks for sharing the solution

[ganeshmoorthy]

Akshaya: if you change the greater than symbol in the query to lesser than symbol it would give you the result what do u want...

for more explanation please refer this