Query based on Junction linked tables 2

[ZOR]

Having put in a junction table to avoid duplication of data has become a nightmare. I am trying to recover a way to produce reports. The code below works but only because old data is still in records

SELECT TXMASTERS.Barcode, AthleteNames.Athlete, TXCLIPS.Comments, TXMASTERS.Competition, TXCLIPS.NName AS Name, TXMASTERS.EpisodeTitle, TXCLIPS.Start, TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXCLIPS.StarRating
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 (((AthleteNames.Athlete)=[forms]![AthleteCompetition].[Form].[LNAME4].[caption]) AND ((TXMASTERS.Competition)=[forms]![AthleteCompetition].[Form].[LNAME6].[caption]));

The field TXCLIPS.NName was a field which held more than 1 name seperated by commas and a host of other wrong data. However it was displayed in column one of a columnar report. However the names are all now in a junctioed table called AthleteNames. I am trying to find how to replace the orignal NName column with a truncated line of relevant AthleteNames.Athlete, eg Name1;Name2;Name3 etc.

Can anyone suggest a way of doing it?? Many thanks

 

[lespaul]

check out FAQ701-4233

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ZOR]

Many thanks Leslie. I will try and see how to use it in my query. Regards

 

[ZOR]

Appreciate any further guidance. Getting in a mess with so many attempts, reports starting to look like nothing they should be. Be wonderful if there was a magic SQL way. Regards

 

[lespaul]

ok, you should have copied the entire module into your database, and saved it as modConcatenate.

In your junction table I'm assuming that you have something like:
[tt]
SomeCommonID IndividualID
1 1234
1 2345
1 3456[/tt]

Then in your query you're going to do this:

SELECT SomeCommonID, Concatenate("SELECT AthleteName FROM TableName WHERE AthleteID IN (SELECT IndividualID FROM JunctionTable WHERE SomeCommonID =1") As Names FROM JunctionTable

and you'll get results of:

[tt]
SomeCommonID Names
1 John, Sue, Dave[/tt]

What exactly is not working correct? What is the SQL that you are trying to use?

Les

 

[ZOR]

Many thanks Les.

I have the following SQL

SELECT ID2, Concatenate("SELECT Athlete FROM AthleteNames WHERE AthleteID IN (SELECT AthleteID FROM Junction WHERE ID2 =1") As Names FROM Junction

But it has an error in the syntax. I could not cut paste the message but it may be obvious

My Junction Table (Junction) is

ID2 AthleteID
1 1234
1 2345
1 3456

Hope it is relevant. Thanks again

 

[ZOR]

The error message is

Missing ), ] or item in query expression 'AthleteID IN (SELECT AthleteID FROM Junction WHERE ID2 = 1'.

Regards

 

[lespaul]

try this:

SELECT ID2, Concatenate("SELECT Athlete FROM AthleteNames WHERE AthleteID IN (SELECT AthleteID FROM Junction WHERE ID2 =1[COLOR=red])[/color]") As Names FROM Junction

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ZOR]

Hi Leslie,

That produces two columns, ID2 which has all values of ID2 within the table but names column is blank.
I tried changing As Names FROM Junction to FROM AthleteNames but that error'd

??

 

[ZOR]

I just looked at the junction table and there is no ID2=1. I changed the SQL to a value in ID2 that exists, but column 2 (names) is filled with one particular name all the way through. Regards

 

[lespaul]

so if you run the query:

SELECT Athlete FROM AthleteNames WHERE AthleteID IN (SELECT AthleteID FROM Junction WHERE ID2 =1)

do you get a list of names that you want to have in the comma separated list?

 

[ZOR]

I got there!!

I put the SQL into a query, and found it put two columns in the query grid. I looked at the junction table to find a value of ID2 which had duplicates, and put the number in. When I ran the query, every row was filled with the same names seperated by commas. I then added the ID2 number in the ID2 criteria and bingo it works. The final SQL is:

SELECT Junction.ID2, Concatenate("SELECT Athlete FROM AthleteNames WHERE AthleteID IN (SELECT AthleteID FROM Junction WHERE ID2 =855)") AS [Names]
FROM Junction
WHERE (((Junction.ID2)=855));

Does that look right?

Regards

 

[dhookom]

Could you provide your actual table and field names and how they are related? I can't imagine why you need a subquery and why your main select has "FROM junction".

Duane
Hook'D on Access
MS Access MVP

 

[ZOR]

Thanks Duane. my table structure is below.
Regards

TXMASTERS
ID1 pk
other fields

TXCLIPS
ID1 fk
ID2 pk
OldAthleteName text
other fields

Junction Table
JunctionID pk
ID2 fk
AthleteID fk

AthleteNames Table
AthleteID pk
AthleteName text
AthleteCountry text

 

[dhookom]

You could create a query of the Junction and AthleteNames tables.
[tt][blue]qselJunctionNames[/blue][/tt]

SELECT ID2, AthleteName, AthleteCountry
FROM Junction INNER JOIN AthleteNames ON Junction.AthleteID = AthleteNames.AthleteID;

Then your query using TXCLIPS would be

SELECT ID1, ID2, 
Concatenate("SELECT AthleteName FROM qselJunctionNames WHERE ID2 =" & ID2)
FROM TXCLIPS;

You can add in other fields and possibly the TXMASTERS table if needed. This code assumes your ID2 field is numeric.

If you want to include the country field

SELECT ID1, ID2, 
Concatenate("SELECT AthleteName & ' (' & AthleteCountry & ')' FROM qselJunctionNames WHERE ID2 =" & ID2)
FROM TXCLIPS;

Duane
Hook'D on Access
MS Access MVP

 

[ZOR]

Excellent, I should have enough now to do all my report queries. Many thanks both.