Numbered Transactions

[Mike]

I have a query with specific results that have fields such as concatenation,
division, amount, etc....I want to have a field which numbers the rows in
sequence all the way down to the last row. Is this possible?

Thanks in advance.
Mike

 

[John Spencer]

YEs, it is possible.

WHERE do you want to use this. If you want a one-up number in a report, it is
very simple to do. In a query, it becomes more difficult.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KenSheridan via AccessMonster.com]

Mike:

Yes, if you have column(s) by which the query is sorted, e.g. in the
following example where a table is sorted by a date column, but where two
rows might have the same date, in which case the distinctly valued YourID
column is brought into play to arbitrarily sort within each group of rows
with the same date:

SELECT
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.YourDate <= T1.YourDate
AND ( T2.YourID <= T1.YourID
OR T2.YourDate <> T1.YourDate))
AS RowCounter, YourDate, YourID
FROM YourTable AS T1
ORDER BY YourDate, YourID;

If the query is being used as the basis for a report, however, its better to
number the rows in the report by having a text box with a ControlSource
property of =1 and a RunningSum property of 'Over All'.

Ken Sheridan
Stafford, England

 

[Mike]

I would like to display this using a field column in a query.

Thank you

 

[KenSheridan via AccessMonster.com]

Mike:

A query of the type I posted in my earlier response does this, but you must
have the necessary columns by which to order it of course. Also if the query
needs to be updatable you'd have to call the DCount function rather than
using a subquery:

SELECT
DCount("*", "YourTable",
"YourDate <= #" &
Format(YourDate,"yyyy-mm-dd") &
"# And (YourID <= " & YourID &
" Or YourDate <> #" &
Format(YourDate,"yyyy-mm-dd") & "#)")
AS RowCounter, YourDate, YourID
FROM YourTable
ORDER BY YourDate, YourID;

Ken Sheridan
Stafford, England

I would like to display this using a field column in a query.

Thank you

YEs, it is possible.

[quoted text clipped - 13 lines]

.