Results 1 to 15 of 15
  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 am having to change a query which had a field NNAME, which is now replaced using a junction table.

    The old code that worked was

    'Me.L88.RowSource = "SELECT TXCLIPS.NName AS Name, TXCLIPS.Shot, TXCLIPS.StarRating AS Rating,TXCLIPS.Comments, TXCLIPS.ID2" _
    & " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1 " _
    & " WHERE TXCLIPS.Comments & ' ' Like '*" & Me!LNAME14.Caption & "*' " _
    & " AND TXMASTERS.SportOrSports & ' ' Like '*" & Me!LNAME15.Caption & "*' " _
    & "ORDER BY TXCLIPS.NNAME"

    I am now modyfying it, but get no output when adding the line of inner join on athletenames. I have used a msgbox to look at the SQL but cannot see a problem. If I omit the line, the query asks for an athlete, if I answer with nothing it displays records but obviously not the correct ones. You can see remmed out lines where I have been working back to see where the problem is but got nowhere. Any ideas, thanks

    mysql = "SELECT AthleteNames.Athlete, TXCLIPS.Shot, TXCLIPS.StarRating AS Rating, TXCLIPS.Comments, TXCLIPS.ID2"
    mysql = mysql & " FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1"
    mysql = mysql & " INNER JOIN (AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID)"
    mysql = mysql & " ON TXCLIPS.ID2 = JUNCTION.ID2)"
    'mysql = mysql & " WHERE TXCLIPS.Comments & ' ' Like '*" & Me!LNAME14.Caption & "*' "
    'mysql = mysql & " AND TXMASTERS.SportOrSports & ' ' Like '*" & Me!LNAME15.Caption & "*' "
    'mysql = mysql & "ORDER BY ATHLETE"

    MsgBox mysql
    Me.L88.RowSource = mysql

  2. #2
    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)

    Managed to sort it out, went back to basics in query grid and worked forward. Ended up with:

    mysql = "SELECT StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) AS Athletes,"
    mysql = mysql & " TXCLIPS.Shot, TXCLIPS.StarRating AS Rating, TXCLIPS.Comments, TXCLIPS.ID2"
    mysql = mysql & " FROM TXCLIPS INNER JOIN (AthleteNames INNER JOIN JUNCTION ON"
    mysql = mysql & " AthleteNames.AthleteID = JUNCTION.AthleteID) ON TXCLIPS.ID2 = JUNCTION.ID2"
    mysql = mysql & " WHERE TXCLIPS.Comments & ' ' Like '*" & Me!LNAME14.Caption & "*' "
    Me.L88.RowSource = mysql

    Which seems to work. Drove me up the wall, but got there thanks.

  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)

    Out of one, into another. Can someone tell me why when this query runs it asks for paramater values for Barcode,SportorSports,SeriesName,Episode Title,Competition? Thanks

    MYSQL = "SELECT StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3),"
    MYSQL = MYSQL & " TXMASTERS.Barcode, TXCLIPS.Comments, "
    MYSQL = MYSQL & " TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXMASTERS.SportorSports AS Sport,"
    MYSQL = MYSQL & " TXCLIPS.StarRating, TXCLIPS.Shot, TXMASTERS.SeriesName AS Programme,"
    MYSQL = MYSQL & " TXMASTERS.EpisodeTitle AS Episode, TXMASTERS.Competition, TXCLIPS.ID2"
    MYSQL = MYSQL & " FROM TXCLIPS INNER JOIN (AthleteNames INNER JOIN JUNCTION ON"
    MYSQL = MYSQL & " AthleteNames.AthleteID = JUNCTION.AthleteID) ON TXCLIPS.ID2 = JUNCTION.ID2"
    MYSQL = MYSQL & " WHERE TXCLIPS.ID2 IN (" & Mid(strList1, 2) & ")"
    MYSQL = MYSQL & " ORDER BY ATHLETE"

    Me.LP1.RowSource = MYSQL

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

    Re: Query Problem (msaccess 2003)

    Because TXMASTERS is not included in the FROM part of the query

    FROM TXCLIPS INNER JOIN (AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID) ON TXCLIPS.ID2 = JUNCTION.ID2

  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)

    Thanks Hans. I have tried virtually every combination, no errors pop up, but no records in LP1. ?

    mysql = "SELECT TXCLIPS.ID2, StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3),"
    mysql = mysql & " TXMASTERS.Barcode, TXCLIPS.Comments, "
    mysql = mysql & " TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXMASTERS.SportorSports AS Sport,"
    mysql = mysql & " TXCLIPS.StarRating, TXCLIPS.Shot, TXMASTERS.SeriesName AS Programme,"
    mysql = mysql & " TXMASTERS.EpisodeTitle AS Episode, TXMASTERS.Competition, TXCLIPS.ID2"

    mysql = mysql & " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)"
    mysql = mysql & " INNER JOIN (AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID)"

    mysql = mysql & " FROM TXCLIPS INNER JOIN (AthleteNames INNER JOIN JUNCTION ON"
    mysql = mysql & " AthleteNames.AthleteID = JUNCTION.AthleteID) ON TXCLIPS.ID2 = JUNCTION.ID2"

    mysql = mysql & " WHERE TXCLIPS.ID2 IN (" & Mid(strList1, 2) & ")"
    mysql = mysql & " ORDER BY ATHLETE"

    ' MsgBox mysql

    Me.LP1.RowSource = mysql

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

    Re: Query Problem (msaccess 2003)

    You should design the query interactively (i.e. in design view) until it does what you want, instead of trying to create it entirely in VBA, then transfer the SQL to the Visual Basic Editor.

  7. #7
    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)

    Thanks Hans. I have not found how to do joins in the query grid, will go back and see how to do it. Only guessing, but do you construct 1 query and then use it for the next?

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

    Re: Query Problem (msaccess 2003)

    You join two tables (or a table and a query or two queries) in the query design grid by dragging a field from one of them to the appropriate field in the other one and dropping it there. This creates a join line between them. You can modify the properties of the join by double-clicking the join line.

  9. #9
    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)

    Thanks, I will have a go at that. Regards

  10. #10
    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)

    Things starting to get resovled, however I cannot see why this query sometimes outputs more records than the one being selected, the records are all identicle. The only thing I can think it might be (guessing) is the concantenation line running the code muliple times until all names are together. Could this be the case?

    mysql = "SELECT StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3)"
    mysql = mysql & " AS Athlete, TXMASTERS.Barcode, TXCLIPS.Comments, TXCLIPS.Start, TXCLIPS.Duration, TXMASTERS.SportorSports,"
    mysql = mysql & " TXCLIPS.StarRating , TXCLIPS.Shot, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXMASTERS.Competition, TXCLIPS.ID2"
    mysql = mysql & " FROM TXMASTERS INNER JOIN ((TXCLIPS LEFT JOIN Keywords ON TXCLIPS.Comments = Keywords.Keyword)"
    mysql = mysql & " INNER JOIN (AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID) ON TXCLIPS.ID2 ="
    mysql = mysql & " JUNCTION.ID2) ON TXMASTERS.ID1 = TXCLIPS.ID1"
    mysql = mysql & " WHERE TXCLIPS.ID2 IN (" & Mid(strList1, 2) & ")"
    mysql = mysql & " ORDER BY TXCLIPS.Start"
    Me.LP1.RowSource = mysql

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

    Re: Query Problem (msaccess 2003)

    Without knowing what (for example) qselJunctionNames does, it's hard to say, but does it work better if you use

    mysql = "SELECT StrConv(Concatenate(""SELECT DISTINCT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3)"
    ...

  12. #12
    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)

    Thanks Hans but it did not fix it. Sorry, I forgot about the query involved, its:Thanks

    SELECT Junction.ID2, AthleteNames.Athlete, AthleteNames.Country AS Expr1
    FROM AthleteNames INNER JOIN Junction ON AthleteNames.AthleteID = Junction.AthleteID;

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

    Re: Query Problem (msaccess 2003)

    If that doesn't work I don't have any idea of the problem, let alone of the solution. It's all far too complicated.

  14. #14
    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)

    Thanks Hans, I quite understand, but thanks for your help.

  15. #15
    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)

    Just managed to do it:

    mysql = "SELECT DISTINCT (StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3))"

    Best regards

Posting Permissions

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