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

    Integrate queries (MS Access 2003)

    I have a listbox having the rowsource below. It works okay, however It has a field NName which was an original field for a name. However users entered multiple names in the field and it became a problem for querying a particular name etc. A junction table arrangement was then put in place together with a table for Names to go in.

    Old Query
    SELECT DISTINCT TXCLIPS.NName AS Name, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXCLIPS.NName, TXCLIPS.Start, TXMASTERS.Typeofmaterial, TXMASTERS.Subtitles, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.AdditionalInformation, TXMASTERS.Competition, Audio.AudioType AS A, Audio_1.AudioType AS b, Audio_2.AudioType AS C, Audio_3.AudioType AS D
    FROM (Audio AS Audio_3 RIGHT JOIN (Audio AS Audio_2 RIGHT JOIN (Audio AS Audio_1 RIGHT JOIN (Audio RIGHT JOIN TXMASTERS ON Audio.AudioID = TXMASTERS.Audio1) ON Audio_1.AudioID = TXMASTERS.Audio2) ON Audio_2.AudioID = TXMASTERS.Audio3) ON Audio_3.AudioID = TXMASTERS.Audio4) LEFT JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
    WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]))
    ORDER BY TXCLIPS.Start;

    My next issue was to try and concatenate the Names in the new AthleteNames table to replace my NName in my original query above.

    First I did a query named QryNames

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

    I then did a second query

    SELECT ID1, ID2,
    Concatenate("SELECT Athlete FROM QryNames WHERE ID2 =" & ID2)
    FROM TXCLIPS;

    Both the queries work, but how can I integrate them into the original query so that the concatenated name can go into replace NName?
    Query still to be driven by "WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]))"

    Feel guilty asking the question, looks messy, quite understand if nobody answers back. Thanks

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

    Re: Integrate queries (MS Access 2003)

    This is too complicated to answer without seeing the database. Moreover, Concatenate is not a built-in function.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

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

    Re: Integrate queries (MS Access 2003)

    Thanks Hans, thought that might be difficult without seeing the DB, however it's size is a problem. I have cut down the original query so its not so daunting as a last attempt otherwise its trial and error for then next days.

    SELECT DISTINCT TXCLIPS.NName AS Name, TXMASTERS.Barcode, Audio.AudioType AS A
    FROM (Audio RIGHT JOIN TXMASTERS ON Audio.AudioID = TXMASTERS.Audio1) LEFT JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1
    WHERE (((TXMASTERS.Barcode)=[FORMS]![TapeLogs].[BCODE].[CAPTION]));

    Regards

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

    Re: Integrate queries (MS Access 2003)

    Hans, I got it working. I used the Concatenate line of my last query with modifiaction and it seemed to fix it. The extra bit was because it complained not knowing which ID2 it was to use.

    Name: Concatenate("SELECT Athlete FROM QryNames WHERE ID2=" & [TXCLIPS].[ID2])

    We move again 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
  •