Create count to show records Y of X

[badandy996]

Hello,
I am working on an asset database. I have it set up where I can put in the order and shows there are "X" assets on this order. How can I make it so when the user is on a partitcular recordset it will show "Y" of "X"

Y = current record set tied to that order
X = all records tied to that order

[NeoPa]

Not easily. The whole concept of Set Theory - upon which most RDBMSs are built - is that there IS no order. They are simply within a set - or Table.

You can sort result data in a Query, but the data must be there before the sorting is applied so Ordinals are not naturally available.

You can have Report Controls that use the Running Sum property so that Ordinals are displayed in the Report, but that is still not within your data.

Essentially Ordinals are anathema to database theory.

All that said, in some circumstances it is possible to produce these within the data. If you have a Field within your data that reflects the order you want to determine the Ordinal by then you can link to a SubQuery of items where the value is less than (or greater than, whatever.) the value in the current record and return a Count() of that SubQuery. Very expensive in processing but can be done.

Obviously that's a kludge. I suspect what you really need to ask yourself is why you think such an item is necessary. I guess it's not impossible but I've been programming in databases for a few years now and the only time I ever had to think about such matters was when people have asked for it, generally before they realised it wasn't a great idea.