Inserting Data

[dandoescode]

I need to insert entries from one table into another, however a standard insert statement will not work. The output table is ordered under one field, and I want to insert the data so that it fits the ordering of a second field.


For instance the table might look like

1 Apple 3
2 Oarnge 5
3 Cherry 6

I want to add Watermelon 4 so that the table would be

1 Apple 3
Watermelon 4
2 Oarnge 5
3 Cherry 6

I'm sure that the solution is something trivial.

[Robeen]

Off topic here - but I couldn't resist!!
What's an Oarnge?


To your question:
Could you rephrase, please - or explain in more detail?

[R_Badger]

Dont use the table to view your data...

Tables are for storing data

Queries are for sorting, joining and differentiating your data

Forms are for displaying the data to users and for users to input

Reports are for displaying data on paper for users to output

So, to sort it, use an insert, create a query, then order on the appropriate field.

[dandoescode]

The problem is that if I try to simply do an input query and then perform an ordering on the second coloumn, I will have destroyed the ordering on the first coloumn.
What I'm really doing here is looking at two sets of data, and finding an output that describes there matches.

Set A
1 red
2 blue
3 purple
4 black

Set B

1 Yellow
2 blue
3 cherry
4 purple
5 black

now I can easily find those colors that match the ordering of Set A in Set B

C1 Coulour C2


1 red 2
2 blue
3 purple 4
4 black 5

what I want to do is enter into this table those which do not match

Yellow 1
1 red 2
Cherry 3
2 blue
3 purple 4
4 black 5

Does that make any more sense. It would be easy to simply append those that do not match to the end of the table, but that's not what I want to do.

[dandoescode]

I basically want to be able to insert a row at a specific position.

[dandoescode]

Option Compare Database

Private Sub go_Click()


' Initialize variables

Dim tableOne, tableTwo, fieldName  As String
Dim valueOne, valueTwo, strValueOne, strValueTwo  As String
Dim db As Database
Dim rstOne, rstTwo, rstDataOne, rstnoMatch, rstDataOutPut As DAO.Recordset
Dim match As Boolean
Set db = CurrentDb()

tableOne = Me.tableOne
tableTwo = Me.tableTwo
fieldName = Me.fieldName

idField = Me.ID

DoCmd.SetWarnings False

DoCmd.RunSQL ("DROP TABLE DataTwo;")
DoCmd.RunSQL (" CREATE TABLE DataTwo;")
DoCmd.RunSQL ("ALTER TABLE DataTwo ADD ID AUTOINCREMENT (1,1), Compared Text, Identifier Number, TableName Text, strValue Text ;")
DoCmd.RunSQL ("INSERT INTO DataTwo (Compared, Identifier, TableName, strValue) SELECT " & fieldName & ",  " & idField & ", '" & tableTwo & "', strValue FROM " & tableTwo & " ; ")



'Delete Tables from previous run and recreate new tables. DataOne holds the matched values from table Two
'DataTwo holds the values of TableOne, this may seem unneccisary but it allows to do a left join on the autoincrement, which is nice.


DoCmd.RunSQL ("DROP TABLE DataOutput;")
DoCmd.RunSQL ("DROP TABLE DataOne;")
DoCmd.RunSQL ("DROP TABLE DataThree;")
DoCmd.RunSQL ("DROP TABLE noMatch;")


DoCmd.RunSQL (" CREATE TABLE DataOutPut;")
DoCmd.RunSQL (" CREATE TABLE DataOne;")
DoCmd.RunSQL (" CREATE TABLE DataThree;")
DoCmd.RunSQL (" CREATE TABLE noMatch;")


DoCmd.RunSQL ("ALTER TABLE DataOutput ADD ID AUTOINCREMENT (1,1), TableName1 Text, Identifier1 Number, Compared Text, TableName2 Text, Identifier2 Number, strValueOne Text, strValueTwo Text ;")
DoCmd.RunSQL ("ALTER TABLE DataOne ADD ID AUTOINCREMENT(1,1), Compared Text, Identifier Number, TableName Text, strValue Text   ;")
DoCmd.RunSQL ("ALTER TABLE DataThree ADD ID AUTOINCREMENT(1,1), Compared Text, Identifier Number, TableName Text, strValue Text  ;")
DoCmd.RunSQL ("ALTER TABLE noMatch ADD Identifier2 Number, Compared Text, TableName2 Text, strValueTwo Text ;")


'Populate DataThree with the required elements of Table1
'Set Used = 0, Used allows us to see if a element of Table 2 has been matched or not.

DoCmd.RunSQL ("INSERT INTO DataThree (Compared, Identifier, TableName, strValue) SELECT " & fieldName & ",  " & idField & ", '" & tableOne & "', strValue FROM " & tableOne & " ; ")
DoCmd.RunSQL ("UPDATE " & tableTwo & " Set Used = 0; ")


DoCmd.SetWarnings True



Set rstOne = db.OpenRecordset(tableOne, dbOpenDynaset)
Set rstTwo = db.OpenRecordset(tableTwo, dbOpenDynaset)
Set rstDataOne = db.OpenRecordset("DataOne", dbOpenDynaset)

match = False
LastId = 0




        
       

        
            

        ' Setup Do Loop to cycle through the entries of the first table
        
        Do While Not rstOne.EOF And match = False
        
        
        
        ' Grab values from the first entry in both tables
        valueOne = rstOne.Fields(fieldName)
        idOne = rstOne.ID
        rstTwo.MoveFirst
        valueTwo = rstTwo.Fields(fieldName)
        idTwo = rstTwo.ID
        strValueOne = rstOne.strValue
        strValueTwo = rstTwo.strValue
       
        
        
        
        ' If the string values match, then write them into the output table, we dont have to worry about preserving order
        ' since this is the first entry
        
              
        
        
                 If valueOne = valueTwo Then
                                            
                
                    rstDataOne.AddNew
                    rstDataOne![Compared] = valueOne
                    rstDataOne![TableName] = tableTwo
                    rstDataOne![identifier] = idTwo
                    rstDataOne![strValue] = strValueOne
                    rstDataOne.Update
        
        
                     rstTwo.Edit
                    rstTwo![Used] = 1
                    rstTwo.Update
                    
               
                
              
                match = True
                LastId = idTwo
                                           
                                             
                End If
                
                
        rstTwo.MoveNext
                                     
        
        
        ' Setup Do Loop to look for the first match between the entry in the first table, and the entries in the second table
        
        Do While match = False And Not rstTwo.EOF
        
        
        
        
        valueTwo = rstTwo.Fields(fieldName)
        
        
        
        
        ' If we have a match then we want to check that the match preserves order, if it does then we want to write it to the output
        ' If there is no match that is order preserving we leave the field blank
      
        If valueOne = valueTwo Then
        idTwo = rstTwo.ID
                                        
                                       
                                        
                                        
        If LastId < idTwo + 1 Then
                                        
                                        
                                        
                                        
        
        rstDataOne.AddNew
        rstDataOne![Compared] = valueOne
        rstDataOne![TableName] = tableTwo
        rstDataOne![identifier] = idTwo
        rstDataOne![strValue] = strValueOne
        rstDataOne.Update
        
        
         rstTwo.Edit
        rstTwo![Used] = 1
        rstTwo.Update
        
               
        
        LastId = idTwo
        match = True
                    
        End If
        
        
        
                                    
                                     
        End If
        
        
        
        
        
        
        rstTwo.MoveNext
        
        
        
        Loop
        
        'We want to create an empty row in DataOne, this will show up as an empty entry in our Data Output when we join it to DataThree
        
        If match = False Then
        
        rstDataOne.AddNew
        rstDataOne![Compared] = valueOne
        rstDataOne.Update
        
        End If
        
        
        
        
        
        ' Set match back to false, and move on to the next entry in the first coloumn
        
        match = False
        
        rstOne.MoveNext
        
        Loop
        
        DoCmd.SetWarnings False
        'Opening the query Insert1 joins DataOne to DataThree.
     DoCmd.OpenQuery ("Insert1")
    
    
    
    
    
    
    
    
    
    
    
    
    
    
        ' We still have to take care of the unmatched elelments of DataThree. This query sens the unused elements to DataOutPut
    DoCmd.RunSQL ("INSERT INTO noMatch (Compared, Identifier2, TableName2, strValueTwo) SELECT " & fieldName & ",  " & idField & ", '" & tableTwo & "', strValue FROM " & tableTwo & " WHERE  Used = 0 ;")
    
    
    
    'What needs to happen here is I need to insert the pieces of noMatch into
    ' DataOutput, adhereing to the order of Identifier2, without changing the order of Identifier1
    
    
    
    
    
    
    
    
    
    DoCmd.SetWarnings True
   
        ' Open the output table for the data.
    DoCmd.OpenTable ("DataOutPut")
    
    
Set rstOne = Nothing
Set rstTwo = Nothing
Set rstDataOne = Nothing

Set db = Nothing

End Sub

[orange]

These

Dim tableOne, tableTwo, fieldName As String Dim valueOne, valueTwo, strValueOne, strValueTwo As String

do not work as you think.

I thought this looked familiar - I see I answered this part previously
https://www.accessforums.net/program...eld-25023.html

see post #13 at https://www.accessforums.net/access/...-do-24457.html

As for library references there was a discussion here
https://www.accessforums.net/program...der-25094.html

I think you're over reaching for some detail by attempting to insert a record at a specific position.
The database stores the record where and how it sees fit. You can order records in a query without knowing anything about
the storage used by the database system.