Bingo said:
I use a make table query to write a subset of data to a new table. In this
new table, I would like to create a new column (Record ID) that shows the
row# for that data (or sequential number starting with 1).
Your row_ID is a calculated column and just about everyone round here
agrees such simple calculations should not be stored in a table.
What attribute of the original data set gives it its relative order?
For example, using the Northwind Orders table, OrderID could be used
SELECT DT1.row_ID,
DT1.OrderID, DT1.ShippedDate
FROM (
SELECT T1.OrderID, T1.ShippedDate, (
SELECT COUNT(*)
FROM Orders
WHERE ShippedDate
BETWEEN #1995-06-01#
AND #1995-06-30#
AND OrderID <= T1.OrderID
) AS row_ID
FROM Orders AS T1
WHERE T1.ShippedDate
BETWEEN #1995-06-01#
AND #1995-06-30#
) AS DT1
ORDER BY DT1.row_ID;
This demonstrates why it could not be ShippedDate
SELECT DT1.row_ID,
DT1.OrderID, DT1.ShippedDate
FROM (
SELECT T1.OrderID, T1.ShippedDate, (
SELECT COUNT(*)
FROM Orders
WHERE ShippedDate
BETWEEN #1995-06-01#
AND #1995-06-30#
AND ShippedDate <= T1.ShippedDate
) AS row_ID
FROM Orders AS T1
WHERE T1.ShippedDate
BETWEEN #1995-06-01#
AND #1995-06-30#
) AS DT1
ORDER BY DT1.row_ID;
because ShippedDate does not provide a unique row_ID.
When you have identified the attribute, use it in a Query (VIEW) of the
data to calculate on demand rather than persist the result it in a
table.
