Avatar billede ladelund Juniormester
30. april 2020 - 13:39 Der er 14 kommentarer og
1 løsning

Autonummer baseret på værdi i felt

Jeg har en stor Access database hvorfra jeg via forespørgsler genererer andre tabeller.
I en af kolonnerne forekommer datene varenummer flere gange efter hindanden.
Hvordan nummerer jeg disse således at ens varenumre nummerere fortløbende?
Avatar billede terry Ekspert
30. april 2020 - 14:03 #1
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?
Avatar billede ladelund Juniormester
30. april 2020 - 14:18 #2
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
Avatar billede terry Ekspert
30. april 2020 - 14:42 #3
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:
Avatar billede terry Ekspert
30. april 2020 - 14:45 #4
Can I ask what you are trying to achieve with the New Column (acscending number value 01,02,03,04)?
Avatar billede ladelund Juniormester
30. april 2020 - 14:48 #5
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
Avatar billede ladelund Juniormester
30. april 2020 - 14:52 #6
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.
Avatar billede terry Ekspert
30. april 2020 - 14:57 #7
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....?
Avatar billede ladelund Juniormester
30. april 2020 - 15:02 #8
Yes, I am creating a csv file. I need a number so 1, 2, 3 will be fine. :-)
Avatar billede terry Ekspert
30. april 2020 - 15:06 #9
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?
Avatar billede ladelund Juniormester
30. april 2020 - 15:09 #10
My VBA skill are very novice. I will be offline the rest of the day, so take you time ;-)
Avatar billede terry Ekspert
30. april 2020 - 15:57 #11
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;
Avatar billede ladelund Juniormester
01. maj 2020 - 08:59 #12
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.
Avatar billede terry Ekspert
01. maj 2020 - 09:11 #13
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 = @
Avatar billede ladelund Juniormester
01. maj 2020 - 09:27 #14
Yes, I will make a sample for you this this afternoon.
Avatar billede terry Ekspert
06. maj 2020 - 12:46 #15
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 ;-)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester