I am trying to concatenate values from related records, and have looked at Allen Browne’s solution (using DAO), as well as those of Dev Ashish (on The Access Web) and Duane Hookom (using ADO). All of these are for applications with no more than two tables (and 1-many relationships) to get the data from. My problem is I have many-many relationships, so I have a third table in between. The db (originally developed in Access 2000, but I am now using 2007) is used to track and report on the missions our church supports, and show the names of the people in our church who report on those missions.
There are 3 tables:
Missions (MissionID as primary key and autonumber Long), Mission_Title (text) and several others, all text but one, which is memo.
Reporters (ReporterID as primary key and autonumber Long), FirstName, LastName, and phone (all text), and Notes (memo).
MissionReporters (MissionReporterID as primary key and autonumber Long), MissionID and ReporterID, both Long.
There are about 25 records in Missions and a few more in Reporters, so it’s a pretty small db, and is in the .mdb format.
I need to create a report that shows which reporter(s) are assigned to each Mission, and another report showing which missions are assigned to each reporter. I am having difficulty with a query for the first report, having tried Mr. Hookom’s approach, and can’t figure out how to adapt it to a many-many application.
Data records in Missions: Mission A, Mission B, Mission C, etc. Each unique mission appears once. Data in Reporters: John Smith, Jane Doe, Oscar Myer, etc., with each name appearing once. Data in MissionReporters: All long integers, MissionReporterID being 1-27, and the 2 other fields matching Missions and Reporters.
I would really appreciate help with the query, as I am now retired and don’t have access to a corporate library, local experts, etc.
Thanks,
Ray