fields from different tables in one form

[leerlap]

I'm pretty sure this is a very basic question, but it's tripping me up and any help is appreciated. I have two tables, A and B. Table A is a list of clinicians with their names. Table B is a list of patients. Table A contains variables for ClinicianID and LastName. Table B contains PatientID, ClinicianID, and PtLastName. I have a parent form for the clinicians and a subform for the patients and i have it set up so you click a button from the clinicians main page to pop open the subjects subform pages. How do I add the patient's correct Clinician LastName to the subform form? When I look at the field list, only the variables from Table B are there? I can put the ClinicianID there, but I'd like the name to be there instead. Thanks for your help.

 

[Pat Hartman]

I think that you should change your table structure. A clinician can be a patient and a patient can be a clinician so it makes sense to keep them in the same table. You can add a field that identifies a person as a clinician so that it is easy to populate comboboxes. Also, it should be possible for a given patient to have more than one clinician. Then you would need a relation table that connects a patient with as many clinicians as necessary.

tblPerson:
PersonID (autonumber primary key)
LastName
FirstName
ClinicianFlag
etc.

tblClientPatient:
PatientID (foreign key to tblPerson.PersonID)
ClinicianID (foreign key to tblPerson.PersonID)

When you create queries that join the patients and clinicians you would add tblPerson to the QBE grid twice. Join one instance from the PatientID to the PersonID and the second instance from the ClinitianID to the PersonID.

To answer your question, use a combo and let the wizard build it. You will end up with a field that shows the clinician's name but stores his unique ID.

 

[leerlap]

Thanks for your response. Actually in this case, clinicians are only clinicians and patients are only patients (this is for a research study where we recruit clinicians to participate and then they recruit a few of their patients). So, my problem still remains. Each clinician has an ID number (clinicianID) and this clinicianid is in both the clinician table and patient table (it gets written to both from the clinician form). What i want to do is to be able to look up in the clinician table the name of the clinician using the clinicianid from the patient table and then display that on the patient form. Also, i already have the subject form complete and i really don't want to redo it using the wizard - would take hours to recreate. I just hoped there was a way of pulling data from one table and putting it into a form easily. If you or anyone knows how, please let me know. Thanks again for the help.
Lee

 

[neileg]

Your form should be based on a query, not a table. All you need to do is add the clinician table to the query and drop the clinitian name into the query grid and it is available to add to your form.

I f you have based the form on the patient table, here's how to fix it:
1. Start a new query in design view.
2. Add the Patient table to the top of the grid
3. Drop all of the fields into the query grid and save the query
4. Open your form and change the datasource from the table to the new query. Make sure you get the same results as before.
5. Close and save the form.
6. Open the query in design view. Add the Clinician table. There should be a link between the clinician ID in each table.
7. Add the clinician name from the clinician table to the query grid.
8. Close and save the query.
9. Open the form in design mode. The clinician name is now available in the field picker to add to the form.

 

[leerlap]

Neil - thanks for the quick and detailed reply. The steps all make sense, but can you teach me a bit on why this is how it should be done? I've only used queries in the past for setting up information to be used in reports, so have little knowledge about their real use.

Also, i should mention that the clinicianID in the patient table is only there because i wrote a piece of code in the patient form that puts it there:

Sub Form_BeforeInsert(Cancel as integer)
Me.[clinicianid]=Forms![clinician].[clinicianid]
End Sub

So basically, i'm just duplicating info. In fact, i really could do the same thing with clinician name, but again this is obviously not the best way to do it, but a novices' workaround.

Once i set things up in queries, will the data then get stored in the tables as well? Sorry for the naivete - i appreciate the help.
Lee

 

[neileg]

Neil - thanks for the quick and detailed reply. The steps all make sense, but can you teach me a bit on why this is how it should be done? I've only used queries in the past for setting up information to be used in reports, so have little knowledge about their real use.

The reason you use queries to base your forms on is so that you can do the kind of thing we are talking about! If you use a table, you can't easily bring in data from other tables, or use calculations based on the data.

Also, i should mention that the clinicianID in the patient table is only there because i wrote a piece of code in the patient form that puts it there:

Sub Form_BeforeInsert(Cancel as integer)
Me.[clinicianid]=Forms![clinician].[clinicianid]
End Sub

So basically, i'm just duplicating info. In fact, i really could do the same thing with clinician name, but again this is obviously not the best way to do it, but a novices' workaround.

Well, the way I would have done it would be to use a combo box on the form so the user can select the clinician from a list. Using the name is a bad idea, because names can change. Marriage, divorce, and other life events may require you to change the name, but it's the same clinician.

Once i set things up in queries, will the data then get stored in the tables as well? Sorry for the naivete - i appreciate the help.
Lee

Yes. It is possible to create a query that is non-updateable, but not in this situation. Access will tell you if you create a non-updateable query, these are usually things like total queries and others where the unique link back to the table data is lost.

 

[leerlap]

Neil - thanks so much - very useful - i've already played around with your suggestions and they work great - thanks again.
Lee