Adding the row number or a similar sequence column in a Query 1

[smopitz]

I am running a query that is re-sorting (reprioritizing) a simple work order table based on the table’s 4 priority-setting fields. How can I add a new column to the query that is an incrementally-numbered sequence - similar to pulling the row number? The "Work Order" id field originally was an autonumber primary key, but I want to replace it with this "sequence" field as the new primary key.

I've read various FAQs but don't find exactly what I'm looking for, and I think it's because i'm using ORDER to resort so many fields. Or it's because I'm obviously not a programmer.

Here is my current Code:

SELECT Scheduling.[Work Center ID], Scheduling.[BackorderDollars], Scheduling.Startdate, Scheduling.[PRIORITY DESCR], Scheduling.[Work Order]
FROM Scheduling
ORDER BY Scheduling.[Work Center ID], Scheduling.[BackorderDollars] DESC , Scheduling.Startdate, Scheduling.[PRIORITY DESCR];

All I want to do is add one more column called "Sequence" and use it as the new Primary Key.

Thanks in advance.

 

[Remou]

Have a look at the FAQS for this forum there are at least two suggestions.

 

[smopitz]

Thanks, You're right Remou, and I apologize for my oversight. my problem is that my coding experience in Access is terrible, and I don't know how to add a function like the one suggested into Queryesign View.

global IncrementVariable as Long

function IncrementValues(i) as Long
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
end function

Can you give some quick suggestions?

 

[Remou]

Add it as if it were a field in the query:

IncrementValues(i)

Once you have added the function name to the query design grid, you must change i to the name of your unique ID, as far as I recall.

 

[smopitz]

Now I get the following error:

Undefined Function 'IncrementValues' in expression.

 

[Remou]

Have you pasted the code into a module?

 

[smopitz]

Thank you Remou! I never knew what a module was before, but now read up on it, and pasted the code in there, and now the query worked perfectly! Thank you for your patience! This is opening up a whole new world for me in Access.

 

[smopitz]

This incremental field has been working great the last few weeks! But it gets into the 1000's by the end of the query. Now I'm hoping to add one more feature. Is there a way to make the incremental column reset back to "1" each time the 2nd column changes data? The second column, [Work Center ID], only has eight possibilities and is sorted ascending, so the incremental column won't reset more than 8 times throughout, but will make referencing a lot easier. Do you have any suggestions? Thank you in advance.

 

[Remou]

In that case, try something like:

[tt]SELECT A.ID, A.[Work Center ID], (SELECT Count(*) FROM tblTable WHERE [Work Center ID]=A.[Work Center ID] AND ID>=A.ID) AS [Counter]
FROM tblTable AS A
ORDER BY A.[Work Center ID][/tt]

--

http://support.microsoft.com/kb/208946