How SERIAL NUMBER is inserted in Data Reports

[asif_14443]

I am using Data Reports in Visual Basic. How Serial Number is insert on the data reports ??

like this....

S.No Name City
1. Asif Islamabad
2. MahmoodRawalpindi
3. Khalid Lahore
4. Yasir Karachi

[Coke]

in the field explorer, there is this special field call record number. drag it into the section(details), it will then display in section(page header) and section(details). You try.

[Coke]

in the field explorer, there is this special field, expand it and look for record number, drag it into the section(details), it will then display in section(page header) and section(details). You try.

[asif_14443]

Where is field explorer...???

but I dont want to call any serial number from database.... because it will not be sorted, when I sort any other field.

I drag all fields on the Pager Header and Detail Section. it shows records and my reports run correctly. but there is still prob of Serial Number. I have short time to present to my officers.

[brucevde]

I believe Coke thinks you are using Crystal Reports. There is no RecordNumber in Data Reports.

Data Reports has pretty limited functionality. This Serial/Record number that you want to display must come from a field in the Recordset bound to the report You will need to design the query so that it generates and includes this number.

Which database are you using?

[asif_14443]

I am using MS Access as database. no I am not using Crystal Reports, only Data Report through Data Environment.

Any short way to put S.No. please help.

[asif_14443]

Any Answer plz................

[brucevde]

Use a Sub Query to determine the serial/record number.

SELECT Customers.CustomerID, Customers.CompanyName, 
(SELECT Count(*) From Customers As Rank Where Rank.CustomerId <= Customers.CustomerId) AS RecordNumber
FROM Customers
ORDER BY Customers.CustomerID;

In the above query, the sub query returns the number of customer records that are less than or equal to the current record. You can use any number of fields in the sub queries' where clause, just make sure they indicate a unique value.

Running the query against the Northwind database produces these results

Customer ID	Company Name	RecordNumber
ALFKI	Alfreds Futterkiste                               	1
ANATR	Ana Trujillo Emparedados y helados                	2
ANTON	Antonio Moreno Taquería                           	3
AROUT	Around the Horn                                   	4
BERGS	Berglunds snabbköp                                	5
BLAUS	Blauer See Delikatessen                           	6
BLONP	Blondel père et fils                              	7
BOLID	Bólido Comidas preparadas                         	8
BONAP	Bon app'                                          	9
BOTTM	Bottom-Dollar Markets                             	10
...etc

[asif_14443]

thanks , as u told and I have applied this query, but in my case it show total number of records as count 179
--------------------------------------
SELECT EQUIPMENT_ID, MODEL,
(SELECT COUNT(*)
FROM EQUIPMENT_MSTR AS SNO
WHERE SNO.EQUIPMENT_ID <= EQUIPMENT_ID)
AS SLNO
FROM EQUIPMENT_MSTR
ORDER BY EQUIPMENT_ID
--------------------------------------

EQUIPMENT_ID MODEL SLNO
3 SK-F 710E 179
4 SK-F2 179
5 SK-F3 179
8 . 179
9 RVS 416 179
10 BVS-3100P 179
11 DFS-700 179
12 PRODIOGY 179
- - -
- - -
- - -
- - -
- - -
- - -
- - -
----------------------------------------
I want serial no as 1,2,3,4,5,6,7,8,...........179
and how I will print this serial number on data reports....?????????

[brucevde]

but in my case it show total number of records as count 179

You did not indicate the table name of the second Equipment_Id field.

SELECT EQUIPMENT_ID, MODEL,
(SELECT COUNT(*)
FROM EQUIPMENT_MSTR AS SNO
WHERE SNO.EQUIPMENT_ID <= EQUIPMENT_MSTR.EQUIPMENT_ID) 
AS SLNO
FROM EQUIPMENT_MSTR
ORDER BY EQUIPMENT_ID

and how I will print this serial number on data reports

The SLNO field is part of the Recordset. Print it the same way you print any other field in the recordset.

[asif_14443]

ooooooooooo WOW,,,,, Fantastic... you did it man....

Now the secong prob... it shows all rows from a table from 1 to 179
but in my case the rows can be fluctuate.... 1 to 130, or 1 to 56, or 1 to 475 etc etc... now how can I more filter this command... to take parameter from user via ComboBox or TextBox.

[asif_14443]

My actual Query is as under

SELECT CATEGORY_MSTR.EQUIPMENT_CATEGORY, SUB_CATEGORY_MSTR.EQUIPMENT_SUBCATEGORY, BRAND_MSTR.BRAND_NAME, EQUIPMENT_MSTR.MODEL, EQUIPMENT_MSTR.QUANTITY, LOCATION_MSTR.LOCATION, EQUIPMENT_MSTR.DESCRIPTION, EQUIPMENT_MSTR.DATE_OF_COMMISSIONING FROM LOCATION_MSTR, EQUIPMENT_MSTR, CATEGORY_MSTR, SUB_CATEGORY_MSTR, BRAND_MSTR WHERE LOCATION_MSTR.LOCATION_ID = " & mLocId & " and LOCATION_MSTR.LOCATION_ID = EQUIPMENT_MSTR.LOCATION_ID AND EQUIPMENT_MSTR.DOMAIN_ID = CATEGORY_MSTR.DOMAIN_ID AND EQUIPMENT_MSTR.SUB_CATEGORY_ID = SUB_CATEGORY_MSTR.sub_category_id AND EQUIPMENT_MSTR.BRAND_ID = BRAND_MSTR.BRAND_ID ORDER BY CATEGORY_MSTR.EQUIPMENT_CATEGORY, BRAND_MSTR.BRAND_NAME, SUB_CATEGORY_MSTR.EQUIPMENT_SUBCATEGORY, LOCATION_MSTR.LOCATION, EQUIPMENT_MSTR.MODEL

[asif_14443]

this is a relational database containing following tables:

EQUIPMENT_MSTR
LOCATION_MSTR
CATEGORY_MSTR
SUB_CATEGORY_MSTR
BRAND_MSTR

now I want to fetch those records i.e at specific location how much equipment is installed.

I have assigned Location ID to the query.. and it gives correct result.,
now I want to put simple Serial Number in this report.

How I will combine your serial number code in my query.?

[asif_14443]

any answer please........???

[asif_14443]

BRUCEVDE I am stilling waiting for your response... please help me.....

[asif_14443]

I am still waiting for your reply regarding putting serial number in data reports...Brucevde you told me the code,, and I have applied it in the data reports but I am still facing problem, that it prints the serial number of data reports but It does not show the serial number in sequence order,, but it shows only records number placed in table.

let me explain through an example:-

Eqpt Location Qty Model
Camera Karachi 12 sp12
Camera Karachi 10 cd25
Camera Lahore 8 cf85
VCR Karachi 3 -
VCR Lahore 4 -
VCR Quetta 6 -
Tripod Islamabad 8 -
Tripod Islamabad 9 -
Camera Quetta 6 vb67
Camera Karchi 9 gh87
Camera Karchi 12 je87
DSNG Islamabad 3 -
DSNG Peshawer 6 -

Now in the above record if I select those Cameras which are located at KARACHI it gives me the following result:-

Sl.No Eqpt Location Qty Model
1. Camera Karachi 12 sp12
2. Camera Karachi 10 cd25
10. Camera Karchi 9 gh87
11. Camera Karchi 12 je87

Now in the above produced result the serial number is 1,2,10,11 instead of 1,2,3,4 .... if u concentrate it, this is giving only the corresponding record number from the table.

I want this result:-

Sl.No Eqpt Location Qty Model
1. Camera Karachi 12 sp12
2. Camera Karachi 10 cd25
3. Camera Karchi 9 gh87
4. Camera Karchi 12 je87

Please reply me... I am waiting for your response.

[mabbas110]

u have to generate ur self by traversing each record. people are mixing here with crystal report and data report.

[asif_14443]

but how I generate it manually... please send me some code....

[mabbas110]

u must have record in table
add one more column in table just in query.

if u can send me ur exact query from where u r pulling the data then i can help you out. please send me

[asif_14443]

i hv record in the table. ok if i add new column in the table but i would be null., i have written code on the above queries....