Update a 1-side table with many-side values

[Jeff H]

I’m trying to create a form where a patient is selected and, based on a particular day’s session, a subform displays whatever specific services they received that visit. This will be used to add and edit the services provided.

In my attempts to understand how to set this up, I’ve normalized the service events into three tables: tblSvcEvents; tblSvcsProvided; and tblSvcItems. (Originally I was using an un-normalized table called Activities.)

Now I don’t know how to update tblSvcEvents (the one-side) with multiple services provided (the many-side). At this stage there are only about 26 such multi-service events: in tblSvcsProvided they are SvcProvidedID numbers 1408 and above.

Could you please show me how to write a query that will incorporate those 26 service records into tblSvcEvents? I’ve considered Update or Append queries, but I don’t know how they would coordinate the services provided with the existing service events. Additionally, all the prior service events (1407 and below) were single-service visits (because tracking individual services is a capability they wish to add go forward). So the SvcProvidedID for all the earlier sessions needs to be the same as the SvcEventID and the ActivityID.

I’ve included the relevant tables in the attached sample db. The tables tblVolunteers and tblPatients are included because the services provided relate to a specific patient/volunteer combination.

ServiceEventsSample.zip

[HansV]

I don't understand what you're trying to accomplish. Why would you need to add data from tblSvcsProvided to tblSvcEvents?

[Jeff H]

Yes, I see your point: the relevant foreign field is SvcEventID in tblSvcsProvided and that has the correct information. I'm confusing myself with all the attempts I've made.

So apparently my issue is how to create the form/subform combination that allows me to view and update the services provided. I had set up a form that displays the info ok, but doesn't allow me to update. I guess my question is, What is the key to relating the form and subform?

[HansV]

The main form should be based on tblSvcEvents.
The subform should be based on tblSvcsProvided.
The Link Child Fields and Link Master Fields properties of the subform should be set to EventID.

[Jeff H]

I have that for the subform, so I think the problem must be with the main form because I created it by adding fields from three tables: tblSvcEvents; tblPatients; and tblVolunteers (screenshot 18). If I try to enter a new item in the subform I get the error in screenshot 19.

If I create the main form on tblSvcEvents, how to I link the data from tblPatients and tblVolunteers?

Screenshot (18).png

Screenshot (19).png

[HansV]

If the main form is based on tblSvcEvents only, you can create combo boxes bound to PatientID and VolunteerID that display the field(s) that you want from tblPatients and tblVolunteers. See the attached very simple example.

ServiceEventsSample.zip

[Jeff H]

Ok, I'll play around with that and see how I do. Thanks Hans.