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
Best Answer
First - I am not sure why you would have
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.