Combine / Concatenate multiple tables into one

[Guest]

How can I get from two tables to one table like this

Table A (two columns)
ID League
1 NFL
2 NBA
3 MLB

Table B (two columns)
ID Team
1 Eagles
1 Steelers
1 Patriots
1 Falcons
2 Sixers
2 Celtics
2 Hawks
3 Phillies
3 Pirates
3 Red Sox
3 Braves

Combined table (with 3 columns)
ID League Teams
1 NFL Eagles; Steelers; Patriots; Falcons
2 NBA Sixers; Celtics; Hawks
3 MLB Phillies; Pirates; Red Sox; Braves

 

[Lynn Trapp]

Use a query:

SELECT A.League_ID, A.League, B.Team
FROM [Table A] AS A INNER JOIN [Table B] AS B ON A.League_ID = B.League_ID;

 

[Douglas J. Steele]

Don't see how that's going to do it, Lynn. He wants to combine all of the
teams into a single field (not a good idea, of course)

I think he's going to need to create a function like what's in
http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" in order
to do it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Use a query:

SELECT A.League_ID, A.League, B.Team
FROM [Table A] AS A INNER JOIN [Table B] AS B ON A.League_ID =
B.League_ID;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

How can I get from two tables to one table like this

Table A (two columns)
ID League
1 NFL
2 NBA
3 MLB

Table B (two columns)
ID Team
1 Eagles
1 Steelers
1 Patriots
1 Falcons
2 Sixers
2 Celtics
2 Hawks
3 Phillies
3 Pirates
3 Red Sox
3 Braves

Combined table (with 3 columns)
ID League Teams
1 NFL Eagles; Steelers; Patriots; Falcons
2 NBA Sixers; Celtics; Hawks
3 MLB Phillies; Pirates; Red Sox; Braves

 

[Lynn Trapp]

Don't see how that's going to do it, Lynn. He wants to combine all of the

teams into a single field (not a good idea, of course)

Doug, I just cringe at helping someone do something non-normal like
that...LOL. You're quite right that he needs the Acces Web stuff to do what
he wants.

 

[Guest]

I promise there's a pretty good reason for me needing to do this and I'm not
blaspheming the relational db gods. It's actually a training tracking table
which uses a sub-table to track the trainers which of course is a one-to-many
relationship, but there's a need to report the data back with trainers
attached to the master record rather than a new master record for each
different trainer training at the same session.

Thanks for the feedback and I'll check out the links.

 

[Guest]

This works... I have a few more questions if you know your way around this
code.

Here's my SQL

SELECT [Master Table].TrainingRecordID,
fConcatChild("tblTrainingEvents","TrainingRecordID","TrainerKey","long",[TrainingRecordID]) AS Trainers
FROM [Master Table];
As I understand it says, make two columns one with TrainingRecordID, and
another with using the function fConcatChild to concatenate TrainerKey by
matching TrainingRecordID from tblTrainingEvents to TrainingRecordID from
Master Table.

OK... makes sense... somewhat...

The problem I have now is that the table uses keys as opposed to actual
values and while it does a great job of concatenationg the keys, I need to
display values back. I need to try to get at the values for TrainerKey
instead of the key itself.

Additionally, I would like to figure out how I could make it concatenate
only unique values. I haven't examined the code to see if I could alter the
function to make this work, but any feedback you may have would be greatly
appreciated.

Don't see how that's going to do it, Lynn. He wants to combine all of the
teams into a single field (not a good idea, of course)

I think he's going to need to create a function like what's in
http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" in order
to do it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Use a query:

SELECT A.League_ID, A.League, B.Team
FROM [Table A] AS A INNER JOIN [Table B] AS B ON A.League_ID =
B.League_ID;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

How can I get from two tables to one table like this

Table A (two columns)
ID League
1 NFL
2 NBA
3 MLB

Table B (two columns)
ID Team
1 Eagles
1 Steelers
1 Patriots
1 Falcons
2 Sixers
2 Celtics
2 Hawks
3 Phillies
3 Pirates
3 Red Sox
3 Braves

Combined table (with 3 columns)
ID League Teams
1 NFL Eagles; Steelers; Patriots; Falcons
2 NBA Sixers; Celtics; Hawks
3 MLB Phillies; Pirates; Red Sox; Braves

 

[Guest]

I figured out to just add the values to a query prior to running the
concatenation function, but have not figured out how to concatenate only
unique values. The function itself is not too complicated (this coming from
a novice "programmer"), but I'm not sure where to compile the recordset as
unique values only.

Don't see how that's going to do it, Lynn. He wants to combine all of the
teams into a single field (not a good idea, of course)

I think he's going to need to create a function like what's in
http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" in order
to do it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Use a query:

SELECT A.League_ID, A.League, B.Team
FROM [Table A] AS A INNER JOIN [Table B] AS B ON A.League_ID =
B.League_ID;


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

How can I get from two tables to one table like this

Table A (two columns)
ID League
1 NFL
2 NBA
3 MLB

Table B (two columns)
ID Team
1 Eagles
1 Steelers
1 Patriots
1 Falcons
2 Sixers
2 Celtics
2 Hawks
3 Phillies
3 Pirates
3 Red Sox
3 Braves

Combined table (with 3 columns)
ID League Teams
1 NFL Eagles; Steelers; Patriots; Falcons
2 NBA Sixers; Celtics; Hawks
3 MLB Phillies; Pirates; Red Sox; Braves

 

[Marshall Barton]

Create a SELECT DISTINCT query for the function to use
instead of going directly to the table.