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

  • Thread starter Thread starter Mary
  • Start date Start date
M

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
 
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
'====================================================
 
Sweet! That was the query I was looking for. It worked great! Thanks John. I
appreciate your help. All the best my friend
 
Back
Top