Query for Multiple tables with 1 to many relationship

[sbrem00]

Hi all,

I have a table holding resident details and i have a table holding community group details, the residents table is the child of the Com groups table but is replicated so each field in the groups table (Chair, VChair, Sec, Tres) can be linked to a different resident. (a resident can also fulfill more than one of these roles).

I would like to make a query which displays all of the residents that are either Chair, VChair, Sec or Tres and details what they are. It will then need to group the duplicate records so all Com groups connected to that resident are on the same row.

For example one row of the query should say: Jim Brown, Chair/Tresurer, Eastside Pensioners Club. (And any other groups they may be a member of)

I have attached a image of my table relationships as it may help.



Thanks

Sam

[June7]

The image is not coming through for me in Internet Explorer.

With this structure the query would have multiple joins to the Residents table. Each of the position fields would join to the Residents table. This means bringing the Residents table into the query designer four times. The query record would look like:

Eastside Pensioners Club | Jim Brown | Casey Martin | Jim Brown | Linda Parker

The output you want will not be easy to do.

[boblarson]

Upload the image to this site instead of using external sites.

The output you want will not be easy to do.

The output can be done with this:

http://allenbrowne.com/func-concat.html

[June7]

Yep, not easy if you are totally unfamiliar with VBA coding. An alternative is a UNION query that will get the data into a normalized structure on which a report with Grouping and Sorting can be built. The data will be vertically arranged but you can group information by residents. There is no wizard or designer for UNION query, must type in the SQL View editor window of the query designer.

SELECT GroupID, GroupName, "Chair" As Position, Chair As ResID FROM Groups
UNION SELECT GroupID, GroupName, "VChair", VChair FROM Groups
UNION SELECT GroupID, GroupName, "Sec", Sec FROM GROUPS
UNION SELECT GroupID, GroupName, "Treas", Treas FROM GROUPS;

Now join to the Residents table to get resident names and use as RecordSource for the report.
SELECT Query1.*, Residents.ResName
FROM Residents RIGHT JOIN Query1 ON Residents.Residents = Query1.ResID;

[sbrem00]

Yep, not easy if you are totally unfamiliar with VBA coding. An alternative is a UNION query that will get the data into a normalized structure on which a report with Grouping and Sorting can be built. The data will be vertically arranged but you can group information by residents. There is no wizard or designer for UNION query, must type in the SQL View editor window of the query designer.

SELECT GroupID, GroupName, "Chair" As Position, Chair As ResID FROM Groups
UNION SELECT GroupID, GroupName, "VChair", VChair FROM Groups
UNION SELECT GroupID, GroupName, "Sec", Sec FROM GROUPS
UNION SELECT GroupID, GroupName, "Treas", Treas FROM GROUPS;

Now join to the Residents table to get resident names and use as RecordSource for the report.
SELECT Query1.*, Residents.ResName
FROM Residents RIGHT JOIN Query1 ON Residents.Residents = Query1.ResID;

Thanks June 7, This is exactly what i wanted. I did look into Union Queries but was unsure how to impliment them so you have been a great help.

Now i have my data in a list do you know how i may be able to merge the duplicate names or more specifically the Position/Role columns of the duplicate rows?

Thanks again,

Sam

[June7]

Would require VBA code, as in the link boblarson provided. However, I recently tried to use a UNION query in VBA code and it failed. If a UNION was used in any stage of a query sequence, the VBA would not build a recordset from the final query. I finally gave up and restructured my procedure to not involve a UNION query.

You really need your data properly normalized. If you simply can't fix the database then write the UNION records to a table and use that table in the VBA.