Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: Show the same record number (fk) only once in query result

    Hi,

    I use this query to display items in a library catalogue. This works well and a command button to open a "Full details" form shows all information including subject headings associated with the item in a subform.

    Code:
    SELECT Library_table.[Publish Date], Library_table.Title, Library_table.Author, Library_table.Publisher, Library_table.Subject, Library_table.Summary, Library_table.[WWW  link:], Library_table.[ISBN:], Library_table.[Document Description:], Library_table.[Copy Number], Library_table.Class_No, Library_table.[Team:], Library_table.ID
    FROM Library_table INNER JOIN itemlocation ON Library_table.ID = itemlocation.bookid
    WHERE (((Library_table.[Publish Date])=IIf([Publish Date] Is Null,0,Val([Publish Date]))))
    ORDER BY Library_table.[Publish Date] DESC;
    My problem is I've added a text search button and it really needs to search the Subject Headings as well. The trouble is that because an item can have more than one subject heading this creates duplicates in the results. These are not real duplicates as they are unique rows, but I only want to see the book.id once in the result.

    Code:
    SELECT Library_table.ID, Library_table.Title, Library_table.Author, Library_table.Publisher, Library_table.[Publish Date], Library_table.Subject, Library_table.Summary, Library_table.[ISBN:], Library_table.[Document Description:], Library_table.Class_No, tblSubjectHeadings.[Subject Heading]
    FROM tblSubjectHeadings INNER JOIN (Library_table INNER JOIN item_subjectheadings ON Library_table.ID = item_subjectheadings.bookid) ON tblSubjectHeadings.ID = item_subjectheadings.subjectid
    WHERE (((1)=1));
    Can anyone help with the correct syntax?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SELECT DISTINCT Library_table.ID, Lib.....
    should do the trick

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2007
    Posts
    72
    Hi izy,
    That doesn't change the result. I tried that earlier and thought that because each row (if displayed) would be unique that DISTINCT doesn't work? Is this right or is something else wrong?

    e.g. Book1, War and Peace, subjectid: 1(war), subjectid:2(peace)

    War and Peace, bookid 1, subjectid: 1
    War and Peace, bookid 1, subjectid: 2.

    These rows have unique values but in a display you only need to look at one.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why does it matter?
    this sounds like a user interface issue rather than a query issue
    where are you using the data, what is the probelm where you are using that data.

    if its in a report judicious use of record grouping should get over the issue
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    War and Peace, bookid 1, subjectid: 1
    War and Peace, bookid 1, subjectid: 2

    are very much DISTINCT thanks to the subjectid field

    if you get rid of subjectid from the SELECTed fields, you will get only one 'war and peace'.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jul 2007
    Posts
    72
    Healdem, The problem is it is in a form not a report, so I can't group? (I'm in Access 2003)
    Izy, I just tried that and it means the subject heading field isn't being searched. That is why I'm including it.
    I can't think of a way around it. The idea is to display results of a search, and if one book is displayed four times because it has four different headings it is not useable. I don't mind changing my forms and using reports if that will do the job, but right now I'm stuck as to what to do.

    I'm using the search as a filter on one form, but I don't have a preference for this and would be happy to be pointed to a way that works for this situation!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    I just tried that and it means the subject heading field isn't being searched !!
    why?
    just because you have the field in the WHERE clause doesn't mean you must have it in the SELECT clause.

    please post your current SQL - in #1 the first has a JOIN but does not use the JOINed table. the second SELECTs all records.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bronisaurus
    e.g. Book1, War and Peace, subjectid: 1(war), subjectid:2(peace)

    War and Peace, bookid 1, subjectid: 1
    War and Peace, bookid 1, subjectid: 2.

    These rows have unique values but in a display you only need to look at one.
    Please also show us what you want, for this book, the display to be.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2007
    Posts
    72
    Hi izy, healdem, the first sql query is the record source for the form. It has the joins so that the correct fields are available when the second query (this is behind the search button) is run. It doesn't need them for display purposes as this page shows the Title, Author, Publish Date (these are fields in one table). The second query needs the joined tables as subject headings are in a related table and are important for searching.
    That is the first part of the Search button query, the whole thing is:
    Code:
    Private Sub Command63_Click()
    Dim strSql As String
    Dim IngLen As Long
    Const strcTail = "ORDER BY [Publish Date]DESC;"
    
    strSql = "SELECT Library_table.ID, Library_table.Title, Library_table.Author, Library_table.Publisher, Library_table.[Publish Date], Library_table.Subject, Library_table.Summary, Library_table.[ISBN:], Library_table.[Document Description:],Library_table.Class_No,tblSubjectHeadings.[Subject Heading]FROM [Library_table]INNER JOIN (tblSubjectHeadings INNER JOIN  item_subjectheadings ON tblSubjectHeadings.ID=item_subjectheadings.subjectid) ON Library_table.ID = item_subjectheadings.bookid WHERE (1=1)"
    
    If Not IsNull(Me.freetext) Then
    strSql = strSql & " And( Library_table.Title Like '*" & Me.freetext & "*' Or Library_table.Author Like '*" & Me.freetext & "*' Or Library_table.Publisher Like '*" & Me.freetext & "*' Or Library_table.[Publish Date] Like '*" & Me.freetext & "*' Or Library_table.Subject Like '*" & Me.freetext & "*' Or Library_table.Summary Like '*" & Me.freetext & "*' Or Library_table.[ISBN:] Like '*" & Me.freetext & "*' Or Library_table.[Document Description:] Like '*" & Me.freetext & "*' Or Library_table.Class_No Like '*" & Me.freetext & "*' Or tblSubjectHeadings.[Subject Heading] Like '*" & Me.freetext & "*')"
    End If
    
    
    strSql = strSql & strcTail
    
    'Assign the query string.
    Me.RecordSource = strSql
        
    End Sub

    The way the form (continuous) looks is:

    Title: Rethinking landscape Author Smith, Tom Pub.Date 2009 Button: Full Details

    When the Full Details button is clicked this opens a form with all fields from "Library_table" and a subform with the associated subject headings (this has problems too, only showing one heading (logical I know,)).

    I thought of starting again with a blank search form (not a form of everything that is then filtered) but I'm afraid I would still have similar problems.

  10. #10
    Join Date
    Jul 2007
    Posts
    72
    I've been leaving this alone for a while as my head is spinning.
    In the mean-time I've decided to just use the main query and get things working without the subject headings search as most people look for specific books anyway. Would Allen Browne's Microsoft Access tips: Subquery basics para on deleting duplicates be what I'm looking for?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •