You may need to produce a report or query which has each row of output numbered to enhance report readability or to print only every X row of output. To create a query which has an auto number assigned field for each row of output you use a sub-query in the auto number field.
A sub-query is a query which is imbedded within your target query or report record source. They can be used to limited data in a where condition, or act as a field's data source. To create an auto number field, we use a sub-query as a field to count the number of records which occurred in underlying table or query before the current row is reached.
Critical to creating the auto number sub-query is that the table or query you are using as a source must contain a unique index and this field must be included in your auto number query. As an example in an order's report, usually the order number would be a unique field used in the query or recordset.
Example: To list all orders in the table, "tblOrders" and auto number the output rows, you would enter the following ina field of the orders query:
RowNum: (Select Count (*) FROM [tblOrders] as Temp
WHERE [Temp].[OrdNum] < [tblOrders].[OrdNum])+1
If you need to constrain your query's output (as is usually the case,) to a specific set of records, you must add the same constraints to your auto number sub-query, so that temp recordset generated by the sub-query has the same result set as your main query. As an example, if you wanted the output to only list orders between a specific "StartDate" and "EndDate" you would also add the same parameters to the subquery previously shown, so that your subquery in the field would now read:
RowNum: (Select Count (*) FROM [tblOrders] as Temp
WHERE ((Temp.[OrdDate] BETWEEN [Startdate] AND [EndDate] )
AND ([Temp].[OrdNum] < [tblOrders].[OrdNum])))+1
