Lookup box + Query

[jondavidf]

Hey all,

I have a table, tblPtMeds, with an "Rx_Medication" field. The field is a combo box that serves to lookup list values from another table, tblLstMeds. There are actually 3 columns in the lookup table (tblLstMeds) that include columns for Generic Name, Brand Name, and Drug Class. If selected, only the Generic Name will be passed to the Rx_Medication field, though. See screenshot for entries from tblPtMeds and tblLstMeds.


(above left is the Combo box from tblPtMeds and above right is once the values have been selected)

(above is from tblLstMeds)

This box will allow users to enter Generic Names of medications in the tblPtMeds via a drop-down which contains common medications prescribed (~350 records). I also want users to be able to free type in responses to the Rx_Medication field in case the medication isn't listed in the drop-down. Everything is working as desired up to this point.

The problem that I'm having is when I try to query the Rx_Medication (Generic) field. I'd like to show all records (selected via combo box or free typed) for this field, and show the Brand Name if it was selected via the combo box. I'm pretty sure I need to use the "Join" functionality but my SQL is quite rusty so I'm not 100% sure about that. Using the wizard, I can return records from tblPtMeds where there are matching values in tblLstMeds but I'm missing the records without matching values. Essentially the records that have been free-typed are not showing up. See screenshot.


(above is the query I created - notice GettemBetter is missing from the query)

Any help would be greatly appreciated.

Thanks!

Jon

[pbaldy]

Double or right click on the join line to edit it. The appropriate choice should be apparent. In SQL, that will change the INNER join to LEFT or RIGHT as appropriate.

[jondavidf]

Perfect. Thank you!

[pbaldy]

Happy to help!