Query Data excluding records based from another table

[AndrewCorkery]

Hi,

I have a query that I wish to limit data retrieved based on data from another table.

The field contains data of many country names and I only want to return country names that are NOT included in a seperate table.

The field below contain data that needs to be queried:

Country Name
Singapore
Singapore
Singapore
Singapore
Singapore
South Africa
South Africa
South Africa
United Kingdom
United Kingdom
United Kingdom
United States
United States
United States
AUSTRALIA
NEW ZEALAND



This is the table that I wish to use to query and excluded records from the above table if they are equal.

CountryFullName
AUSTRALIA
NEW ZEALAND
PAPUA NEW GUINEA

 

[BGrego]

"SELECT * FROM TopTable WHERE [Country Name] NOT IN (SELECT CountryFullName FROM BottomTable);"

 

[AndrewCorkery]

Hi,

I have the SQL below and cannot get it to work, please could you add the code for me? thanks v much.

SELECT Current_issinfo.[Partner Name], EQTradingAustralia.[Risk Class], Current_issinfo.[Partner Key], EQTradingAustralia.[Risk Domicile], Current_issinfo.[Internal Country Rating], Current_issinfo.[Model Credit Rating], Current_issinfo.[Credit Officer Owner], Current_issinfo.[Market Risk Officer Owner (Debt)], Current_issinfo.[Market Risk Officer Owner (Equity)], EQTradingAustralia.Function, EQTradingAustralia.[Loss Given Event (Mio CHF)], EQTradingAustralia.[Max Default Exposure]
FROM Current_issinfo INNER JOIN EQTradingAustralia ON (Current_issinfo.[Partner Key] = EQTradingAustralia.[Partner Key]) AND (Current_issinfo.[Partner Name] = EQTradingAustralia.[Partner Name])
WHERE (((Current_issinfo.[Partner Name])<>&quot;UNKNOWN&quot);

 

[BGrego]

SELECT Current_issinfo.[Partner Name], EQTradingAustralia.[Risk Class], Current_issinfo.[Partner Key], EQTradingAustralia.[Risk Domicile], Current_issinfo.[Internal Country Rating], Current_issinfo.[Model Credit Rating], Current_issinfo.[Credit Officer Owner], Current_issinfo.[Market Risk Officer Owner (Debt)], Current_issinfo.[Market Risk Officer Owner (Equity)], EQTradingAustralia.Function, EQTradingAustralia.[Loss Given Event (Mio CHF)], EQTradingAustralia.[Max Default Exposure]
FROM Current_issinfo INNER JOIN EQTradingAustralia ON Current_issinfo.[Partner Key] = EQTradingAustralia.[Partner Key]
WHERE Current_issinfo.[Partner Name])<> &quot;UNKNOWN&quot;;

 

[AvGuy]

Use the Access query wizard to create a query showing unmatched records. Don't reinvent the wheel.

AvGuy