Full Version: Subquery for control box row source
UtterAccess Forums > Microsoft® Access > Access Forms
rlnathan
I have a form that stored information for different study subjects. The PK is the subject ID. Our subjects are children, so I also have a Caregiver table that stores parental information. They are connected by FamilyID, which is the first 4 numbers of the SubjectID and CaregiverID. One Caregiver may be associated with more than one Subject.
created a Query to list all the Caregivers.
SELECT Caregiver.CaregiverID, Caregiver.FirstName, Caregiver.LastName
FROM Caregiver;
Now, when a form is filled out for a specific subject, I would like the combo box to only show the Caregivers associated with that specific subject.
Otried putting "Like (mid([SjID],1,4))" as the control source and the Caregiver query as the row source, but it simply shows ALL of the Caregivers.
How do I get the combo box to only show the Caregivers associated with that specific subject?
Thanks!
ScottGem
You stated that a caregiver can have more than one subject, but can a subject have more than one caregiver?
If not, then you don't need a combo. The CaregiverID is a foreign key in the Subject table.
rlnathan
Yes, there could be more than one caregiver for each subject.
Thanks.
ScottGem
In that case you have a many to many relation and need a junction table. You alkso should not be using a combination of values as a PK. So you should have three tables:
blSubjects
SubjectID (PK autonumber)
FName
LName
etc.
tblCaregivers
CaregiverID (PK Autonumber)
FName
LName
etc
tjxSubjectCaregivers
SubjectCaregiverID (PK Autonumber)
SubjectID (FK)
CaregiverID (FK)
Now if need to list caregivers for a subject in a combo use a Rowsource like:
SELECT CareGiverID, FName, LName
INNER JOIN tblCareGivers.CareGiverID = tjxSubjectCaregivers,CaregiverID
FROM tblCaregivers, tjxSubjectCaregivers
WHERE SubjectID = Forms!formname!txtSubjectID;
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.