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?

 

[AxeaXeaxE]

What kind of field do you want to put them in?

 

[mflancour]

I'd be interested in hearin the answere to ghis one as well.

 

[Ouch]

to all

this is how i did it...

users enter data in a field like so...

authors: bloggs j ; doe j ; etc..

the fuction takes each author and puts them into one row of a table

i will also be using it to split keywords, for searching etc..

eg..

keywords: athletics sport running women

by replacing the ; in the fuction with a space it will slit this field.





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

 

[mflancour]

What version of Access are you using...Split is not an option for me. (access 97)

Also what do you do with the three names once you split them? I meen do would you split them to display them on a form? If so, how do you grap the different portions of the field? ....Maybe you could use left([author],len(split[authors],";&quot) and mid() somehow?

 

[BSman]

OK, why do you want to do it this way? What's the maximum number of authors, for example?

Why not just have a separate table with one record for each author? Just have an internally generated ID for each title or book (let's call it bookID). You could even get more creative and have a separate table listing authors and an internal AuthorID number. Then your table for the book authors would only contain the bookID and the AuthorID.

By doing it with the separate table for the authors of each book (whether you use one or two additional tables as mentioned above), there is no limit to the number of authors and it's much easier to print the authors on separate lines as you mention or combine them together in a specific format on one line.

Also, you can much more easily search for works by a specific author. If you use the third table I mention (with the AuthorID and author names, etc.), you can be sure that the same author is identified the same way for all books, since you are not relying on the user entering the name of the author exactly the same way for every book.

 

[Ouch]

i would love to do it the correct and sensible way as above but its the users who are the problem.

they insist that they must enter the authors by typing them i one box seperated by a ; on the form

i am then going to seperate them into a seperate author table with an author ID by slpitting the feild and pasting it into the authors table, then link the book record with the author record using id link so i can search sensibly.

they will not select them from a list (although i WILL give them this option so hopefully they will get used to it and i can scrap the stupid author field)

 

[BSman]

Ah yes, users can be a real PIA (pain in the A**) when they aren't willing to try what will end up as easier and better. I presume you already gave them the pitch about spelling incorrectly, etc. I also assume (and suggest) that you only allow them that mode of entry for new records (be a little tough on them, while accomodating their request), so that when you display existing entries you use the multiple tables as you plan to set up.

Here are a couple of ideas:

1. Assume (i.e. require) that the special author input field have everything entered exactly correctly. This means that all you have to do is do an instring search for the semicolon and then add the author to your authors table if it's a new author. Then pick up the AuthorID and put it in your BookAuthors table with the BookID. The code to do this would be something like:

Dim strAuthors as String
Dim strOneAuthor as String
Dim intLength as Integer
Dim intPlace as Integer
strAuthors = me.Authors
NextAuthor:
' me.Authors is the input control for the users to enter
' the authors separated by a semicolon and a space after
' the semicolon.
intLength = Len(strAuthors)
If LEN(IntLength) > 1 then
intPlace = INSTR(1,strAuthors,";&quot
If intPlace > 0 then
strOneAuthor = MID$(strAuthors,1,intPlace - 1)
' put your code to add the author to the authors table
' and add to the BookAuthors table here
' Note that there must be a space after the semicolon
strAuthors = mid$(strAuthors,intPlace + 1,100)
Goto NextAuthor
else
' we are at the last author entered in the text control
strOneAuthor = strAuthors
' here add the code for Authors and BookAuthors tables
' and now we're done
end if
end if


2. If you wanted to get fancier, when you add an author to the Authors table, look for similar names and if there are any, ask the user to verify that it's a new author or, if not, to select from the existing list of authors. This added code might help convince them of the validity of the "right" method with the separate tables.


Good luck....