Show fields from multiple records in query

[irvy]

I have a database with 2 tables. One table contains a list of team leaders. The second table contains a list of workers. The workers table is as follows:



Table: Workers
Id Name Surname Group
1 John Joseph A
2 Joe Johnson B
3 Arthur Edgar B

Table: TeamLeaders
Id Name Surname Group
1 Edward Fox A
2 John Grant B
3 Ethan Carter A

The team leader is similar and the two tables are linked by the Group. Some groups have more than 1 team leader.

I want to run a query which will give me the worker along with all the team leaders as below:

John Joseph A Edward Fox Ethan Carter
Joe Johnson B John Grant

Would it make it easier if I broke my Workers into Groups?

Thank you,

Irvy

[Robeen]

Do you have to see each worker and all related team leaders on the same row?
In my experience, that is not easy to do, because in the scenario you've given above, Access will want to show your data like this:
John Joseph A Edward Fox
John Joseph A Ethan Carter
. . .
What you want CAN be done . . . but it involves jumping through some hoops [relatively speaking].
Much easier to go with how Access [and other databases] works by default.

[irvy]

Hi Robeen,

Thank you for your reply. I thought that might be the case. I can work with the double records, but just thought it might look more efficient the other way, if it was easy enough to do.

Irvy

[Robeen]

Perhaps someone else will look in here and tell us if there is an easy way to get this done.
Every time I've done it, I've used a Function that Loops through the records and writes stuff out the way you want to a separate table.
If you want to go that route, I can point you in the direction and/or give you some sample code for a function.