Relationship Problems (access of course!)

[Fizzio]

I could do with a little help here as I'm stuck.

I have a patient information system and part of this is to record the patient's past medical history.
These items are not set but they can be recorded via a yes/no response.
Therefore I have tblPatientInfo, tblPMH and tblPatientsPMH

tblPatientInfo
PatientID
etc

tblPMH (stores PMH Questions)
PMHID
PMHQuestion

tblPatientPMH
PatientID
PMHID
Response

I want to be able to add PMH items to the patients PMH with a yes/no response but I cannot seem to do it. I've included a demo so I'd be most grateful if anyone could have a look and help me out.

 

[Mile-O]

I'm getting "File Not Found" when I try to open your Access '97 version.

 

[Fizzio]

Pesky VBA. Thanks Mile, try this

 

[Mile-O]

Are you wanting the subform to show all questions for each patient regardless of whether they have been ticked or not?

 

[Fizzio]

Yes Mile.

Essentially, I want to be able to offer all the possibilities to the user and allow them to select which ones are relevant. I have deliberately not hard-coded the PMH questions as they could potentially be added to.

Thanks for your help.

 

[Mile-O]

Sorry, I've tried!

 

[Fizzio]

It's a bugger isn't it. I'm not sure if I need a different table structure to get this to work but I have tried several possibilities.

 

[Fizzio]

I have managed a partial solution by incorporating some code into the before_update of the checkbox on the form, plugging in the patientID


However , The query supplying the form now only picks up those items that are either null or not selected. Surely this can be done

 

[The_Doc_Man]

I have a problem with downloading anything because of where I work, but this problem sounds like a case I ran into a while back.

You want to join two tables but you DON'T want the join to be limited on questions that have not yet been answered.

The solution is to build a join that shows all of your patients and all of the questions. In design-grid view, if you have a relationship between the patient table and the question table, ERASE it for that one query. (You can do that, you know...)

This makes the join what some authorities call a PERMUTATION join. The only catch is that you MUST be careful to not store a record just by passing the joined version thereof.

So perhaps you need to make the query read-only. Behind the controlling form, use recordsets to do the updates if someone checks a box, maybe.

Or, make the parent form show patient data. Make a sub-form show all questions, but make the answers "unbound." When you do a form-current in the sub-form, make it load the question responses that are on record.

You realize, of course, that you need not just Yes/No but also a No Answer option - TRI-valued rather than Boolean.

Sorry if these ideas seem a bit disorganized. But then, it IS a Friday, you know...

 

[Fizzio]

Cheers Doc. I will have a go at these this weekend (instead of doing something probably more useful) and see if
a) I understand what you are talking about!
b) can make any of the solutions work!

Thanks

 

[Fizzio]

Doc, Thanks for your suggestions.

I could not get the permutation join to work, it just kept giving me an empty recordset.

However, your recordset idea had me thinking. How I have this working is probably well away from standard convention but hey, it works!

I essentially, after the insertion of a new patient record, used a dynamic recordset to create a PMH record for each patient. Looping through the PMH options, it copies the PatientID and the PMHID to each record and sets the selection flag to false.

All I have to work out now is if I add a new PMH Item, the best and speediest way of adding this new selection to ALL patients PMH records (I'll worry about this when the time comes though!)

Once again, thanks for your Ideas.

 

[Fizzio]

Here is a zip with the original problem and how I managed to sort of 'fix' it.