Hi,
I have two tables:
Travellers_Report
Name Country Place_of_visit Return_date
A United States Los Angeles 2/27/2013
B United Arab Emirates DUBAI 2/21/2013
C Puerto Rico San Juan 2/28/2013
D United States Los Angeles 2/27/2013
E United States Boston 2/27/2013
F Malaysia Kulalampur 3/1/2013
G United Arab Emirates DUBAI 2/15/2013
H Malaysia Kulalampur 3/15/2013
I United States Los Angeles 2/27/2013
and Company_presence
Country City
United States Los Angeles
United Arab Emirates DUBAI
United States Boston
I need to compare the 'Travellers_Report' table's 'Place_of_visit' field to 'Company_presence' table's 'City field' and get the count.
Expected output is:
Country Place_of_visit Count
United States Los Angeles 3
United Arab Emirates DUBAI 2
United States Boston 1
SELECT sub.company_Presence.Facility, Count(company_Presence.Facility) AS CityCount FROM (SELECT Travellers_report.*, company_Presence.Location, company_Presence.Facility from Travellers_report,company_Presence WHERE Travellers_report.Return_Date >= #2/20/2013# AND Travellers_report.Return_Date <=#2/27/2013# and Travellers_report.Place_of_Visit = company_Presence.Facility) AS sub GROUP BY sub.company_Presence.Facility
This query gets me the result for 'Travellers_report.Place_of_Visit = company_Presence.Facility'
How to change this query to get records that are not matching? 'Travellers_report.Place_of_Visit <> company_Presence.Facility' is not showing the correct result.
In short:
1.Need to show the count of travellers reg to location where the company presence is there. in one listbox (is working with the above query)
2.Need to show the count of travellers reg to location where the company presence is NOT there (<> not working)
Pls suggest some solutions....
Bookmarks