Nummerér fra 1 i hver gruppe

if you only have the fields you show in the png file then its going to be difficult finding a solution which uses an update query only. And I'm not sure using code will be easier either.

The problem is how to sort the table in the first place so that
you get one SubItem followed by 2 CostCode

Sharp as always Terry.

I have a lot of other fields.
I think I have the sorting under control.
I can make a field [SortID] with numbering that will give the right sorting.

Your welcome to send me an example of the dB and I'll see if I can find a solution, but no guaranties :-)

ekspertenATsanthell.dk
AT = @

Awesome. I will send you an email and hope for the best.

Got the rar file unpacked.

After looking at the data I've made a query which is very close to what you want.

SELECT VSOutput.NewREDSUBclass, VSOutput.Sitem, VSOutput.Type
FROM VSOutput
ORDER BY VSOutput.NewREDSUBclass, VSOutput.ID, VSOutput.Type DESC;


But I can see that Sitem although sorted correctly doesn't always start from 1.
So, my question is, if you make an update query so we start from 1, what about new data getting appended to the table, how will you ensure these are numbered correctly?

Forget to ask. I have had to use ID in the sort. Is this OK or can it give problems later?

The Sitems not starting with 1 is wrong data, ignore it.

New data added to the table is not an issue, the data is to be imported into another system.

Using ID is perfectly OK.

Have you tried the SQL I gave? If I understand you correctly, those where Sitem doesn't start with 1 are to be ignored, then the sItems are 'almost' correct without update. I say 'almost' because I need to check something.

Can you tell me how the ID which is an autonumber has records with same number?

You are right, it is kind of strange. The ID field is imported from several other tables. There is not an truly autonumbered field at the moment. 

The SQL you gave is only sorting the table as I see it(?).

The Sitem values for rows of Type SubItem should be overwritten by the update query, starting from 1 in each group  followed by 2 and 3 etc. in the right order defined by the sorting of the table.

I think this query gives you what you want but it doesn't update the table.

SELECT VSOutput.NewREDSUBclass, VSOutput.Type, IIf([Type]='SubItem',(SELECT count(*)+1 from VSOutput AS V where [V]![NewREDSUBclass] = [VSOutput]![NewREDSUBclass] AND [V]![Type] = 'SubItem' AND [V]![ID] < [VSOutput]![ID]),'') AS Sitem
FROM VSOutput
ORDER BY VSOutput.NewREDSUBclass, VSOutput.ID, VSOutput.Type DESC;

Adding a new autonumbered field to keep the sorting is possible but not done yet.

How can I make it update the table?

Sorry for my lack of Access skills :-)

Not a straight forward way to update table. Normally it would be possible, but because there is a calculated field in query then the table is not updateable.

I'll just try something and get back ...

It seems like the query does the job. I can merge the data in the query using my basic skills. Thanks. Please drop an answer, I would love to give you the points.

There isn't a unique key on the table, probably why we cant update. So I tried to change ID which is an autonumber to a long integer with the intention of making an new ID field as primary key. I cant, Access wont let me.

So a suggestion is to make a new table but with an new ID (Name it CID)(autonumber) and old ID change to integer (long).

Then alter the query I have made to select all fields and then an insert query to insert into new table.
Obviously use new sitem field

Tusind tak!

selv tak