Increment A Field; DMAX?

[Dugantrain]

I'm trying to accomplish a (seemingly) simple task. I'd like a calculated, non-stored field in a query to increment for each row in the recordset. There will be another calculated field in this row which will simply be this new incremented field-1. The idea is that I'm trying to self-join a table's record to the record right before it. I'd prefer not to use a stored Autonum as I'd like to avoid any entanglements with missing numbers. I believe DMAX is the way to accomplish this task, but I haven't quite gotten a handle on how to accomplish this.

 

[Jon K]

The domain function to use is DCount.

To rank records with 1,2,3,... etc (e.g. for each ClientID), the table needs to have a field
(a) that can uniquely identify each record; and
(b) that can be compared with >= or <=.

An autonumber field or a unique date field (e.g. unique for each ClientID) is a good example.


I will demonstrate with the DB attached. The table tblAccounts contains an autonumber field as well as a date field. The query I used is:

qryDCount:-
SELECT a.*,
val(DCount("*","tblAccounts","ClientID='" & a.ClientID & "' and AutoNum<=" & a.AutoNum)) AS AutoNumDCount,
AutoNumDCount-1 AS AutoNumDCountMinus1,
val(DCount("*","tblAccounts","ClientID='" & a.ClientID & "' and DateField<=#" & a.DateField & "#")) AS DateFieldDCount,
DateFieldDCount-1 AS DateFieldDCountMinus1
FROM tblAccounts AS a
ORDER BY [ClientID], [AutoNum];

Fields in DCount have to be surrounded with correct delimiters. The query shows how to rank records based on an autonumber field and also on a date field.


I have also included another query as an alternative:

qrySelectCount:-
SELECT a.*,
(SELECT Count("*") from tblAccounts where ClientID=a.ClientID and AutoNum<= a.AutoNum) AS AutoNumSelectCount,
AutoNumSelectCount-1 AS AutoNumSelectCountMinus1,
(SELECT Count("*") from tblAccounts where ClientID=a.ClientID and DateField<=a.DateField) AS DateFieldSelectCount,
DateFieldSelectCount-1 AS DateFieldSelectCountMinus1
FROM tblAccounts AS a
ORDER BY [ClientID], [AutoNum];

This query uses the Select Count subquery to achieve the same results as the query using DCount. In Select Count, the fields need not be surrounded with delimiters.


As Select Count can be optimised by Access, you will see that the second query runs significantly faster than the first.

Hope the sample DB helps.

The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.

 

[Dugantrain]

OK, got it. Maybe not quite as simple as I thought (after all, I'm really just trying to find a way to accomplish in SQL what an Autonum already does) but it works none-the-less. Thank you for your time.