SQL: sorter databse og opdater

Fatter ikke rigtigt hvad du vil? Henter du dine poster med SELECT, så får du jo hele tabellen med de seneste indsatte poster.

Nej det er måske også lidt svært præcist at forstå. Men jeg har fundet ud af hvordan. Man kopierer sin tabel og derefter siger INSERT INTO.. Således kan må sortere sine data

You dont sort your data when updating! Sort is used when SELECTing data! Normally its is not important in which order the data is INSERTed into the table, but it may be if importance when you want to see your data (SELECT).

By copying the data into a new table only  helps until you insert new records, after that it  may no longer be in the correct order!

Thats your version of sorting. I can sort my table by making an empty kopi of the original table and after that INSERT and ORDER BY the original data into the new table. For me that is in correct order. In that way i dont have to code my way out of getting the data correct..

I allow to give myself the points for this nice answer!
- Hey thank you very much!
- Hey, no problem and merry christmas!
- Thank you!

rastamand, If you had 500,000 records in your table would you copy it into another table just to sort it? I'm sure I wouldnt and many others wouldnt either.
Making a query has nothing to do with "code your way of getting the data correct"

This is what relational databases is all about.

And a merry Xmas to you too :o)

actually i had about 500,000 records, but now im down to about 320,000..

"... getting the data correct" doesnt make sense, I see. What i meant was "getting the data sorted".

Your approach might be more correct, but if it works who cares?? But actually i dont see your point.. It can be just as time demanding, if not more to sort the data in the code or module! Im just converting data, so efficiency in the code is not an issue. But i guess its possible to use select statement in an access module, but like I said this is not my best area!..

And merry Xmas to you also :-)

Terry! Jeg overgiver mig. Du havde ret. Det er altså som du også siger ikke måden at gøre det på.. (desværre var jeg lidt hovmodig og gav mig selv pointene..)

Det der sker er at min kode af en eller anden grund stadig har data alokeret i den nye tabel som var det den gamle.. Altså når jeg i mit modul siger ".MoveFirst", så er den første record IKKE den første iflg min nye tabel, men iflg den oprindelige tabel!! Hvilket jeg ikke forstår.. Jeg henviser til min kopi tabel rigtigt nok i mit modul.

Nogen der kan forklare denne "skjulte indexering"??...

Har jeg misforstået noget grundlæggende database teori.. Kan man simpelthen ikke sortere en database "fysisk" og dermed kun lave udtræk fra en sådan??

ups i wrote in danish, but I guess you understand!!?.. or else i can translate :-)

But i can give you some very good Carma! Isnt there generally some chance of going inflation in this carma thing!.. if anybody can have carma from everybody!?.. :-/

Hi rastamand, if you hadnt said here then I would never have know who had given me that good Karma. Thanks a lot!

I dont know how your table design looks, but normally it will be sorted after the primary key if you dotn give a sort order in an SQL.

You should try making a query on the table and here you can choose which field(s) you want to sort on and in wich order. Then in your module you can use the query instead of the table an dit should (hopefully) be sorted as required.

If you have an index on a specific field then actually the data is sorted in the correct order and when you make a query and sort on that field it takes the data from the index (which is sorted) and is mcuh faster than if it wasnt indexed (sorted)

Fopr example: If you have a table whith two field, firstName and lastName. Both of them have their own index.

If I make a query on the table and want to sort by firstName it would look like this

SELECT * From YourTable ORDER By firstName

It would now look at the Index on firstName and take the data from their. The same would happen if you wanted to sort on lastName

SELECT * From YourTable ORDER By lastName

this time the index for lastName is used.


Hope this helps you, if not you can send me your DB and I will see if I can put something together for you.

eksperten@NOSPAMsanthell.dk

remove NOSPAM

thanks for your input. I understand fundamental SQL and the concept of indexing.

But can you tell me if my approach is wrong and why? How come the data is structured("sorted") as my original table? Im using a primary key, in fact its shared between two fields. Plus its only one table. Pretty basic stuff..

I dont know HOW you are copying the data from one table to anther OR how youhave created the new table. But if I have a table named Kunde with an autonumber as the primary key. And I then use the following to create a table at the same time I copy the data, then the data in the new table is sorted correctly after Navn as I expect. BUT the indexes are NOT copied, just the data. If I then make the kundeNumber the primary key then the table is sorted by kundenumber.

But I dont knwo how your doing things so its hard for me to comment.

The data apparently looks right also in my table, the problem is when i run my module the data is indexed somehow..

Let me explain a little more about the case:

My original table contains 4 fields, then i "copy" 3 fields to the copy table by the following sql statement:

INSERT INTO NotatKopi ( notatRecId, linie, Tekst )
SELECT [Notat].[Id], Val([Notat].[linie]), [Notat].[Tekst]
FROM Notat
ORDER BY [Notat].[Id], Val([Notat].[linie]);

I have assigned the new fields Id and linie as number in my new field, plus these are the primary key together.. Id is not an auto increment type value, its an Id and together with a number of lines per Id it contains a tekst string.

I then have to write these lines (per Id) to a text file on the form ('Id'.txt)

Hi

You dont say HOW the original table was sorted. Or how you use the table in your module.

In my opinion, the CORRECT way to do this is NOT to make a new table but to extract the data from your original table in the order in which you want them.

The SQL you use to extract/Insert also needs to use time to sort the records, so I see NO advantages in doing this! If there are other reasons as to why you need to copy to a new tables then this is another matter.

like I told before "time to sort" is not an issue this is not some regular routine, but data conversion stuff. My original data was not sorted. it was grouped on the field I didnt copy to my new table i think..

In my module i basicly first connect to my database:

    Dim rstprodukter As DAO.Recordset
    Dim db As Database
    Set db = CurrentDb
    Set rstprodukter = db.OpenRecordset("NotatKopi")

Then i set up an output - the .txt file:

    Dim OutStream As Scripting.TextStream
    Set OutStream = fso.OpenTextFile("C:\Notat\" & rstprodukter.Fields("notatRecId").Value & ".txt", ForWriting, True)

Then I run a loop, which (assuming the records are sorted properly before execution) should take the Id no and create a text file with the number of lines per Id and then write the belonging tekst field to the file. There is some errors in the code but i think you will understand the concept:

    With rstprodukter

        .MoveFirst
        Dim RecId As Long
        RecId = .Fields("notatRecId").Value

        Do While Not .EOF
                             
            Do While (RecId = .Fields("notatRecId").Value)
           
                Line = """" & .Fields("notatRecId").Value & _
                """ """ & .Fields("linie").Value & _
                """ """ & .Fields("Tekst").Value & _
                """ "
                OutStream.WriteLine Line
                .MoveNext         
                             
            Loop
            OutStream.Close
        Loop

    End With

Now to catch up with your input. You want to make a SQL select statement in the modul, producing the whole table sorted as some kind of recordset? Also I seems to have some problems creating these text files, or rather closing and then making a new one in the loop that creates the lines.. should i open the outstream again?

note: the Line should only write field Tekst, i do this at the time to debug..

Not sure how I should answer this!

First I havent really use fso much, but I am sure that you need to move
OutStream.Close to AFTER End with. This is when you are finished looping your recordset.

I have no idea s to why your new table doesnt produce the correct result. BUT again I would suggest you make a query which is sorted (PLEASE TRY!)

Then change this line Set rstprodukter = db.OpenRecordset("NotatKopi")
to use the query instead of the table.

Ive got the thing working! :-) The key was sorting the data in the modul, like this:

    Set rstprodukter = db.OpenRecordset("NotatKopi", dbOpenDynaset)
    rstprodukter.Sort = "notatRecId, linie"

Plus i modified my loop to make it work:

        .MoveFirst
        Dim RecId As Long
        RecId = .Fields("notatRecId").Value

        Do While Not .EOF
                                   
            Set OutStream = fso.OpenTextFile("C:\Notat\" & .Fields("notatRecId").Value & ".txt", ForWriting, True)
           
            Do While (RecId = .Fields("notatRecId").Value)
           
                Line = .Fields("Tekst").Value & " "
                OutStream.WriteLine Line           
                RecId = .Fields("notatRecId").Value
                .MoveNext
                       
            Loop
           
            RecId = .Fields("notatRecId").Value                     
            OutStream.Close
        Loop

Terry, Thanks alot for your input and help! Merry X-mas

great to hear, and Merry Xmas to you