Limiting search returns

[SueBK]

My table has (among others) fields "book ID" (autonumber) and "title". Each title has only one record in the table, but may have more than one author - picking up from another table.

I have a search form that allows users to enter data in various text boxes to return all the titles in the database that match their query. The form format is that the search boxes are in the form header, and the results are returned in the details section (if that makes any difference). The "Book ID", "title" and "author" are all returned in the search results.

At the moment if a title has more than one author an entry is returned against each author.

Is it possible to limit the results to just one 'copy' of the title? and combine the multiple authors into a single return.

For example:
At the moment I get:

ID TITLE AUTHOR
1; koalas and their habitat; smith
1; koalas and their habitat; jones

What I would like is:
1; koalas and their habitat; smith, jones

 

[pbaldy]

You can use a query that groups by the first 2 fields, and use a technique like this to concatenate the 3rd.

http://www.mvps.org/access/modules/mdl0004.htm

 

[jsv2002]

You could IF multiple authors of the same book concate the the fields I suppose but what if the title is the same and its not the same book a "unique" identifier is required maybe the ISBN?

 

[SueBK]

You could IF multiple authors of the same book concate the the fields I suppose but what if the title is the same and its not the same book a "unique" identifier is required maybe the ISBN?

The unique identifier is the "book ID". If I have two titles by different authors say:
"Koalas" by Smith & Jones
"Koalas" by Brown & White
they would have two different "Book ID" entries.

 

[jsv2002]

well to me looks like you want a multiple search criteria in your query something like..:

Like "*" & [Forms]![frmYourForm].[MySearchText] & "*"

That can be used in criteria of each field for your query to return your result.