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

    Query with Junction table problem (access 2003)

    The database originally had a Name field where users where putting in more than one name, there was a severe duplication as names were common to many records. I then created a Names junction table setup. It works, however for a report I have to combine names again in records. When I run a query there are duplicated lines shown in a pre-report listbox. I have thought about creating a temporary table from the query and then working through the recordset but wondered if there was a better way. The field ID2 is a primary key in related tables. First table is TXMASTERS, second table is TXCLIPS, third table is Junction, last table is AthleteNames. All tables related, AthleteNames.Athlete is the field I am tring to combine for every individual value of ID2. Any guidance appreciated. Thanks

    SELECT DISTINCT TXMASTERS.ID1, TXCLIPS.ID2, AthleteNames.Athlete, TXCLIPS.Start AS [Timecode In], TXCLIPS.Duration, TXCLIPS.StarRating, TXCLIPS.Comments, TXMASTERS.Barcode, TXMASTERS.SeriesName, TXMASTERS.EpisodeTitle, TXCLIPS.Start, TXMASTERS.Typeofmaterial, TXMASTERS.Subtitles, TXMASTERS.Stageofcompetition, TXMASTERS.EventDate, TXMASTERS.Venue, TXMASTERS.AdditionalInformation, TXMASTERS.Competition, AthleteNames.Athlete
    FROM TXMASTERS INNER JOIN (TXCLIPS INNER JOIN (AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID) ON TXCLIPS.ID2 = JUNCTION.ID2) ON TXMASTERS.ID1 = TXCLIPS.ID1
    WHERE (((TXMASTERS.Barcode)="ECS000005602"))
    ORDER BY TXMASTERS.ID1, TXCLIPS.ID2, TXCLIPS.Start;

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

    Re: Query with Junction table problem (access 2003)

    The attachment to <post#=301,393>post 301,393</post#> contains the code for a function named Concat that you can use to concatenate values into one long string. Copy the function into a standard module.

    To use it in your query, remove the AthleteNames table (and hence its Athlete field) from the query design.
    Add a calculated column:

    Athletes: Concat("AthleteNames", "Athlete", "AthleteID=" & Junction.AthleteID)

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

    Re: Query with Junction table problem (access 2003)

    Thanks Hans. I put the funtion into a module. I reduced the rowsource query of the listbox so I could see what was happening, however the Athlete field does not change. I presume the Junction table stays in, although I also tried it without.

    My list SQL is

    SELECT TXMASTERS.Barcode, TXCLIPS.ID2, JUNCTION.AthleteID, Concat("AthleteNames","Athlete","AthleteID=" & [Junction].[AthleteID]) AS Athletes
    FROM TXMASTERS INNER JOIN (TXCLIPS INNER JOIN JUNCTION ON TXCLIPS.ID2 = JUNCTION.ID2) ON TXMASTERS.ID1 = TXCLIPS.ID1
    WHERE (((TXMASTERS.Barcode)=[FORMS]![Hans].[BCODE].[CAPTION]))
    ORDER BY TXCLIPS.ID2;

    I am probably doing something stupid. Regards and thanks again.

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

    Re: Query with Junction table problem (access 2003)

    I see now, it's going to be more complicated. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

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

    Re: Query with Junction table problem (access 2003)

    Thanks Hans. I am probably taking the long way round but I am looking at using the original query of the list to create a temporary table and then go through the recordset. When a value of ID2 is the same then concantinate the names. The query spills out in ID2 order however I seem to be going round in circles doing it. I will let you know how I get on with it.

    Something like this skeleton

    IEE = 0
    Do While Not rsTemp1.EOF

    IDD = rsTemp1.Fields("ID2")

    If IEE = 0 Then
    ' Store Athlete name in string to later get added to
    IEE = IDD
    Else

    If IDD <> IEE Then

    Else

    If IDD = IEE Then

    End If: End If: End If

    rsTemp1.MoveNext
    Loop

    Thanks and 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
  •