MS Access查询以使用两个字段来区分以显示table1中不在table2中的行. (左外联接?)

问题描述

表1:SalesforceReportWeekly；字段:[机会:商店编号]，[目标电路完成(FOC)]，...

Table 1: SalesforceReportWeekly ; Fields: [Opportunity: Store Number], [Target Circuit Completion (FOC)], ...

表2:SentSalesforceReportWeekly字段:[机会:商店编号]，[目标电路完成(FOC)]，...

Table 2: SentSalesforceReportWeekly Fields: [Opportunity: Store Number], [Target Circuit Completion (FOC)], ...

我需要一个在MS Access中工作的查询，该查询将显示SalesforceReportWeekly中的所有行，而SentSalesforceReportWeekly中没有相应的行.使用[商机:商店编号]和[目标电路完成(FOC)]字段.在[结果:商店编号]和[目标电路完成(FOC)]都相同的情况下，需要从我的结果视图中删除这些记录.我的结果视图中需要包含SalesforceReportWeekly中具有相同商店编号但与[SentSalesforceReportWeekly]中的记录不同的[目标电路完成(FOC)]的记录.这必须在MS Access中起作用.我觉得我已经尝试了一切:

I need a query that will work in MS Access which will show all rows in the SalesforceReportWeekly that do not have a corresponding row in the SentSalesforceReportWeekly. using the [Opportunity: Store Number] AND the [Target Circuit Completion (FOC)] fields. records where both the [Opportunity: Store Number] and the [Target Circuit Completion (FOC)] are the same need to be omitted from my result view. a record in SalesforceReportWeekly with an identical store number but a different [Target Circuit Completion (FOC)] to a record in SentSalesforceReportWeekly needs to be included in my results view. This has to work in MS Access. I feel like I have tried everything:

尝试一个:由于某种原因，这几乎返回了整个表格...

Attempt One: for some reason this returns almost the entire table...

SELECT *
       FROM [SalesforceReportWeekly]
            LEFT OUTER JOIN [SentSalesforceReportWeekly]
                 ON ([SalesforceReportWeekly].[Opportunity: Store Number] = [SentSalesforceReportWeekly].[Opportunity: Store Number] AND [SalesforceReportWeekly].[Target Circuit Completion (FOC)] = [SentSalesforceReportWeekly].[Target Circuit Completion (FOC)])
                     WHERE [SentSalesforceReportWeekly].[Target Circuit Completion (FOC)] IS NULL AND [SentSalesforceReportWeekly].[Opportunity: Store Number] IS NULL;

尝试二:返回结果视图，其中包含成吨的空行(已发送表中的行除外).

Attempt Two: returns a result view with tons of null rows except for the rows which are in the sent table.

SELECT * 
       FROM [SalesforceWeeklyReportFutureInstalls]
            LEFT JOIN [SentInstallScheduled] ON ([SalesforceWeeklyReportFutureInstalls].[Opportunity: Store Number] = [SentInstallScheduled].[StoreNumber] AND [SalesforceWeeklyReportFutureInstalls].[Target Circuit Completion (FOC)] = [SentInstallScheduled].[TargetCircuitCompletionFOC])
UNION
SELECT *
       FROM [SalesforceWeeklyReportFutureInstalls]
            RIGHT JOIN [SentInstallScheduled] ON ([SalesforceWeeklyReportFutureInstalls].[Opportunity: Store Number] = [SentInstallScheduled].[StoreNumber] AND [SalesforceWeeklyReportFutureInstalls].[Target Circuit Completion (FOC)] = [SentInstallScheduled].[TargetCircuitCompletionFOC]);

我花了数小时梳理其他SO问题，但发现了其他类似问题，但没有一个完全像我的问题(两个表从两个字段中的另一个表中排除了一个表的结果)，并且无法成功地将其解决方案用于符合我的需求.任何帮助都将不胜感激！

I have spent hours combing through other SO questions, i have found other similar problems but none exactly like mine(two tables excluding results of one table from another on two fields) and haven't been able to successfully adapt their solutions to fit my needs. Any and all help will be very appreciated!

推荐答案

查找另一个表中不存在的行的标准方法是利用NOT EXISTS:

The standard way to find rows which don't exists in another table utilizes NOT EXISTS:

SELECT *
FROM [SalesforceReportWeekly]
WHERE NOT EXISTS
  (SELECT * FROM [SentSalesforceReportWeekly]
   WHERE [SalesforceReportWeekly].[Opportunity: Store Number] = [SentSalesforceReportWeekly].[Opportunity: Store Number] 
     AND [SalesforceReportWeekly].[Target Circuit Completion (FOC)] = [SentSalesforceReportWeekly].[Target Circuit Completion (FOC)]
  )

这篇关于MS Access查询以使用两个字段来区分以显示table1中不在table2中的行. (左外联接?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！