Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting Records when relationship is null (A2K)

    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

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Records when relationship is null (A2K)

    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... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Records when relationship is null (A2K)

    How about having a medication called " None " or

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting Records when relationship is null (A2K)

    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")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •