Sequential Numbering in Quuery

[Betty]

Is it possible to assign sequential numbers (checks - starting with # xxxx)
in a query? I have payments that need to be made and I would like to assign
check numbers to print checks out of the database.
Thank you

 

[Allen Browne]

Betty, I think you will actually need a table to track the payments, each
with its own unique check number recorded.

If you just try to calculate the next available number, on the fly in a
queyr, the numbers will get all messed up over time. For example, if someone
deletes on old record, all the later numbers will change. Similarly, if
someone backdates an entry, all the existing numbers from that time on will
change. In fact, if a check is cancelled before you issue it (e.g. somebody
messed it up), you need to be able to skip a number in your database.

If you want to do it anyway, see:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html

 

[a a r o n _ k e m p f]

SQL Server has the RANK function

so sorry that Jet doesn't meet your needs.

PS - you _DO_ know that writing checks from Jet is a bad idea--
because you have no audit trail and no security, right?

 

[Michel Walsh]

Sure MS SQL Server has a RANK function, ... but that would imply that your
'check number' COULD change if its value is a computed expression. I don't
know what kind of security you have in mind with your tracking system
referring the check which was the 10th one, as per the date of the third of
March 2009, while the same check may now be the 11th one, or any other, 10
days later, when you read the log!

Your remark is not only irrelevant, but frivolous.

Sure, unless you dare to explain to us how a computed expression, such as a
RANK, could be used as a universal, time independent, reference for a
tracking system.



Vanderghast, Access MVP



SQL Server has the RANK function

so sorry that Jet doesn't meet your needs.

PS - you _DO_ know that writing checks from Jet is a bad idea--
because you have no audit trail and no security, right?