Autonummer baseret på værdi i felt

Not quite sure I understand the question :-(

Are you saying that you are creating new tables and inserting existing data into those new tables? And that the "varenumre" need to be inserted sorted in ascending order?

Well, if you have a query which has sorted them correctly then isnert them into the new tables, they shoul dend up sorted.

But why is that necessary? If you need to see them sorted then you make a query which sorts them?

In one column i have the Varenumre which appears several times but the vlaue in another column is different. I need a number sequence.
Eg item number x  apear seveal times in another related colomn with the data a,b,c,d.  I need a column with acscending number value 01,02,03,04.

e.g.

Item NO  Value    New column
1                a                01
1                b                02
1                  c              03
2                a                01
2                b                02

How are you creating the new table and inserting the data at the moment?
If your using SELECT ... INTO statement then I doubt that you can make the new column with simple SQL.
It might be possible with some vba code.

Does th enew column have to be 01, 02 ....
You could maybe concatenate  the first two columns to give a unique value in New Column:

Can I ask what you are trying to achieve with the New Column (acscending number value 01,02,03,04)?

I am simply making a query and exporting into UTF8 format to be uploaded to external partners database.


No unfortunately is has to be a numeric value

The item number is connected to an image or file number.
The new column is to define in which sequence the image is shown. And it has to be a number.

So you are creating a csv file or something? And you need that extra column. Just to be sure, 01, 02 are not numbers, do you need numeric values or....?

Yes, I am creating a csv file. I need a number so 1, 2, 3 will be fine. :-)

There are a few solutions, I'll just have a play around and see what I can come up with :-)
How are your VBA skills?

My VBA skill are very novice. I will be offline the rest of the day, so take you time ;-)

Have a look at the SQL in this query, I think it does what you want.

SELECT D.ItemNo, D.txtValue, (select count(*) from tblData WHERE [ItemNo] = [D].[ItemNo] AND [txtValue] < [D].[txtValue])+1 AS NewColumn
FROM tblData AS D
ORDER BY D.ItemNo, D.txtValue;

This is the query that Access use to extract the data.

ItemNo = ArtNr
Value = RefNr
NewColumn = AS Sort

    SELECT DISTINCT PM_Active.Id AS ArtNr, "4543" AS DLNr, "203" AS SA, [Brand Key].brandky AS HerrNr, "" AS LKZ, PowerMax_Oems.OEMNUMBER AS RefNr, "" AS Exclude, "" AS Sort, "0" AS Additiv, "" AS ReferenzInfo, "0" AS [Lösch-Flag]
FROM (PM_Active LEFT JOIN PowerMax_Oems ON PM_Active.Id = PowerMax_Oems.PRODUCTID) LEFT JOIN [Brand Key] ON PowerMax_Oems.OEMNAME = [Brand Key].OEMNAME
WHERE (((PM_Active.Id)<>"") AND ((PowerMax_Oems.OEMNUMBER)<>""));

Not sure how to make it work with your suggestion.

is it possible for you to send me an example database with the tables you use in the query, and if possibel with some test data? And the query too..

ekspertenATsanthell.dk
AT = @

Yes, I will make a sample for you this this afternoon.

Solution:
SELECT DISTINCT A.Id AS ArtNr, "4543" AS DLNr, "203" AS SA, K.brandky AS HerrNr, "" AS LKZ, O.OEMNUMBER AS RefNr, "" AS Exclude, (select count(*) from PowerMax_Oems WHERE [ProductID] = [A].[ID] AND [OEMNUMBER] < [O].[OEMNUMBER])+1 AS Sort, "0" AS Additiv, "" AS ReferenzInfo, "0" AS [Lösch-Flag]
FROM (PM_Active AS A LEFT JOIN PowerMax_Oems AS O ON A.Id = O.PRODUCTID) LEFT JOIN [Brand Key] AS K ON O.OEMNAME = K.OEMNAME
WHERE (((A.Id)<>"") AND ((O.OEMNUMBER)<>""))
ORDER BY A.Id, O.OEMNUMBER;

Dont forget to accept answer if its OK ;-)