Combining field from multiple record into one field 1

[Ricardo1]

Hi

I'm using a access database in combination with visual basic 6 and the ms jet driver.

I've created three tables :
1) Authors (containing number and firstname, middlename and surname)
2) Books (containing number and title)
3) Link (containing booknumber, indexnumber and authornumber)
In this setup one book can be writen by more than one author.

What I'm trying to do is to create a view which contains the name of a book and in one field a combination of all the names of the authors involved.
For example : Title A - Author 1, author 2

I can't seem to find the correct SQL-statement(s) and/or functions within visual basic. Does anybody have an suggestion?

 

[lespaul]

Here's a thread with some links to functions that do what you are asking for:

Thread701-889682

Leslie

 

[Ricardo1]

Thank you for your suggestion, lespaul!

This looks helpfull. It's looks like this function is created within the mdb file. I would like to call this function through a sql statement which I submit through the jet database driver from visual basic (not the visual basic for applications within the mdb file but from a standalone visual basic version).

The sql would look something like this :
SELECT BOOKS.TITLE, (SELECT CONCATENATE(FIRSTNAME, MIDDLENAME, SURNAME) FROM AUTHORS WHERE AUTHORS.NUMBER=BOOKS.NUMBER) AS NAME FROM BOOKS

However when I do this, the jet driver tells me it doesn't recognize then CONCATENATE function. This gives me the impression that the function can't be called from outside the mdb file. Is this so? If not, what am I doing wrong?

 

[lespaul]

If you are using VB, why not just combine the information after you get the result set?
(some psuedo code - I usually code in Delphi, so it won't be exactly right, but you should get the idea)

strTitle = ""
while not recordset.eof do
  if Field("Title") <> strTitle then
    strTitle = Field("Title")
    strAuthor = Field("Author")
  else
    strAuthor = strAuthor & ", " & Field(Author)
  end if
next

now strAuthor will be a comma separated list of all the authors on the book, I usually put something like this into an array or grid for easy use.

I don't know about being able to call that function from outside an Access database.

Maybe someone else will know.

HTH

Leslie

 

[Ricardo1]

Leslie,

What you suggest seems rather logical, however I spend some thought on this and came up with the following problem :
Normally when I open a database through the jet driver only the first x records (normally 50) are read into memory. This saves time and resources. When I do as you suggest all records will be read into memory. Offcourse a way around this can always be found, but, if I can avoid it, I rather don't do this.
Do you know any way to call this function from outside access?

 

[OhioSteve]

This is a cool question because there is not an easy answer. Please review my sample database and my solution:

table Authors
field Lname
field Fname
field Title
author_ID pk, autonumber

table Books
book_ID pk, autonumber
title
authorString

Initially, authorString will be blank. Then I use a macro to with 3 update queries to populate it.

UPDATE books SET books.authorString = Null;

UPDATE books INNER JOIN (authors INNER JOIN junction ON authors.author_ID = junction.author_ID) ON books.bookID = junction.book_ID SET books.authorString = authors!Lname & ", " & books!authorString;

UPDATE books SET books.authorString = Mid(books!authorString,1,Len(books!authorString)-1);

The first sql nukes the field. The second one populates the field with a value like "Smith, Jones, Johnson,". The third query removes the last comma.

 

[lespaul]

With this set up you will have searching issues! Look through the fora and see how many people are asking for help on how to:

I want my users to be able to enter Smith Jones Johnson and be able to find all the books where any of them are the author:

So I want to find:


Smith, Jones, Johnson
or
Jones, Smith, Johnson
Or
Johnson, Smith, Jones
OR
Jones
OR
Smith
Or
Johnson
OR
Jones, Smith
OR
ad infitinum

With a normalized struture where each record contains information about a single item, these kinds of situations rarely occur.

Leslie

 

[Ricardo1]

Leslie,

If I'm not mistaking a function like instring is available in SQL. When using it, it will return the position of some text within some other text. If you use it and check whether the return value is larger than 0 you'll probably be able to find them all! You might however have to use some separator character in the field to make sure you don't get things like the name johnsons when you're only looking for johnson.

 

[Ricardo1]

Steve,

It looks like we are getting somewhere! Offcourse I'll have to check whether using this doesn't hurt performance too much (it probably will have to be executed serveral times per hour on a few thousand records!). However I have a small problem to add to the discussion. The program will be running on more than one pc at the same time, using the same database. Also I have to produce the names involved in more than one format, for example : Johnson, Paul but also Paul Johnson. When using the same field in a table, I probably will have different users messing thing up for each other! Do you have any ideas on solving this?

 

[OhioSteve]

Here are my comments on your concerns:

1) Performance issues. My solution is extremely inefficient, so performance is a potential problem. You may want to run the macro at night, when the workers are gone. There are several ways to automatically kickoff a task at night.

2) style variation. If there are logical rules for the syle variations, then you should be able to handle them. Remember that update queries can have elaborate conditions. Post your rules if you want help with them.

3) multiuser woes. In my experience, MS Access is NOT ideal for a multiuser database. However, if you must use MS Access then you should consult one of the many good Access books. You will find many chapters on multiuser stuff.

PS: Thanks for the star!