Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem (MSACCESS 2003)

    i have a problem query. A table has a comments field where certain keywords are in. There is an inner join on comments and keywords. The problem is there is another field called shots. The results of the query are producing no output of this field because its field is not within the comments field. I was thinking of having two listboxes, one showing records with a keyword match and one with the shots field, but that will give a larger headache. Any suggestions greatly appreciated. Thanks

    mysql2 = "SELECT TXCLIPS.Shot, KEYWORDS.Keyword,TXCLIPS.Comments, AthleteNames.Athlete "
    mysql2 = mysql2 & "FROM ((TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1) "
    mysql2 = mysql2 & "INNER JOIN (AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID) "
    mysql2 = mysql2 & "ON TXCLIPS.ID2 = JUNCTION.ID2) "
    mysql2 = mysql2 & "INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' "
    mysql2 = mysql2 & "WHERE AthleteNames.Athlete Like [Forms]![ShotsKeywords].[Form].[LNAME4].[Caption]"
    mysql2 = mysql2 & " ORDER BY KEYWORD"

    Me.L14.RowSource = mysql2

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Problem (MSACCESS 2003)

    Please try to explain what you want to do with the field variously called Shot and Shots.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (MSACCESS 2003)

    Sorry Hans I meant shot. I am trying to display records in a listbox that either have a matching keyword in the keyword column and a shot value if there is one in the shot column, this also applies if a single record has a keyword and a shot. Hope that's a bit clearer, thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Problem (MSACCESS 2003)

    No, I don't understand at all, sorry. Please try to explain more clearly.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (MSACCESS 2003)

    Sorry, I know it's complicated. The DB is too large to attach which would have made it easier. Basically there are two related tables, the parent TXMASTERS and the child TXCLIPS. In the TXCLIPS table there is a comments field. In the text of the comments are variuos keywords. Therfore there is a join between the comments field in TXCLIPS and a table called Keywords.

    This join provides records that have a matching keyword.which works well. However because it gathers only records with a matching keyword, it omits to output other records in the child field called shot, because that record did not have a keyword in the comments field it was excluded. Appologies for my bad way of explaining it. Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Problem (MSACCESS 2003)

    Perhaps you should create two queries: one that finds matches for keywords in the Comments field and another one that finds matches for keywords in the Shot field.
    Then create a Union query that combines the results of the two queries.
    A union query looks like this:

    SELECT ...
    UNION
    SELECT ...

    Actually, you can add more UNION and SELECT ... statements. Each of the SELECT statements should return the same number of fields, where corresponding fields have the same field types. For example:

    SELECT Field1, Field2 FROM TableA
    WHERE Field2 > 34
    UNION
    SELECT Field1, Field2 FROM TableB
    WHERE Field1 = "Jones"

Posting Permissions

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