Query Result Problem

[sandra45]

Hi, I've created one query to filter record from three tables:
-TblTitle (consists of books' title, subject, ISBN as the primary key);
-TblTitlAuthor (consists of ISBN and Au_ID);
-TblAuthor (consists of Au_ID, Author name).

I have TblTitlAuthor because TblTitle and TblAuthor has many-to-many relationship, that is why to make them one-to-many, I've linked them with TblTitlAuthor.

The result of the query will be stored in another form under some text boxes such as title, author, ISBN, obtained from the query as the source. I notice that the records shown will be repeated since many authors link to one title, e.g.:
record no. 1:
title: programming in vb
author: xyz
ISBN: 123

record no.2:
title: programming in vb
author: abc
ISBN: 123

How to set the result only shows ONE record with combined authors? Thanks.

Regards,
Sandra

 

[dyarwood]

Have you tried using a string for the authors and where the titles and isbn's of the books are the same add the strings and update the table with the new author string for the book?

dyarwood

 

[sandra45]

Hi, I'm not using any vb code here. I just created a query to do the filtering, that is why I don't know how to manipulate the result as I want it. Where to add the string as you mention here? I have one form to let the users type in keyword for searching, once the search button is clicked, another form is opened with the result obtained from the query.

Regards,
Sandra

 

[MakeItSo]

Could you copy the SQL statement of your query here?
If your result output is wrong, then there should be something wrong with the query.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[sandra45]

Okay, this is the code I have in my search form (search command button). I'm not using the codes at this moment because the problem as shown below.

Dim sql As String
Dim sql1 As String
Dim sqltest As String
Dim temp1 As Database
Dim temp As DAO.Recordset

Set temp1 = CurrentDb

If txtTitle <> &quot;&quot; Then sql = sql + &quot;TblTitle.Title LIKE '*&quot; + txtTitle + &quot;*' AND &quot;
If txtSubject <> &quot;&quot; Then sql = sql + &quot;TblTitle.Subject LIKE '*&quot; + txtSubject + &quot;*' AND &quot;


****************ERROR with this SQL Statement,
****************try to link three tables TblTitle,
****************TblTitlAuthor, TblAuthor
********Intention is to get ISBN from TblTitle based on
********Author's name inputted by the user
*******and this Author's name is located in TblAuthor
*******which is linked by TblTitlAuthor
If txtAuthor <> &quot;&quot; Then
sql1 = sql + &quot;TblTitlAuthor.[Au_ID] IN (SELECT TblAuthor.[Au_ID] FROM TblAuthor WHERE Tbl_Author.[Author] LIKE '&quot; + txtAuthor + &quot;*') AND &quot;
sql = sql1 + &quot;TblTitlAuthor.[ISBN] IN (SELECT TblTitle.[ISBN] FROM TblTitle WHERE TblTitle.[ISBN] LIKE TblTitlAuthor.[ISBN]) And &quot;
End If
******************************************Until here

If Right(sql, 4) = &quot;AND &quot; Then sql = Left(sql, Len(sql) - 5)

On Error GoTo errHandle
If sql = &quot;&quot; Then
sqltest = &quot;SELECT COUNT (*) FROM TblTitle&quot;
Else
sqltest = &quot;SELECT COUNT (*) FROM TblTitle WHERE &quot; + sql
End If

Set temp = temp1.OpenRecordset(sqltest)

If temp.Fields(0) = 0 Then
MsgBox &quot;The search found no matching records. Please check input and try again.&quot;, 48
Else
DoCmd.OpenForm &quot;FrmResult1&quot;, acNormal, , sql
End If

Exit Sub

errHandle:
MsgBox Err.Description

End Sub