Making a form to update multiple tables

[LakshinK]

Hello All!

I am relatively new to MS Access and come from an academic/biostats background. I am looking to make a database in access to hold leftover samples from old studies that my team has worked on. Here's a picture of my relationships and tables.

I think most of the tables are pretty self-explanatory but I'll describe them shortly:
StudyData: contains data on studies (Name, description)
Sites: has data on the sites that participate in each study
TimePts: Lists the timepoints from which samples were collected for each study
Patient: Lists patient IDs and associates them with a site and a study. Pt_ID, the PK, is an autonumber
Collections: Lists collection events. Collection_ID is an autonumber
Specimens: Lists the specimens for a given collection time point. The reason is because multiple specimens of different types can come from one collection time point.
Finally, Locations: I made this its own table but it could easily be absorbed into the specimens table as it is a 1:1 relationship.

I am making this database for my team and am hoping to create some forms for easy data entry. I have one form that allows you to input a study, its dedicated sites, and the time points in which samples are collected. From here, I am hoping to make a form that allows my team to do a lot (maybe a bit too much) at once. The goals are as follows:
1. Enter a pt ID for a new or existing pt in the database
2. Pick the study and site that they are associated with
3. Select the collection date, collection time point, and notes for the collection (ignoring status for now)
4. In a subform, list all of the specimen data (type, volume, location)

I have been trying to get it in this format (gotten from the form creation wizard)

but have been largely unsuccessful, and keep getting cannot add records; join key of table sites not in recordset errors. I am also trying to do it by hand but I am struggling to link the subform to the main form through collection_id.

Does this seem like a valid approach and if so, any tips on how I can go about this? If not, please let me know and I will pivot to some other format.

Thanks in advance for your access wizardry!

Best,
Lakshin K.

 

[DHookom]

I would change the table structures but it's difficult without some basic specifications.

To start, I would use an autonumber primary key in all of my tables. There should be a Sites table with the SiteID and Site_Name. Then your current Sites table would become a junction table of SiteID and StudyID with its own primary key of SiteStudyID which would be stored in the Patient table (only if a patient can be part of only one study). If more that one study is possible, you would need another junction table that joins Patients with the Site and Study table.

There may be other issues but again, your requirements are clear.

 

[The_Doc_Man]

A comment on your schema...

If "Sites" is a table with only two values in it, I could see you using it as a way to limit combinations of Site and Study. But I question your other references - and non-references - to Site. For instance, it would seem that you worry about the Site for the patient because Site is in the patient table, but I would think that the site SHOULD BE a property of your collection. Putting Site in the patient table makes it seem like you care about where the patient was, but you would be able to derive that if Site were in the collection table, I think. Particularly since Patient ID is in the collection table as well. The rule for normalization is that an attribute should uniquely belong to a thing. Where does site belong? If you have to use a complex query to derive sites for patients, that should be OK. But could it ever happen that a patient could get a collection done one day on one site for one study and a different collection done on a different site for a different study on another day? I don't think your structure would do that.

Implied in your structure is that a patient can participate in only one study (see the PK of the patient table, which lists the Study ID as a property) but can a study have more than one patient?

I think what you need to do is look hard at your tables, which seem close to what you want but maybe they are a bit restrictive. Then look up this concept: "Junction Tables" and see how you can easily associate patients with sites, studies, or collections freely.

 

[plog]

From a developers perspective, 1 form based on 1 table and you'll never go wrong. From a users perspective, by using pop-up forms, tabs and subforms a lot of those forms are seemingly integrated into 1 form.

So no, you can't do all those things you want with one form. Or even from a users perspective, your gonna have to go to various forms to accomplish everything they need to.

With that said, first make those fixes others talked about to your tables, then work on generating the output (reports) you need from this. No point building amazingly beautiful forms if they just throw the data down a pit you can't retrieve anything in a meaningful way from

 

[GaP42]

Reinforcing the foundations: you must get your database schema right for the job you want to do, which as others above have mentioned and offered some insights.
From what has been described by you I would suggest some changes / thoughts about the rules/relationships you are interested in:

1. A Patient may present at one or more CollectionEvents
2. A CollectionEvent may result in the collection of one or more Specimens
3. A Specimen must be stored in one SpecimenLocation.
4. Not described by you, however you may need to know at which sites these specimens are located: A Site may host one or more SpecimenLocations
5. A Specimen may be utilised in one or more Studies, and a Study involves one or more specimens. This M:M relationship is resolved by creating the table StudySpecimen with the 1:N (one to many relationship from Study to StudySpecimen and Specimen to StudySpecimen). However you also have the concept of TimePoint: "Lists the timepoints from which samples were collected for each study" - this suggests to me that for a specimen, multiple samples can be taken/assessed over time - to become datapoints in the study. If this is the case then the previous description of the resolution needs to be resolved further: A StudySpecimen is involved in one or more studies and a Study utilises one or more StudySpecimens - so the resolution requires SampleTimePoint. (see below). I am UNSURE of the distinction between SPECIMEN and SAMPLE in the description you provided, and therefore the need for the creation of SampleTimePoint - the data here might belong with StudySpecimen
6. A Study involves the participation of one or more Sites. A Site may be involved in one or more Studies. Resolved as StudySite

NB: your data as described to date, does not have a place to record a result (which would appear to be SampleTimePoint)
The creation of a study will involve identification of suitable specimens/ patients. This database is only used to record participation (through Sample/Specimen/Collection/Patient) in Studies. It allows the same Specimen to involved in multiple Studies (as multiple samples)

I am making this database for my team and am hoping to create some forms for easy data entry. I have one form that allows you to input a study, its dedicated sites, and the time points in which samples are collected. From here, I am hoping to make a form that allows my team to do a lot (maybe a bit too much) at once. The goals are as follows:
1. Enter a pt ID for a new or existing pt in the database
2. Pick the study and site that they are associated with
3. Select the collection date, collection time point, and notes for the collection (ignoring status for now)
4. In a subform, list all of the specimen data (type, volume, location)

From what is described above, the Study is initially designed where sites are enrolled, there is a protocol for collection of Samples (at multiple timepoints - so the Patients are enrolled prospectively too?). - note prior comment re identification of suitable patients
The entry of a Patient ID - this can only be patients recruited in studies, or if new require the entry of patient demographics .. as needed, then move to the study, site, collection details. So is this database to be used at the point of collection? (the PatientID is internal to the db application - it is not the MRN)
It may be that you also need navigation via Study/Site/then identify a specimen held at the site and record the SampleTimePoint (and thereby the patient involved) - not sure of what the end user workflows will be - you need to work these out.

 

[Pat Hartman]

Once you clean up the tables a little as the others have suggested, we can move on to the forms. I'm posting a very simple example of how a one - many relationship works. You will need to look at the code but there is very little needed to make this type of mainform/subform and mainform/popup work. I also added an option that shows how a mainform/popup form works. You need code in the beforeInsert event that ensures that the foreign key gets populated when a new record is added to the child table.

One-to-many sample database

This is the simplest example I could make. It is states as the main form and cities as the subform. The sample includes a mainform with a subform and a button to open the subform as a popup so you can see how to handle populating the FK for each new record in the popup form. Access does this...

www.access-programmers.co.uk