Selecting Records when relationship is null (A2K)

Topic

New Reply

I have a database that we use to track and order the medications for inmates in a large county jail. I have the primary table as Inmates and many tables that have relationships to this main field. One of the fields is Meds. The state Department of Corrections recently changed their acceptance criteria. I used to have to send a report on each inmate listing their medications along with some demographic information, DOB, SSN, etc., if the inmate had no medications I didn’t need to send anything, that has changed and I now need to send the report with “None” in the medicaion space, and of course the same demographic information.

I have been using a report based on a query with the Inmate and Meds table selected and the criteria being a Yes/No field to select those who will be transferred. It has worked fine, if there were no medications the report would only print out the records with medications.

I need now of course to print out all records, even those without meds and if meds is blank, none currently ordered, to have the “None” listed.

I am lost as to how to do this. Any ideas?

Jail Administrator Medical

Reply | Quote

Replies

Hmmm…. Well my first thought is this….

I’m assuming the Inmate and Meds Tables are “Inner” joined in the query in question… That would show only the inmates showing in the Meds and Inmate tables… I would change the join to a Left Join (using the Inmate table as the primary table) … That would show ALL Inmates… whether they matched the Meds table or not… Then create a new field in the query, to put the None in…

For example:
fldMedsOrdered = IIf(IsNull(fldMeds), “None”, fldMeds)

Does that help at all? (I hope I didn’t completely misunderstand the issue… )

Reply | Quote

How about having a medication called ” None ” or

Reply | Quote

Trudy (Alexya1) has already suggested a query based on Inmates with a left join to Meds. If you indeed have tables Inmates and Meds, that is an excellent idea.

As an alternative to the expression with IIf and IsNull, you can also use the Nz function. This allows you to specify a value to be used if the first argument is Null. Using the same names as in her example, you could create a calculated field in the query:

fldMedsOrdered:Nz([fldMeds],”None”)

Reply | Quote