Good afternoon!
I'm working yet again on perinatal hepatitis B data entry issues. The spreadsheet approach seemed cumbersome and we really need to go with a database anyway, to be efficient. I've designed a simple database with three main tables--one for MOMS, one for BABIES and one for CONTACTS. There are other tables that support those, but those are the main ones. I've attached a zipped file with the Access 2007 database and a PDF with a question in it.
The question: How can I write a query that will update a foreign key in the BABIES and CONTACTS table when a new case is added? (It'll make more sense, I hope, when you see the documents and the database.)
Don't worry--no confidential info is in here--Gwendolyn Ingolfsson is a fictional character created by S. M. Stirling in his Draka series of novels. The baby is also fictitious and created by S. M. Stirling, but the two contacts are fictional characters developed in some fanfic that Steve Stirling and I wrote back in the late 1990s.
ANYWAY... look it over and see if my question makes sense. I'm tired today and it may not! LOL
MishMish3000
You do not have the required permissions to view the files attached to this post.
You can;t just write a query to update a foreign key like that as there are no linking fields to identify MOM except for MomID. Yoh have frmBabies and frmContacts, in both forms add a combo box that shows MOM info from the Mom table and use the Momid selected from the combo box to update the Momid in the babies table as well as the contacts table.
I hope that is clear.
I'll try that out! Thanks!
I thought about other fields to link but there really aren't any that would be completely consistent. For example, babies can have different last names than their moms. Contacts may or may not have the same last name. We don't collect address information at this level, so that's out. So I'm sort of stuck with MOM_ID.
Thanks for your help!
MishMish3000
I agree with Pat about the form for babies: add a combo box to select the mother of the baby.
If each contact is associated with only one mother, you can do the same on the contacts form.
But if one contact can be associated with several mothers, just like one mother can be associated with several contacts, we say that there is a many-to-many relationship between mothers and contacts. To store this relationship in the database, you need an extra table TblMOM_CONTACTS with two fields: MOM_ID and CONTACT_ID (both Number, Long Integer). Each record in this table describes a unique mother - contact combination. If a mother has two contacts, there will be two records for that mother in tblMOM_CONTACTS, and if a contact is associated with three mothers, there will be three records for that contact.
The way to populate the tblMOM_CONTACTS table is through a subform based on this table on FrmMOMS, linked on MOM_ID, and/or a subform based on this table on FrmCONTACTS, linked on CONTACT_ID. (Both are valid approaches, and they don't exclude each other)
Each contact will only be associated with one mom. Each baby will only be associated with one mom.
Mom may have multiple contacts and may have multiple babies--twins, for instance, or we've had cases where a mom has one baby in 2008 and then another in 2009 and we have to keep track and treat both babies if mom is still hepatitis B positive.
One of the things I was hoping for--and may have to think about how to construct the tables in the database I sent you--is managing the IDs for mom, baby or babies, and contact(s) behind the scenes. It gets very confusing sometimes for the nurses to manage the case IDs. Right now, the regional nurses get a case, call us for a case number (the nurse coordinator here has an Excel file with a simple listing of case numbers--2011-001, 2011-002, etc), and then the regional nurses type the case number into their spreadsheets for mom, baby and contact.
Where it's getting crazy is when mom has twins. We have one case number--2011-001--for two babies and a mom. We also have the situation where mom has 5 contacts in the household. Then we have 2011-001 for mom, two babies and five contacts. We had a case where mom had twins... but one died at one month of age. It was difficult, to say the least, to do queries on the case number to figure things out for monthly reports, due to the poor construction of the whole case number idea.
Of course, this case number and spreadsheet system was set up back in 2008 by a nurse who meant well but had absolutely no experience in Excel, data management, and stuff like that. We're trying to look at what we have and see how to fix things.
Is there a way I can use the Access database I sent to Eileen's Lounge yesterday to do the case numbering behind the scenes so the nurses don't have to use a combo box on any of the forms to assign IDs? Is there a programmatic way to do that? I know it's easier to do data validation rules in Access, instead of Excel, so I'm leaning towards a simple data entry set of forms in the Access database instead of the Excel workbooks discussed in the Excel board in Eileen's Lounge earlier.
Any help or suggestions on how to improve the situation would be greatly appreciated!
The database that you attached doesn't have case numbers (as far as I can tell). The MOM_ID is what ties mothers, babies and contacts together.
A possible way to handle data entry is to make the babies form and the contacts form subforms of the mothers form, linked on MOM_ID. That way, if the user creates a new baby record or contact record, the MOM_ID is automatically filled in from the main form.
You're right, Hans, the database doesn't have the case numbers... I didn't put them in the database because I was hoping to work around needing them.
I like the idea of the subforms. I'll try that out. Anything to help keep the nurses from having to worry about entering or maintaining those numbers... it's just something they don't need to bother with--they're busy enough as it is--and the IDs or case numbers are really for us here at the central office to keep track of cases.
With the Excel sheets and their case number system--at first I thought the case number was actually part of the State of TN patient management database, but it's not--the case numbers are just a list on an Excel sheet. They're an artificial construct. So we don't HAVE to have them.
It would be best, I think, to go with an actual ID system where each new case has a unique ID automatically assigned to it. What do you guys think?
