Display RecNo

[Viju]

Hi,
I wanted to insert a new column to my query which will
show its corresponding record number. If the result of my
query will display 3 records, the new column will just
show numbers 1, 2, 3 record by record and in sequence.
Other database such as dBase, FoxPro has recno() which
can display its record number but how will i do it in ms
access.
Thanking You
VIJU

 

[Tom Ellison]

Dear Viju:

The records in a table or query do not have any order to them except
if you impose an order on them. As such, the term "record number" has
meaning only in the context of an order you prescribe.

SInce the term "record number" would seem to have a connotation of
there being some intrinsic ordering to the rows (which I am contending
does not exist) I prefer to reduce confusion by referring to the
imposition of a numbering system to a set of rows as "ranking" those
rows according to the values in one or more columns.

To produce a ranking of rows in a set of rows, you need supply one or
more columns by which to order the ranking. If this set of columns
uniquely orders the rows, then the ranking values will be unique. If
the set of columns by which you order and rank the rows is not unique
then the ranking column will not be unique.

To provide a ranking column in a query you can write a correlated
subquery that counts the number of rows that precede each row. This
will produce a new ranking column for the query that numbers the rows
starting with zero. Add one if you want to start the ranking values
from one.

For more specific information on how to do this in your situation,
please post a query that provides the results you want omitting the
ranking column. Be sure to include an ORDER BY clause to describe the
ordering to be imposed on the ranking. I can provide you with a
subquery to produce the ranking based on this.

Hi,
I wanted to insert a new column to my query which will
show its corresponding record number. If the result of my
query will display 3 records, the new column will just
show numbers 1, 2, 3 record by record and in sequence.
Other database such as dBase, FoxPro has recno() which
can display its record number but how will i do it in ms
access.
Thanking You
VIJU

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Albert D. Kallal]

The problem with Recno() in FoxPro is that it returns the actual record
number.

So, if you have 30,000 records in a file, and your query returns 3 records,
recno() is going to return some sheer nonsense like:


2375
25,000
11,000

You will have 3 records, but recno() is the actual physical record number.
Worse, in a multi-user situation if you use the "pack" command, those
numbers will change. All in all, this results in information that is
completely useless. You can send the query to a temp file, and then use
recno(), but creating/using temp files is not a good idea. That is why we
have a query engine!

As you can see, ms-access not have the concept of record number, and for
most modern database systems, the concept of physical record number location
has gone the way of the do-do bird (just like we don't use punched cards
anymore!).

However, you certainly can have the need to display a record count in things
like reports etc.

If you are sending the data to a report, then simply put a un-bound text box
in the report like:

=(1)

Set the running sum for the text box to true, and it will increment for you
and display the record count for the report.

If you need a way to do this in a sub-form, or continues form, then you can
create a function in the *forms* module like:

Public Function NRec(vID As Variant) As Variant

If IsNull(vID) = False Then
Me.RecordsetClone.FindFirst "id = " & ID
NRec = Me.RecordsetClone.AbsolutePosition + 1
End If

End Function

Now, just place a un-bound text box on the form with:

=nrec([id])

 

[Groucho]

"Albert D. Kallal" wrote in message

However, you certainly can have the need to display a record count in

things like reports etc.

Somehow a light bulb went off at the sql standards committee
when it was finally realized that *row numbers* and *rankings*
passed the 'display' stage and are now considered fundamental
constructs in problem solving as well.So instead of using inefficient
representations (subqueries/joins) they are now part of the
sql lexicon and embedded in the engine where they belong.
Sql99 now has row_number(),rank() and dense_rank().These
functions are available in Oracle and IBM DB2.MS has a lot
of catching up to do.

RAC v2.2 and QALite released for Sql Server 2000.
www.rac4sql.net

 

[Albert D. Kallal]

I can't agree more. As long as one is not thinking in terms of physical
record location then we are ok.

Old programs like dbase and variants like old FoxPro did use and work with
RecNo() which returned the physical disk record number. In fact, old dbase
code works on the assumption that recno() is the physical record on the disk
drive. Thus, that number can't even be used for identity purposes since it
can change.

Modern databases don't have the concept of record numbers anymore. However,
having a rank/row_number for a "set" of data returned is a fine concept. As
long we don't confuse the two issues, I all on board.

 

[Viju]

Hi,
I am posting a query as a continuation of the query
posted on Sunday, 14 Sep 2003 regarding RecNo().

SELECT INVOICEITEM.INVOICENO, INVOICEITEM.ITEMCODE,
ITEMMASTER.ITEMNAME, INVOICEITEM.QUANTITY,
INVOICEITEM.COSTPRICE, INVOICEITEM.SALEPRICE
FROM INVOICEITEM INNER JOIN ITEMMASTER ON
INVOICEITEM.ITEMCODE = ITEMMASTER.ITEMCODE
WHERE (((INVOICEITEM.INVOICENO)='26'));

Now I wanted to include a column which will
display 'SlNo'. It is not necessary to order the rows.

Thanking you in advance,
VIJU
..

-----Original Message-----
Dear Viju:

The records in a table or query do not have any order to them except
if you impose an order on them. As such, the term "record number" has
meaning only in the context of an order you prescribe.

SInce the term "record number" would seem to have a connotation of
there being some intrinsic ordering to the rows (which I am contending
does not exist) I prefer to reduce confusion by referring to the
imposition of a numbering system to a set of rows as "ranking" those
rows according to the values in one or more columns.

To produce a ranking of rows in a set of rows, you need supply one or
more columns by which to order the ranking. If this set of columns
uniquely orders the rows, then the ranking values will be unique. If
the set of columns by which you order and rank the rows is not unique
then the ranking column will not be unique.

To provide a ranking column in a query you can write a correlated
subquery that counts the number of rows that precede each row. This
will produce a new ranking column for the query that numbers the rows
starting with zero. Add one if you want to start the ranking values
from one.

For more specific information on how to do this in your situation,
please post a query that provides the results you want omitting the
ranking column. Be sure to include an ORDER BY clause to describe the
ordering to be imposed on the ranking. I can provide you with a
subquery to produce the ranking based on this.

 

[Tom Ellison]

Dear Viju:

I have no technique that will assign unique numbers throughout a set
of rows in a query other than the technique that assigns them based on
an existing, unique ordering. The technique uses the unique ordering
to assign the numbers.

I suppose you could just use a random number generation to obtain a
number for each row. There would not be a guarantee that the result
would be unique.

Such a random ordering would not be repeatable. The number assigned
to a row would potentially change every time you run the query to
assign them.

Perhaps if you give some information on why you want this number
assigned, and how you will use it, then I could be of more help.

Given your inner join, you probably have a one-to-many relationship
between tables ITEMMASTER and INVOICEITEM. If so, an autonumber
column in the table on the many side would provide a unique value that
is repeatable. Perhaps this would be a solution for you.

Hi,
I am posting a query as a continuation of the query
posted on Sunday, 14 Sep 2003 regarding RecNo().

SELECT INVOICEITEM.INVOICENO, INVOICEITEM.ITEMCODE,
ITEMMASTER.ITEMNAME, INVOICEITEM.QUANTITY,
INVOICEITEM.COSTPRICE, INVOICEITEM.SALEPRICE
FROM INVOICEITEM INNER JOIN ITEMMASTER ON
INVOICEITEM.ITEMCODE = ITEMMASTER.ITEMCODE
WHERE (((INVOICEITEM.INVOICENO)='26'));

Now I wanted to include a column which will
display 'SlNo'. It is not necessary to order the rows.

Thanking you in advance,
VIJU
.

-----Original Message-----
Dear Viju:

The records in a table or query do not have any order to them except
if you impose an order on them. As such, the term "record number" has
meaning only in the context of an order you prescribe.

SInce the term "record number" would seem to have a connotation of
there being some intrinsic ordering to the rows (which I am contending
does not exist) I prefer to reduce confusion by referring to the
imposition of a numbering system to a set of rows as "ranking" those
rows according to the values in one or more columns.

To produce a ranking of rows in a set of rows, you need supply one or
more columns by which to order the ranking. If this set of columns
uniquely orders the rows, then the ranking values will be unique. If
the set of columns by which you order and rank the rows is not unique
then the ranking column will not be unique.

To provide a ranking column in a query you can write a correlated
subquery that counts the number of rows that precede each row. This
will produce a new ranking column for the query that numbers the rows
starting with zero. Add one if you want to start the ranking values
from one.

For more specific information on how to do this in your situation,
please post a query that provides the results you want omitting the
ranking column. Be sure to include an ORDER BY clause to describe the
ordering to be imposed on the ranking. I can provide you with a
subquery to produce the ranking based on this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Viju:

"I wanted to list Items in the Order I entered the Invoice"

Do you keep track of this Order in any fashion? Are you Invoice
numbers assigned sequentially? Do you keep a date/time stamp in the
Invoice table? And, if you do, is this guaranteed to be unique?

If this Order is important, then your table design needs to keep track
of it in some fashion. That is because Access does not do this for
you.

Dear Tom,
Thank you for the response!! I just wanted row
numbers which can be used as SlNo in reports. I save all
the invoices in the INVOICEITEM table, and those invoices
will be displayed later in a MsFlexGrid. There I use VB
Code to generate SlNo. Now I wanted to diplay it in Data
Report. I am using VB 6 and Data Report for reporting. I
don't know if there is a technique in Data Report (of VB)
where we can use any running variable. If any solution is
there please let me know, I will be thankful to you. I
can order the Items in ITEMMASTER according to their
Code, But I suspect that will change the order of
display, ie, I wanted to list Items in the Order I
entered in the Invoice.
Thanking you in advance.

VIJU.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Viju]

Dear Tom,
Thank you for the response. I will explain my
problem a little more. I use two tables, INVOICEBILL &
INVOICEITEM to store the Invoice Data. INVOICEBILL has
the details ,InvNo, Date, CustomerId etc ..INVOICEITEM
has the details InvNo, ItemCode, Qty, SalesPrice..etc. An
operator enter Items in the invoice in any order. I
thought it will be better if I display those in the same
order (Order here doesn't mean an ORDER in query) as he
entered when he chooses to view an old invoice. Now it is
working OK and I am not sure whether this is a good
method or not!! I could have used a SLNo field and I
could have displayed items in the Invoice in the order
of SLNo. Now, InvNo in the INVOICEBILL is unique, But
InvNo in INVOICEITEM is not and I display invoices in a
VB Flex Grid, where I can put SlNo's very easily. I just
Select all Items from INVOICEITEM whose InvNo='111' (for.
eg.) . Now I wanted to display those items in a
datareport. I just wanted a column which can display
RowNo's so that I can put SlNo's in the report. Is there
any method? Or I have to change the design to Include an
additional field such as "SLNO"?

Thanking you,
VIJU.

-----Original Message-----
Dear Viju:

"I wanted to list Items in the Order I entered the Invoice"

Do you keep track of this Order in any fashion? Are you Invoice
numbers assigned sequentially? Do you keep a date/time stamp in the
Invoice table? And, if you do, is this guaranteed to be unique?

If this Order is important, then your table design needs to keep track
of it in some fashion. That is because Access does not do this for
you.

 

[Viju]

Dear Tom,
Pls consider this mail as the continuation of the
previous reply. Suppose if I wanted to Put RowNo's to
Invoices which is ordered by InvNo (ie. Sequentially and
is Unique) , what should I do? Please suggest a subquery,
as you mentioned earlier, which counts the number of rows
that precede each row.
Thanking you,
VIJU

-----Original Message-----
Dear Viju:

"I wanted to list Items in the Order I entered the Invoice"

Do you keep track of this Order in any fashion? Are you Invoice
numbers assigned sequentially? Do you keep a date/time stamp in the
Invoice table? And, if you do, is this guaranteed to be unique?

If this Order is important, then your table design needs to keep track
of it in some fashion. That is because Access does not do this for
you.

 

[Tom Ellison]

Dear Viju:

There is a system I use for numbering detail rows, such as within an
invoice. However, is is not based on when each row was added.
Rather, it allows the user to insert a new line anywhere within the
invoice, and allows them to move the lines around.

Here's a brief description. The InvSeq column that sorts the rows
within an invoice is an 80 character long value. The first four
characters are actually a 4 digit sequence value that is used only if
a new row is added to the end of the invoice. The other 76 characters
are used to subdivide the row sequence if a new row is inserted
between existing rows.

When a new row is inserted between two existing rows (or before the
first row) I use the characters 0-9 and A-V in these 76 characters.
This is a total of 32 character values which works out well as it is a
power of two, and therefore easy to split in half. So, I put in a
value that is exactly half way between the row preceding it and the
row following it.

For example, when inserting a new row between '000100000000 . . .' and
'000200000000 . . .' (that is, an invoice where two rows have been
added at the end of the list) the new row gets '0001G000000 . . .'
Then, if a new row is inserted between '000100000000 . . .' and
'0001G0000000 . . .' it would be '000180000000 . . .'

Each inserted row divides neatly between the preceding and following
rows. No renumbering of other rows is necessary to accomodate this.
Each of the 76 digits may be subdivided 5 times before it needs to
begin using the next digit. So, you can insert repeatedly at the same
point 380 times before it fails. This has always been enough in
practice.

I point out this moderately complex, but workable solution, so you
will see this is a real world problem of not insubstantial
proportions. However, if all you allow is the addition of a new "last
row" to an invoice, simply numbering them would work. Just get an
aggregate MAX() of the line numbers used in a given invoice and add
one to that. Or, use a date/time stamp.

The same thing can be done using number values, but the largest
numeric datatype cannot come close to the number of ordered
combinations in an 80 character width like this. In addition, if more
than 380 subdivisions became necessary, it is easily extensible for
more resolution.

Dear Tom,
Thank you for the response. I will explain my
problem a little more. I use two tables, INVOICEBILL &
INVOICEITEM to store the Invoice Data. INVOICEBILL has
the details ,InvNo, Date, CustomerId etc ..INVOICEITEM
has the details InvNo, ItemCode, Qty, SalesPrice..etc. An
operator enter Items in the invoice in any order. I
thought it will be better if I display those in the same
order (Order here doesn't mean an ORDER in query) as he
entered when he chooses to view an old invoice. Now it is
working OK and I am not sure whether this is a good
method or not!! I could have used a SLNo field and I
could have displayed items in the Invoice in the order
of SLNo. Now, InvNo in the INVOICEBILL is unique, But
InvNo in INVOICEITEM is not and I display invoices in a
VB Flex Grid, where I can put SlNo's very easily. I just
Select all Items from INVOICEITEM whose InvNo='111' (for.
eg.) . Now I wanted to display those items in a
datareport. I just wanted a column which can display
RowNo's so that I can put SlNo's in the report. Is there
any method? Or I have to change the design to Include an
additional field such as "SLNO"?

Thanking you,
VIJU.

-----Original Message-----
Dear Viju:

"I wanted to list Items in the Order I entered the Invoice"

Do you keep track of this Order in any fashion? Are you Invoice
numbers assigned sequentially? Do you keep a date/time stamp in the
Invoice table? And, if you do, is this guaranteed to be unique?

If this Order is important, then your table design needs to keep track
of it in some fashion. That is because Access does not do this for
you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Viju]

Dear Tom,
Thank you for the mail. I took pretty enough time
understand the logic and I think that it's a very good
workable solution!!
Would you please let me know how can I use a subquery
that you mentioned earlier that will count all the
preceding rows. I come across so many situations where
serial No's should be used in generating reports. Take an
example of Employee Listing, where all employees have
unique numeric Id's. How do I use a subquery which will
display Serial No's in a list Employees.
eg. SlNo EmpId EmpName
1 1001 VIJU
2 1013 TOM
3 1200 KATE

Here I will use "ORDER BY EmpId", but I don't have
a SLNO field in my database. Is there any query which can
display serialNo's as shown above?

With great anticipation,
Thaking you,
VIJU

-----Original Message-----
Dear Viju:

There is a system I use for numbering detail rows, such as within an
invoice. However, is is not based on when each row was added.
Rather, it allows the user to insert a new line anywhere within the
invoice, and allows them to move the lines around.

Here's a brief description. The InvSeq column that sorts the rows
within an invoice is an 80 character long value. The first four
characters are actually a 4 digit sequence value that is used only if
a new row is added to the end of the invoice. The other 76 characters
are used to subdivide the row sequence if a new row is inserted
between existing rows.

When a new row is inserted between two existing rows (or before the
first row) I use the characters 0-9 and A-V in these 76 characters.
This is a total of 32 character values which works out well as it is a
power of two, and therefore easy to split in half. So, I put in a
value that is exactly half way between the row preceding it and the
row following it.

For example, when inserting a new row

between '000100000000 . . .' and

'000200000000 . . .' (that is, an invoice where two rows have been
added at the end of the list) the new row gets '0001G000000 . . .'
Then, if a new row is inserted

between '000100000000 . . .' and

'0001G0000000 . . .' it would be '000180000000 . . .'

Each inserted row divides neatly between the preceding and following
rows. No renumbering of other rows is necessary to accomodate this.
Each of the 76 digits may be subdivided 5 times before it needs to
begin using the next digit. So, you can insert repeatedly at the same
point 380 times before it fails. This has always been enough in
practice.

I point out this moderately complex, but workable solution, so you
will see this is a real world problem of not insubstantial
proportions. However, if all you allow is the addition of a new "last
row" to an invoice, simply numbering them would work. Just get an
aggregate MAX() of the line numbers used in a given invoice and add
one to that. Or, use a date/time stamp.

The same thing can be done using number values, but the largest
numeric datatype cannot come close to the number of ordered
combinations in an 80 character width like this. In addition, if more
than 380 subdivisions became necessary, it is easily extensible for
more resolution.