No activities.....

[EasyOnly]

Hi All,

I am not sure how to do this but I want to be able to run a query to find any coustmer who did not have any activities since 2 months for example.

The tables involved will be in the following:

SELECT Organization_tbl.OrganizationName, ProjectSub_tbl.[Start date], ProjectSub_tbl.SubDescription, ProjectSub_tbl.[End date]
FROM (Organization_tbl INNER JOIN Projects_tbl ON Organization_tbl.OrganizationID = Projects_tbl.OrganizationID) INNER JOIN ProjectSub_tbl ON Projects_tbl.ProjectID = ProjectSub_tbl.ProjectID;

Thanks

 

[dhookom]

EasyOnly,
You mention "find any customer ... activities" and then provide a sql statement that has neight customer or activity.

Please pretend we don't know anything about your tables, fields, primary or foreign keys, records, or specification prior to posting your questions. The truth is, we don't.

Duane
Hook'D on Access
MS Access MVP

 

[EasyOnly]

dhookom,

Sorry for not being clear here.
organization is the coustomer table and activites is projectsub_table. We have some coustomers who has no activities for months. we would like to have a list with the names so we can call them to make sure they are benefitng from our services. When I run the query mentioned first, it shows me all coustomers and related activities. But if there is no related activities in the projectsub table, it will not show me the coustomer name.

 

[r937]

change [red]INNER JOIN ProjectSub_tbl[/red] to [blue]LEFT OUTER JOIN ProjectSub_tbl[/blue], and add

WHERE ProjectSub_tbl.ProjectID IS NULL



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dhookom]

Or use a subquery:

SELECT OrganizationID, OrganizationName 
FROM Organization_tbl 
WHERE OrganizationID NOT IN 
(SELECT OrganizationID
 FROM Projects_tbl 
 WHERE [End date] >= DateAdd("m",-2,Date()));

Duane
Hook'D on Access
MS Access MVP