Print a record number in access report

[khursheed]

Hi experts! (Waiting for your reply)
I want to print serial number or record number in access report while this field is not included in any table. Please help me

[JustJim]

Hi experts! (Waiting for your reply)
I want to print serial number or record number in access report while this field is not included in any table. Please help me

Hi and welcome to The Scripts,

If a serial number is not included in the table then you can't produce it at all. If you base your report on a recordset or query which is ordered in some way, then you can print the ordinal number of the record within the recordset or query.

It all depends on whether you want this number to be absolutely associated with this record or just for reference purposes within the report.

Let us have more information on your application and we'll see what we can do.

Jim

[khursheed]

I want to print record number in report only for reference. How can I do it?

[sierra7]

Hi
You can't print something that doesn't exist !

You will have to create something but what is the point of having a 'serial number' not tied to a record ? Next time you print the item may have a different number??

Access reports do not support Line Numbers (at least I can't find)

You had better give more details

S7

[khursheed]

Suppose the report is based on query that
SELECT * from employees where age>50

Now I want to print serial number against each selected record in report.

In foxpro a function recno() can do this but in ACCESS i am seeking help.

[sierra7]

Suppose the report is based on query that
SELECT * from employees where age>50

Now I want to print serial number against each selected record in report.

In foxpro a function recno() can do this but in ACCESS i am seeking help.

Hi again khursheed
It seems JustJim has your answer at the same time as myself and told you much the same thing. However, if he can get the 'ordinal' number to print in a report then it sound like this is what you really want. I shall wait for his reply because I would like to know more about this too.

Meanwhile, I have been puzzling how you could add line numbers to each record in a report and found that if you have a simple query you could try revising it into an aggregate query and 'count' a unique field;

 
SELECT FirstName, LastName, StaffNo, Age, Count(StaffNo) AS CountOfEmp
FROM employees 
GROUP BY FirstName, LastName, StaffNo, Age
HAVING ((Age>50));
 

If [StaffNo] is unique then [CountOfEmp] should be '1' for every record but when you are in Report Design mode you can change the property of the [CountOfEmp] control to be Running Sum = 'Over all' on the Data tab . This then shows in the Report as 1,2,3 etc for each record.

Of course, this will only work if you have a unique field or you could concatonate a combination of fields eg count([FirstName]&[LastName]&[Age]) but is messy and far better to modify your database to have 'serial numbers' that means something.

S7

[JustJim]

Hi again khursheed
It seems JustJim has your answer at the same time as myself and told you much the same thing. However, if he can get the 'ordinal' number to print in a report then it sound like this is what you really want. I shall wait for his reply because I would like to know more about this too.

Meanwhile, I have been puzzling how you could add line numbers to each record in a report and found that if you have a simple query you could try revising it into an aggregate query and 'count' a unique field;

 
SELECT FirstName, LastName, StaffNo, Age, Count(StaffNo) AS CountOfEmp
FROM employees 
GROUP BY FirstName, LastName, StaffNo, Age
HAVING ((Age>50));
 

If [StaffNo] is unique then [CountOfEmp] should be '1' for every record but when you are in Report Design mode you can change the property of the [CountOfEmp] control to be Running Sum = 'Over all' on the Data tab . This then shows in the Report as 1,2,3 etc for each record.

Of course, this will only work if you have a unique field or you could concatonate a combination of fields eg count([FirstName]&[LastName]&[Age]) but is messy and far better to modify your database to have 'serial numbers' that means something.

S7

Hi guys,

Sorry to keep you holding your breath, but no I don't have a great answer. If it ain't in there, you can't get it out. My "ordinal number" reply was many based on the concept of a recordset (with an index) and the recordset.absoluteposition.

Remember though that a Primary Key is an internal thing. It should be "meaningless" and "invisible to users". This is important because if a record is deleted in a table a PK between, say, 1003 and 1005 may be missing, it it is "meaningless" and "invisible to users" it will not matter. If the PK was visible to users and somehow meaningful they may be tempted to "replace"it. This can cause all sorts of problems!

Do try to read Mary's wonderful explanation of database normalisation (then read it again in about month or so, it takes a while to sink in)

Jim

[sierra7]

Hi Jim
That's a pity. I hadn't heard of 'ordinal' numbers and thought I was going to learn something new. It's a long time since I did dBase and RecNo did seem to ring a bell. But I totally agree with your rationale.

When I did some Unbound forms a while ago I had to keep looking up '.AbsolutePosition + 1' to find the record number as the built-in navigation controls don't work and had to write my own.

If you could base an Access report on a Recordset instead of a Query then this might be a possiblity.... I suppose you would have to define the Recordset name Public then you be able to use it as the Record source. .

I have not tried this before (and too busy today) but do you know if it's possible??

S7

[Jim Doherty]

Suppose the report is based on query that
SELECT * from employees where age>50

Now I want to print serial number against each selected record in report.

In foxpro a function recno() can do this but in ACCESS i am seeking help.

If all you are wishing to do is print a record number and are not concerned about its ordinal meaning in relation to the data 'other' than showing a sequential number then simply place an unbound textbox in the detail section of the report and set its controlsource to

=[CurrentRecord]

Regards

Jim :)