Adding an autoincrement field to a query.

[Javier Hernández]

Minor question here. I have a table with the quality control measurements for all of our processes and I need to build control charts from it. I can make a query to filter each process and order the measurements by date, but then I need to number each record of the query incrementally in order to draw the control chart (eg. pick measurements from #30 to #50). Is it possible to add a calculated autoincrement field to a query with either QBE or SQL? All information I have found refers to the AUTOINCREMENT type of data for DDL sentences, which I don't need here.

[ranman256]

Its best to do an append to a new table with autonumber field. Queries arent good making autonumbers....they didnt make that a thing.

[June7]

Not an append but an UPDATE to populate a field in table with the desired sequence. Review
http://www.office-forums.com/update-...s-t703875.html
http://dbaspot.com/ms-access/392671-...g-sql-vba.html

Maybe like:

UPDATE tablename SET fieldname = DCount("*", "tablename", "datefield<#" & [datefield] & "#") + 1

[Javier Hernández]

Thanks, I have made the new table for this manipulation purposes. However, I have found the UPDATE instruction takes a significant amount of time to finish. Does the DCount (or any other aggregate function for that matter) function becomes slow when the amount of records is about 150-200?

[June7]

No, should be very fast for so few records. The UPDATE works for me.

Why would you need a new table?