multiple data in one field

[Ouch]

help...

i am designing a database and what i want to do is keep more than one author in a single field in a record but then query that field to seperate the autors out.

eg.

Title: how to be somebody

Authors: bloggs J ; doe B ; bathgate B

then i want to create a query wich lists authors seperately

eg.

Authors
bloggs J
doe B
bathgate B

can anyone help?

 

[LittleSmudge]

Classic RDB problem

What you SHOULD do is create a second table with the names of the Authors in it, one Author per record along with a Foriegn Key field that will store the ID of the 'Book' record from the Book table

On the Form on the screen you can then easily concatenate all of the names from the names table into one field when you want to - or you can display then in a list as you mentioned. ( Reports work the same way )


If you really-really MUST do it this way then:-
Dim strOneName As String
Dim strListOfNames as String
Dim intPosition As Integer
strListOfNames = FullListFromTable

While InStr("ListOfNames",",&quot > 0
intPosition = InStr("ListOfNames",",&quot
strOneName = Left(ListOfNames,intPosition-1)
ListOfNames = Trim(Right(ListOfNames,Len(ListOfNames)-intPosition))

'Add strOneName to display
'Increment display
Wend




'ope-that-'elps.

G LS

 

[Ouch]

its for journal articles which cah have more than one author for each article

this is how i did it...

Private Sub Command44_Click()

Set Dbs = CurrentDb
Set rst = Dbs.OpenRecordset("tbllookups&quot
Dim x As Variant
Dim i As Long

x = Split([authors], ";&quot
For i = 0 To UBound(x)
rst.AddNew
rst![Data] = x(i)
rst![tpref_id] = "AUT"
rst.Update
Next i

End Sub

it's not the best way and will probobally cause me problems in the future but the customer wants to type all the authors of an article in one field.

 

[Bastien]

Hi

So the customer wants to type all the authors into one field? So what? Create an unBound field for the authors, then split the names, then do the insert into the table like the one suggested above by LittleSmudge...

hth

bastien Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[LittleSmudge]

I worked on a database a year or so ago that was collecting academic research papers, summarising and reporting on content etc. There, we were loaded with problems to do with multiple authors etc. We were storing Phd thesis, journal articles, books, conference papers et. al.

I gave the data input user fields to add Surname, Firstname, OtherInitials, Title ( Dr, Prof, etc ), Organisation.
When user clicked 'Complete' this data was stored in a separate table as described above, then the text concatenated with the correct ( & hence automatically consistent ) punctuation added into one long string displaysed in a long text box control.
User could then add more authors or move on.

When user returns to this document's record the text string is once again calculated and displayed. VB manipulates strings quite quickly so the recalulation is not a significant overhead.


G LS