If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vinson
I suppose by "resolver table" you are referring to the table I've got which
has PtID & Protocol_ID as foreign keys from tblPatient & tbl Protoocols. This is the only location where I include info specific to a given patient's activity relative to a given protocol. In the protocol table I include info that relates only to the protocol - not to the patient - & I don't have a foreign key "PtID" in the protocol table. In the patient table I include info relating only to the patient - not to the protocol - and I don't have a foreign key "Protocol_ID" in the patient table. I do have upcoming patient appointment data in tblPatient, but i haven't figured out how to enter data relative to a new patient and have it populate in existing queries. At this point I'm entering data into tblPt, going to tblPatientProtocolActivity (the "resolver table" you referred to"), and seeing no new patient there... so I then head to a new row, use the combo box derived from PtID (the foreign key from tblPt) to find the new patient, and enter it that way. THEN - and only then - does the new patient appear in the queries I've built. -- Thanks for your time! |
#2
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vinson
I'm not sure I saw a question...
Regards Jeff Boyce Microsoft Office/Access MVP "NC_Sue" wrote in message ... I suppose by "resolver table" you are referring to the table I've got which has PtID & Protocol_ID as foreign keys from tblPatient & tbl Protoocols. This is the only location where I include info specific to a given patient's activity relative to a given protocol. In the protocol table I include info that relates only to the protocol - not to the patient - & I don't have a foreign key "PtID" in the protocol table. In the patient table I include info relating only to the patient - not to the protocol - and I don't have a foreign key "Protocol_ID" in the patient table. I do have upcoming patient appointment data in tblPatient, but i haven't figured out how to enter data relative to a new patient and have it populate in existing queries. At this point I'm entering data into tblPt, going to tblPatientProtocolActivity (the "resolver table" you referred to"), and seeing no new patient there... so I then head to a new row, use the combo box derived from PtID (the foreign key from tblPt) to find the new patient, and enter it that way. THEN - and only then - does the new patient appear in the queries I've built. -- Thanks for your time! |
#3
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vin
Question:
How can I get new patient info entered in light of my table structure without manually entering patient into 2 tables? My query & report seem to work OK on patients already included within the patient table, but it's the new guy that gets added in that doesn't show up. So I suspect it's the form that's the problem. As to table structure, I'm not sure what all info you need. I have the following fields: PtID (autonumber, primary key) LastName FirstName MedicalRecordNumber MDID (foreign key from MD table, works fine) DateOfBirth DateOfDeath CauseOfDeaht ApptDate ApptTime Room#OfInpatients AppointmentNeeded? (yes/no - for pts who don't have a current appt & who aren't inpatients but who NEED an appointment set) Comments (memo to self - things to do for this patient) Those are the biggies. I have a seperate Protocol table: ProtocolID (autonumber, primary key) Protocol name A few other tedious details that aren't pertinent. The 3rd table may be my problem. It contains info specific to any one patient's activities related to any one protocol. Since a protocol may have many patients enrolled and any one patient can enroll in several protocols, I have the structure as follows: PtID (Number... foreign key from tblPt) Prot_ID (Number... foreigh key from tblPt) ScreeningDate Cohort Study# PtStatus Day1Treatment LastStudyTreatment As to relationships, I joined both the PtID & ProtocolID primary keys from the first 2 tables to the PtID & ProtocolID from the 3rd table, enforcing referential integrity and cascading updates. -- Thanks for your time! "Jeff Boyce" wrote: I'm not sure I saw a question... Regards Jeff Boyce Microsoft Office/Access MVP "NC_Sue" wrote in message ... I suppose by "resolver table" you are referring to the table I've got which has PtID & Protocol_ID as foreign keys from tblPatient & tbl Protoocols. This is the only location where I include info specific to a given patient's activity relative to a given protocol. In the protocol table I include info that relates only to the protocol - not to the patient - & I don't have a foreign key "PtID" in the protocol table. In the patient table I include info relating only to the patient - not to the protocol - and I don't have a foreign key "Protocol_ID" in the patient table. I do have upcoming patient appointment data in tblPatient, but i haven't figured out how to enter data relative to a new patient and have it populate in existing queries. At this point I'm entering data into tblPt, going to tblPatientProtocolActivity (the "resolver table" you referred to"), and seeing no new patient there... so I then head to a new row, use the combo box derived from PtID (the foreign key from tblPt) to find the new patient, and enter it that way. THEN - and only then - does the new patient appear in the queries I've built. -- Thanks for your time! |
#4
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vin
On Tue, 26 Dec 2006 08:48:00 -0800, NC_Sue
wrote: Question: How can I get new patient info entered in light of my table structure without manually entering patient into 2 tables? The simplest way would be to use a Form based on the Patients table, with a Subform based on your many-to-many resolver table. Use the PatientID as the Subform's master and child link field. John W. Vinson[MVP] |
#5
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vin
Getting closer? Maybe not.
I've built the form & subform. Oddly enough, on the subform, it's attempting to populate the 2nd row of the datasheet with the patient's info - I'm using a combo box in the subform that allows me to see the name of the patient rather than his unique ID. I see that I now have John Doe listed with all of his protocol-specific info on the subform, then a second line of the datasheet says "John Doe" with all other data fields in that row blank. And I still can't enter new data using the form/subform. I'm getting frustrated, and you guys are saints. -- Thanks for your time! "John Vinson" wrote: On Tue, 26 Dec 2006 08:48:00 -0800, NC_Sue wrote: Question: How can I get new patient info entered in light of my table structure without manually entering patient into 2 tables? The simplest way would be to use a Form based on the Patients table, with a Subform based on your many-to-many resolver table. Use the PatientID as the Subform's master and child link field. John W. Vinson[MVP] |
#6
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vin
You mention "forms" and "datasheets". Are you working entirely in forms, or
are you doing something directly in tables? Why do you need to see the patient in the sub-form? Isn't the sub-form joined to the mainform by the patient ID? Why show it again? What am I missing...? Regards Jeff Boyce Microsoft Office/Access MVP "NC_Sue" wrote in message ... Getting closer? Maybe not. I've built the form & subform. Oddly enough, on the subform, it's attempting to populate the 2nd row of the datasheet with the patient's info - I'm using a combo box in the subform that allows me to see the name of the patient rather than his unique ID. I see that I now have John Doe listed with all of his protocol-specific info on the subform, then a second line of the datasheet says "John Doe" with all other data fields in that row blank. And I still can't enter new data using the form/subform. I'm getting frustrated, and you guys are saints. -- Thanks for your time! "John Vinson" wrote: On Tue, 26 Dec 2006 08:48:00 -0800, NC_Sue wrote: Question: How can I get new patient info entered in light of my table structure without manually entering patient into 2 tables? The simplest way would be to use a Form based on the Patients table, with a Subform based on your many-to-many resolver table. Use the PatientID as the Subform's master and child link field. John W. Vinson[MVP] |
#7
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vin
On Tue, 26 Dec 2006 15:14:00 -0800, NC_Sue
wrote: Getting closer? Maybe not. I've built the form & subform. Oddly enough, on the subform, it's attempting to populate the 2nd row of the datasheet with the patient's info - I'm using a combo box in the subform that allows me to see the name of the patient rather than his unique ID. I see that I now have John Doe listed with all of his protocol-specific info on the subform, then a second line of the datasheet says "John Doe" with all other data fields in that row blank. And I still can't enter new data using the form/subform. I'm getting frustrated, and you guys are saints. Sorry you're frustrated! Some specific questions which might help: - What is the Recordsource property of the Mainform? - What are the Master Link Field and Child Link Field properties of the Subform control? - What is the Recordsource property of the Subform? If it's a query please post the SQL. - What happens when you type something into one of the blank data fields on the second row of the subform? (The John Doe is correct - it just means that when you add a new record it will inherit John Doe's PatientID from the current record on the mainform, if the mainform contained information about Alisia Rodriguez it would show her name, I'm guessing). John W. Vinson[MVP] |
#8
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vin
I'm trying to work entirely in forms. I created the subform in
autoform-datasheet view. And my problem is trying to enter data into the form for a new patient - I'm having to go back to the patient and resolver tables to get a new patient into my database. -- Thanks for your time! "Jeff Boyce" wrote: You mention "forms" and "datasheets". Are you working entirely in forms, or are you doing something directly in tables? Why do you need to see the patient in the sub-form? Isn't the sub-form joined to the mainform by the patient ID? Why show it again? What am I missing...? Regards Jeff Boyce Microsoft Office/Access MVP "NC_Sue" wrote in message ... Getting closer? Maybe not. I've built the form & subform. Oddly enough, on the subform, it's attempting to populate the 2nd row of the datasheet with the patient's info - I'm using a combo box in the subform that allows me to see the name of the patient rather than his unique ID. I see that I now have John Doe listed with all of his protocol-specific info on the subform, then a second line of the datasheet says "John Doe" with all other data fields in that row blank. And I still can't enter new data using the form/subform. I'm getting frustrated, and you guys are saints. -- Thanks for your time! "John Vinson" wrote: On Tue, 26 Dec 2006 08:48:00 -0800, NC_Sue wrote: Question: How can I get new patient info entered in light of my table structure without manually entering patient into 2 tables? The simplest way would be to use a Form based on the Patients table, with a Subform based on your many-to-many resolver table. Use the PatientID as the Subform's master and child link field. John W. Vinson[MVP] |
#9
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vin
The Recordsource property of the Mainform is “tblPatients”.
“PtID” is the Master Link Field and Child Link Field properties of the Subform control. “tblPatientProtocolActivity” is the Recordsource property of the Subform. What happens when you type something into one of the blank data fields on the second row of the subform? (The John Doe is correct - it just means that when you add a new record it will inherit John Doe's PatientID from the current record on the mainform, if the mainform contained information about Alisia Rodriguez it would show her name, I'm guessing). OHMYGOD – It populates like it should – I hadn’t tried that. DUH. John, Jeff – you guys are terrific. Thank you SO much for your patience and expertise!!! -- Thanks for your time! "John Vinson" wrote: On Tue, 26 Dec 2006 15:14:00 -0800, NC_Sue wrote: Getting closer? Maybe not. I've built the form & subform. Oddly enough, on the subform, it's attempting to populate the 2nd row of the datasheet with the patient's info - I'm using a combo box in the subform that allows me to see the name of the patient rather than his unique ID. I see that I now have John Doe listed with all of his protocol-specific info on the subform, then a second line of the datasheet says "John Doe" with all other data fields in that row blank. And I still can't enter new data using the form/subform. I'm getting frustrated, and you guys are saints. Sorry you're frustrated! Some specific questions which might help: - What is the Recordsource property of the Mainform? - What are the Master Link Field and Child Link Field properties of the Subform control? - What is the Recordsource property of the Subform? If it's a query please post the SQL. - What happens when you type something into one of the blank data fields on the second row of the subform? (The John Doe is correct - it just means that when you add a new record it will inherit John Doe's PatientID from the current record on the mainform, if the mainform contained information about Alisia Rodriguez it would show her name, I'm guessing). John W. Vinson[MVP] |
#10
|
|||
|
|||
Continuing plaintive pleas for help from Jeff Boyce & John Vin
OK - so I've normalized the tables & the form with master from tblPatients &
child from tblPatientProtocolActivity works fine, but I still find entering new patients awkward. I can enter a new patient into the form, but am unable to enter the protocol he belongs to (get a message telling me that there is no corresponding record in tblPatientProtocolActivity), and am unable to exit the form (as the full record couldn't be entered) without going to tblPatientProtocolActivity & completing the entry there, i.e. selecting the protocol for the patient. Is there a way to make this less cumbersome? It's still TONS better than my original database (where I would enter a patient multiple times in tblPatient, once for each time he was screened for or enrolled in a protocol). Thanks so much for all your time and talent. -- Thanks for your time! "John Vinson" wrote: On Tue, 26 Dec 2006 15:14:00 -0800, NC_Sue wrote: Getting closer? Maybe not. I've built the form & subform. Oddly enough, on the subform, it's attempting to populate the 2nd row of the datasheet with the patient's info - I'm using a combo box in the subform that allows me to see the name of the patient rather than his unique ID. I see that I now have John Doe listed with all of his protocol-specific info on the subform, then a second line of the datasheet says "John Doe" with all other data fields in that row blank. And I still can't enter new data using the form/subform. I'm getting frustrated, and you guys are saints. Sorry you're frustrated! Some specific questions which might help: - What is the Recordsource property of the Mainform? - What are the Master Link Field and Child Link Field properties of the Subform control? - What is the Recordsource property of the Subform? If it's a query please post the SQL. - What happens when you type something into one of the blank data fields on the second row of the subform? (The John Doe is correct - it just means that when you add a new record it will inherit John Doe's PatientID from the current record on the mainform, if the mainform contained information about Alisia Rodriguez it would show her name, I'm guessing). John W. Vinson[MVP] |
|
Thread Tools | |
Display Modes | |
|
|