I have a DB which is used to organise interviews with experts on different animal and plant species. Each expert can have expertise on multiple species, and each species has multiple associated experts. Interviews are divided into different groups by week.

There are 4 tables:
Species (ID (called profile code), scientific name, interview week)
Expert (ID, surname, first name, email, phone etc)
SpeciesExpert Link (Species ID, Expert ID, correspondence) (no primary key)
Interview Schedule (interview week, species group, location)

[Species] are related to [experts] via [SpeciesExpert Link], and the [interview schedule] is related to [Species] via [interview week].

Basically, I want to enter correspondance information against an expert relating to a particular workshop. I have made a query that brings up one record per expert per week (using Count of Species ID/Profile Code):



ELECT [Interview Schedule].[Interview Week], Experts.Surname, Experts.[First Name], Experts.Affiliation, Count([SpeciesExpert Link].[Profile Code]) AS [CountOfProfile Code]
FROM ([Interview Schedule] INNER JOIN Species ON [Interview Schedule].[Interview Week] = Species.[Interview Week]) INNER JOIN (Experts INNER JOIN [SpeciesExpert Link] ON Experts.[Expert ID] = [SpeciesExpert Link].[Expert ID]) ON Species.ProfileCode = [SpeciesExpert Link].[Profile Code]
GROUP BY [Interview Schedule].[Interview Week], Experts.Surname, Experts.[First Name], Experts.Affiliation;

I have created a form based on this query, that I want to add data to. But when I add a [SpeciesExpert Link]![Correspondence] field, it won't let me add data. Obviously this is because it doesn't know which record (which species ID) to put the data against.

Is there any way of entering the correspondence data once in this form and having the same entry copied to all records for that expert in that interview week?

Thank you!!