Query that Selects all rows from one Table and Adds a Third Column

[Mary]

I am kinda rusty with my SQL skills. I was wondering if someone out there
could help me out. I have two tables TradeMedications and GenericMedications
with the following fields.

TradeMedications Table
ID (Primary Key) TradeName Strength Unit
1 Norco Tablet 10 MG
2 Suprax Tablet 5 MG
3 Vicodin Tablet 5 MG
4 Hycamtin 5 MG

GenericMedications Table
TradeName GenericName
Norco Tablet Hyrdrocodone
Hycamtin Topetecan Hydrochloride

I want to create a query that will list all of the rows in TradeMedications
and where a TradeName exists in both TradeMedications and GenericMedications,
I would like to append the GenericName to the end. I would like it blank if
it does not exist. So the result would look something like this.

Query Result
ID TradeName Strength Unit GenericName
1 Norco Tablet 10 MG Hyrdrocodone
2 Suprax Tablet 5 MG
3 Vicodin Tablet 5 MG
4 Hycamtin 5 MG Topetecan
Hydrochloride

 

[John Spencer]

SELECT TradeMedications.ID
, TradeMedications.TradeName
, TradeMedications.Strength
, TradeMedications.Unit
, GenericMedications.GenericName
FROM TradeMedications LEFT JOIN GenericMedications
ON TradeMedications.TradeName = GenericMedications.TradeName

If you are using the design view - query grid
-- Add both tables
-- drage from TradeName to TradeName to set up a join
-- double-click on the join line and select the option (2 or 3) that
gives you ALL records in TradeMedications and only matching in
GenericMedications
-- Select the fields you wish to display.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Mary]

Sweet! That was the query I was looking for. It worked great! Thanks John. I
appreciate your help. All the best my friend