sql-query

  • Thread starter Thread starter Axel Müller
  • Start date Start date
A

Axel Müller

Hello everybody,
I have in my dictionary-db two fields. Now I would like to reduce the number
of rs and
to avoid the same words:
Field1 Field2
man human being
man person
man male or female

I would like to have it in this way:
Field 1 Field2
man human being, person, male or female
How to implement this?
Axel
 
What you wish to do is not recommended. A relational database should have
the structure that you already have: one field with one value, etc.

You can always display concatenated results such as you want via queries and
via manipulating recordsets. What you seek to do will make things much more
difficult for parsing out the Field2 information.
 
Hello Ken,
maybe you did not understand me. What I want to do, only once to do is to
"compress" my db in the way that every word with more meanings gets
all this meanings in the same row. This is a natural structure of
dictionaries.
The present structure in the actual dictionary is
First row: the same word - first meaning
Second row: the same word - second meaning
Third row: the same word - third meaning

I would like to pick up first, second and third meaning and connect them
with the same word in the new table.
The result should be
First row: The same word - first, second, third meaning.

Afterwards this job is done, I do not need the old table any more.
If it is not possible to do this, then I must write it manually, for 120000
records!
Axel
 
It's possible to do. My reply was meant only to state that it's not
necessary to do this in your tables. Leave them the way they are and
manipulate the way you display them.

Otherwise, I would use VBA code to do what you seek. Something along these
lines.....

First, create the new table that is to receive the new records and data.

Second, use code similar to this to run the process:

Public Sub ConcatenateRecords()
Dim rstOld As DAO.Recordset, rstNew As DAO.Recordset
Dim dbs As DAO.Database
Dim strOldWord As String, strNewWord As String
Dim strDefinition As String
Dim strSQLold As String
Set dbs = CurrentDb()
strSQLold = "SELECT * FROM OldTableName ORDER BY Field1;"
Set rstNew = dbs.OpenRecordset("NewTableName", dbOpenTable, dbAppendOnly)
Set rstOld = dbs.OpenRecordset(strSQLold, dbOpenDynaset, dbReadOnly)

With rstOld
.MoveFirst
strOldWord = !Field1
Do While .EOF = False
strDefinition = strDefinition & IIf(strDefintion = "", "", ", ")
&!Field2
.MoveNext
If .EOF = True Then
Exit Do
Else
strNewWord = !Field1
If strOldWord <> strNewWord Then
rstNew.AddNew
rstNew!Field1 = strOldWord
rstNew!Field2 = strDefinition
rstNew.Update
strDefintion = ""
strOldWord = strNewWord
End If
End If
Loop
End With

rstNew.AddNew
rstNew!Field1 = strOldWord
rstNew!Field2 = strDefinition
rstNew.Update

rstNew.Close
Set rstNew = Nothing
rstOld.Close
Set rstOld = Nothing
dbs.Close
Set dbs = Nothing

End Sub
 
Thank you very much, Ken! It works very well!
That is the solution of my problem!
Axel
 
Back
Top