Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    London
    Posts
    7

    Unanswered: Help! Autonumber in query output

    Hi,

    I need to output the results of a query, but need to have an index field (autonumber) as an additional field in the query. Is there any way I can do this, function or otherwise?

    Thanks for the help

  2. #2
    Join Date
    Jul 2003
    Posts
    38
    There are different ways. Easiest (to me) seems to create another table, just containig one autonumber column and one where you can link to the existing table (primary key or whatever).
    Then create your query containing a LEFT JOIN on the new table.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...or if you don't have a suitable unique linking field in the query output, APPEND to a table with suitable design to hold the query results plus an autonumber field. izy

  4. #4
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    read this:

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •