Append to table

[hendrikbez]

The following people help me to understand what is going on in access (Ajax, Accesstos and orange) thank you guys.

BUT.......

I have two tables in my access form, One is the main table (tblSynonyms), that have all the data in it, and the second table (tblSynonymsADD).
Thew second table is data that I have import to sccess.

I know how to append and update date form one table to a other table, but I cannot figure out on how to do this one.

On table (tblSynonyms) I have 3 columns "WordPK,TheWord and GroupFK"
Now I want to import new words to this table, but are not sure on how to to this. I did attahed a zip file for you to see what I mean.

[mike60smart]

Hi

So do you want to append both Field1 and Field2 from tblSynonymsAdd ?

[hendrikbez]

Hi

So do you want to append both Field1 and Field2 from tblSynonymsAdd ?

You can see that in tblSynonyms the is words with the same GroupPK number (word that have the same meaning), so tblSynonymsAdd (field1, field2, field3 eg) when append to tblSynonyms must all have a new groupPk number

[orange]

Hendrik,

In a step by step approach, tell us exactly what should be done with this record in tblSynonymAdd

Field1	Field2
plagiarisse	letterdiewe

which in English translates to
"plagiarize", "letter thieves".

What determines the Group? Details please.
Clarity is critical to getting focused advice.

[hendrikbez]

Hendrik,

In a step by step approach, tell us exactly what should be done with this record in tblSynonymAdd

Field1	Field2
plagiarisse	letterdiewe

which in English translates to
"plagiarize", "letter thieves".

What determines the Group? Details please.
Clarity is critical to getting focused advice.

1. I need the words in all the fields (on one row) to be appended into table tblSynonym, with the next grouppk number that is to be used (it must look what the last number was in tblSynonym)
2. so field1 (plagairisse) field2 (letterdiewe) in table tblSynonymadd must then be added in tblSynonym (with qry) with new grouppk number eg1234.
3. tblSynonym must be appended with the new words then.

the grouppk, give number for all the words that have the same meaning, so each row in tblSynonym must get new grouppk number.

[mike60smart]

Don't know about you orange but I am baffled

[orange]

OK. A few things to consider and confirm:
-the new records do not yet exist in table synonyms (did you check or do you need a routine to check)?
-there is no requirement from the database that the groupfk is sequential --BUT it must be unique in the table.

???
What determines the Group? Details please, Hendrik.
Clarity is critical to getting focused advice.

In your example show us what you expect with the record I mentioned.

WordPK   TheWord  Groupfk

[hendrikbez]

ok will try again.

This words are not in table tblSynonym yet. At the moment I am cleaning up the table manually if I found words that are in more than once.
Not sure about your second line.


1. On my access database I have a table tblSynonym and a form to add one new word to the table. If I have two words with the same meaning and I found a third word, I can add it to the list, it will have the same groupfk as the other two words.

2. I have put more than 100 new rows with words in a excel sheet. this words is not in table tblSynonym at the moment

3. I am going to import this excel into a table tblSynonymADD.

4. I need to make a query to press on append then Run to add this words to table tblSynonym.

5. on tblSynonym I have 3 column/fields
ID=Wordpk (every word will have a number)
Word (every word will be in there)
Groupfk (all words with the same meaning will have the same grouppk number (can be 2 at most, but some can be 30 words of the same meaning.)

6. I need to convert table tblSynonymADD to look like table tblSynonym to a new table tblSynonymappend
7. Then the quary must add table tblSynonymappend data to table tblSynonym
8. The groupfk number must start from last groupfk number in table tblSynonym.
9. A am going to do this every time I have more than 20 new words in the future.

Wordpk                 Word                   groupfk

 new auto number       plagiarisse              new groupfk number  eg 1201
 new auto number        letterdiewe                           1201

[accesstos]

Hi Hendrick!

You have to add a new field in table "tblSynonymsADD" to keep the new group ID and you can create a UNION query to transpose the new words in one column.

Run the procedure below, in your attachment, and check the results.

Sub AppendWords(Optional strFromTable As String = "tblSynonymsADD", Optional strToTable As String = "tblSynonyms")
    Dim db As dao.Database
    Dim rsNew As dao.Recordset
    Dim i As Long
    Dim strSQL As String

    On Error Resume Next
    Set db = CurrentDb
    'Append the NewGroup field if not exists.
    db.Execute "ALTER TABLE " & strFromTable & " ADD COLUMN NewGroup Long", dbFailOnError
    On Error GoTo ErrH
    
    Set rsNew = db.OpenRecordset(strFromTable, dbOpenDynaset)
    'Retrieve the max GroupFK.
    i = Nz(DMax("GroupFK", strToTable), 0)
    'Fill in the NewGroup.
    With rsNew
        While Not .EOF
            i = i + 1
            .Edit
            !NewGroup = i
            .Update
            .MoveNext
        Wend
        'Prepare the SQL string.
        'Transpose the new words from "columns to rows" by a UNION query.
        For i = 0 To .Fields.Count - 1
            If .Fields(i).Name <> "NewGroup" Then
                strSQL = strSQL & " UNION ALL SELECT " & .Fields(i).Name & " AS NewWord, NewGroup FROM " & strFromTable
            End If
        Next i
        strSQL = Mid(strSQL, InStr(1, strSQL, "SELECT"))
        strSQL = "SELECT n.NewWord, n.NewGroup FROM (" & strSQL & ") AS n LEFT JOIN " & strToTable
        strSQL = strSQL & " ON n.NewWord = " & strToTable & ".TheWord "
        strSQL = strSQL & " WHERE " & strToTable & ".TheWord Is Null"
        strSQL = "INSERT INTO " & strToTable & " (TheWord, GroupFK) SELECT NewWord, NewGroup FROM (" & strSQL & ")"
        'Debug.Print strSQL
    End With
    'Append the new words to the synonyms table.
    db.Execute strSQL, dbFailOnError
ExitHere:
    On Error Resume Next
    rsNew.Close
    Set rsNew = Nothing
    Set db = Nothing
    Exit Sub
ErrH:
    MsgBox Err.Description, vbExclamation, "Append Words (Error: " & Err & ")"
    Resume ExitHere
End Sub

Cheers,
John

[hendrikbez]

@accesstos/ John

Thank you, just stupid question

The extra field should be called groupfk and were do I put this code. in a button or main page. not sure about where

[accesstos]

Paste this code in a standard code module and -for now- test it from immediate (write AppendWords in immediate window and push Enter).
If all are OK, then you can run it from anywhere you want (button, other procedure etc).

The extra field should be called groupfk?

The code creates the extra field (with name NewGroup) automatically if not exists.

[accesstos]

I'll give one more attempt.

Hendrick,

To add the extra field with the group values in the tblSynonymsADD, paste this code in a code module of your example and run it.
You can run it directly from the menu "Run" of VBE or pushing F5.

Sub FillNewGroups()
    Const strFromTable As String = "tblSynonymsADD"
    Const strToTable As String = "tblSynonyms"
    Dim db As dao.Database
    Dim rsNew As dao.Recordset
    Dim i As Long

    On Error Resume Next
    Set db = CurrentDb
    'Append the NewGroup field if not exists.
    db.Execute "ALTER TABLE " & strFromTable & " ADD COLUMN NewGroup Long", dbFailOnError
    On Error GoTo ErrH

    Set rsNew = db.OpenRecordset(strFromTable, dbOpenDynaset)
    'Retrieve the max GroupFK.
    i = Nz(DMax("GroupFK", strToTable), 0)
    MsgBox "New groups starts from " & i + 1, vbInformation, "Fill new groups"
    'Fill in the NewGroup.
    With rsNew
        While Not .EOF
            i = i + 1
            .Edit
            !NewGroup = i
            .Update
            .MoveNext
        Wend
        MsgBox .RecordCount & " groups filled in succesfully up to " & i, vbInformation, "Fill groups"
    End With
ExitHere:
    On Error Resume Next
    rsNew.Close
    Set rsNew = Nothing
    Set db = Nothing
    Exit Sub
ErrH:
    MsgBox Err.Description, vbExclamation, "Fill new groups (Error: " & Err & ")"
    Resume ExitHere
End Sub

Now, the query below returns the new words in one single field and the corresponding new group values:

SELECT Field1 AS NewWord, NewGroup FROM tblSynonymsADD 
UNION ALL 
SELECT Field2 AS NewWord, NewGroup FROM tblSynonymsADD;

The query below restricts the new words that exists in the tblSynonyms:

SELECT n.NewWord, n.NewGroup FROM 
(SELECT Field1 AS NewWord, NewGroup FROM tblSynonymsADD 
UNION ALL 
SELECT Field2 AS NewWord, NewGroup FROM tblSynonymsADD)  AS n 
LEFT JOIN tblSynonyms ON n.NewWord = tblSynonyms.TheWord
WHERE (((tblSynonyms.TheWord) Is Null));

Finally, the query below appends the above records in the tblSynonyms:

INSERT INTO tblSynonyms ( TheWord, GroupFK )
SELECT nw.NewWord, nw.NewGroup
FROM (SELECT n.NewWord, n.NewGroup FROM 
(SELECT Field1 AS NewWord, NewGroup FROM tblSynonymsADD 
UNION ALL 
SELECT Field2 AS NewWord, NewGroup FROM tblSynonymsADD)  AS n 
LEFT JOIN tblSynonyms ON n.NewWord=tblSynonyms.TheWord 
WHERE tblSynonyms.TheWord Is Null)  AS nw;

I hope helps.
Good Luck!

P.S.: The above steps are executed by AppendWords() procedure of my previous post.

[hendrikbez]

Hi John

I have got the first part to work like i should. Will look at the rest today.

[hendrikbez]

John

Thank you it is working
But how can I see all the words that I want to add, and it is already in the main table tblSynonyms. can it show me a list of them

[accesstos]

John

Thank you it is working
But how can I see all the words that I want to add, and it is already in the main table tblSynonyms. can it show me a list of them

Hi Hendrik!

Create a new query with this SQL expression:

SELECT TheWord, GroupFK, NewWord, NewGroup FROM tblSynonyms 
INNER JOIN (SELECT Field1 AS NewWord, NewGroup FROM tblSynonymsADD 
UNION ALL SELECT Field2 AS NewWord, NewGroup FROM tblSynonymsADD) AS n 
ON (tblSynonyms.TheWord = n.NewWord);

Cheers