Update a 1-side table with many-side values

Jeff H
3StarLounger
Posts: 279
Joined: 31 Oct 2017, 20:07

Update a 1-side table with many-side values

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Update a 1-side table with many-side values

Post by HansV »

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

Jeff H
3StarLounger
Posts: 279
Joined: 31 Oct 2017, 20:07

Re: Update a 1-side table with many-side values

Post by 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?

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Update a 1-side table with many-side values

Post by 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.
Regards,
Hans

Jeff H
3StarLounger
Posts: 279
Joined: 31 Oct 2017, 20:07

Re: Update a 1-side table with many-side values

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Update a 1-side table with many-side values

Post by 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
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

Jeff H
3StarLounger
Posts: 279
Joined: 31 Oct 2017, 20:07

Re: Update a 1-side table with many-side values

Post by Jeff H »

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