How to order the table?

Question

Using VB 6 and Access Database

My query.

Creating a table

sql4 = "CREATE TABLE tmp_MOI (RECORDNO varchar(20), PERSONID varchar(20), EmpName varchar(50), TitleName varchar(50), Titlecode varchar(50), Department varchar(50), Nation varchar(50), CardEventDate varchar(50), Intime varchar(20), Outtime varchar(20), PrevDate varchar(50), PrevOut varchar(20))"
If rstmpCardEvent1.State = 1 Then rstmpCardEvent1.Close
rstmpCardEvent1.Open sql4, Cn, adOpenStatic, adLockOptimistic

Selecting a fields from the table and saved into the recordset

sql9 = "SELECT RECORDNO, PERSONID, EMPNAME, TITLENAME, TITLECODE, DEPARTMENT, NATION, CARDEVENTDATE, INTIME, OUTTIME, (select TOP 1 F1.CARDEVENTDATE  from tmp_cardevent as F1 where F1.RECORDNO < F2.RECORDNO  AND F1.PERSONID = F2.PERSONID order by F1.RECORDNO  DESC, F1.PERSONID DESC) AS PrevDate, (select TOP 1 F1.OUTTIME  from tmp_cardevent as F1 where F1.RECORDNO < F2.RECORDNO  AND F1.PERSONID = F2.PERSONID order by F1.RECORDNO  DESC, F1.PERSONID DESC) AS PrevOut FROM tmp_cardevent  AS F2 ORDER BY F2.NATION, F2.TITLECODE, F2.PERSONID, F2.CARDEVENTDATE"
If rsCardEvent1.State = 1 Then rsCardEvent1.Close
rsCardEvent1.Open sql9, Cn, adOpenStatic, adLockOptimistic
cmdcardevent1.CommandText = sql9
Set rsCardEvent1 = cmdcardevent1.Execute

Inserting a fields into the table

While Not rsCardEvent1.EOF

sql10 = "INSERT INTO tmp_MOI values('" & rsCardEvent1(0).Value & "' , '" & rsCardEvent1(1).Value & "', '" & rsCardEvent1(2).Value & "', '" & rsCardEvent1(3).Value & "', '" & rsCardEvent1(4).Value & "', '" & rsCardEvent1(5).Value & "', '" & rsCardEvent1(6).Value & "', '" & StringToDate(rsCardEvent1.Fields(7).Value) & "', '" & rsCardEvent1.Fields(8).Value & "', '" & rsCardEvent1.Fields(9).Value & "', '" & StringToDate(rsCardEvent1.Fields(10).Value) & "', '" & StringToTim(rsCardEvent1(11).Value) & "') ORDER BY '" & rsCardEvent1(4).Value & "'"
If rstmpCardEvent1.State = 1 Then rstmpCardEvent1.Close
rstmpCardEvent1.Open sql10, Cn, adOpenStatic, adLockOptimistic
rsCardEvent1.MoveNext

Wend

When I check this query in the access database – It showing correctly, order by nation, titlecode like 001, 002, 003 so on…, personid, cardeventdate

Nation, personid, cardeventdate is showing correctly in the tmp_MOI table

But am not getting the title code properly in the tmp_MOI Table, it showing titlecode in the tmp_MOI table like 001, 002, 001, 003, 002 so on…

What wrong in my code, is any problem in my code?

Need query or code Help

Answer 1

First - I am not sure why you would have

ORDER BY '" & rsCardEvent1(4).Value & "'"

in the Insert statement when you are inserting in a recordset loop and in so doing inserting one row at a time.

Second - Do you have the right number of rows. Could it be that your joins are incorrect so that you are getting multiple rows for each titlecode?

Third -if you have the right number of rows, remember that RDBMS are designed so that the order of records in a table should be independent. So, if you SELECT without an Order By clause, there is no guaranteed order for the data. Add an Order By clause when you need to view the data.