温馨提示:本文翻译自stackoverflow.com,查看原文请点击:ms access - Comparing master list of users against a second list with multiple entries per user

ms access - 将用户的主列表与第二列表进行比较,第二列表具有每个用户多个条目

发布于 2020-03-27 12:02:31

我想根据某些标准确定每个用户还剩下什么课程。我收到2个数据集。我想使用MS Access 2013来处理数据。

数据集1:所有用户的主列表

数据集2:每个用户的课程完成列表

根据用户所属的类别确定他们需要完成哪些课程。数据集并不完全干净也不完美。

数据集1

NAME           CATEGORY
TOASTER, OVEN      3
MICRO, WAVE        2
WHITE, BOARD       1
MOUSE, PAD         1
KEY, BOARD         4
PHONE, CHARGER     2
WATER, BOTTLE      2

数据集2

NAME          COURSE    DATE
TOASTER, OVEN   3A  2000-05-05
TOASTER, OVEN   3B  2019-03-02
TOASTER, OVEN   3A  2018-11-05
MICRO, WAVE     2A  2017-10-20
WHITE, BOARD    1A  2018-01-10
KEY, BOARD     3A-1 2019-01-15
PHONE, CHARGER 2A-1 2018-09-17
WATER, BOTTLE 2A-CL 2015-06-26
WATER, BOTTLE   2A  2011-12-15

期望的结果

NAME        CATEGORY    REQUIREMENT   STATUS
TOASTER, OVEN   3       ADVANCED      COMPLETED
MICRO, WAVE     2       INTERMEDIATE  COURSE 2 REMAINING
WHITE, BOARD    1       INTRODUCTORY  COMPLETED
MOUSE, PAD      1       INTRODUCTORY  NOT COMPLETED
KEY, BOARD      4       ADVANCED      COURSE 1 REMAINING
PHONE, CHARGER  2       INTERMEDIATE  COURSE 2 REMAINING
WATER, BOTTLE   2       INTERMEDIATE  COMPLETED

基于这两个数据集,我希望期望的结果显示数据集1的另外两列。

逻辑:

如果CATEGORY = 3REQUIREMENT = ADVANCED如果CATEGORY = 2REQUIREMENT = INTERMEDIATE如果CATEGORY = 1REQUIREMENT = INTRODUCTORY这种逻辑,我可以执行。

If REQUIREMENT = ADVANCED, then check Data Set 2 to see if course 3A AND 3B exist, OR if 3A-CL, OR if 3A-EQ exist, then STATUS = COMPLETED, ELSE IF ONLY 3A exists, then say Course 2 Remaining, ELSE IF ONLY 3B exists, then say Course 1 Remaining.

If REQUIREMENT = INTERMEDIATE, then check Data Set 2 to see if course 2A and 2B exist, OR if 2A-CL, OR if 2A-EQ exist, then STATUS = COMPLETED, ELSE IF ONLY 2A exists, then say Course 2 Remaining, ELSE IF ONLY 3B exists, then say Course 1 Remaining.

If REQUIREMENT = INTRODUCTORY, then check Data Set 2 to see if Course 1A OR 1A-CL OR 1A-EQ exist, then STATUS = COMPLETED, ELSE NOT COMPLETED.

Please let me know if I need to clarify anything.

I would appreciate any assistance or guidance on this at all!

查看更多

查看更多

提问者
MonkeyMonkey
被浏览
26
SunKnight0 2019-07-04 03:31

一种方法是对第二个数据集进行多次联接。此示例包括完成任务所需的所有元素。您只需要将其扩展到所需逻辑的其余部分即可:

SELECT T1.NAME,
    T1.CATEGORY,
    ISNULL(IS_3A.DATE) AS EXISTS_3A_EXAMPLE,
    NOT (ISNULL(IS_3A.DATE) OR ISNULL(IS_3B.DATE)) AS EXISTS_3A_AND_3B_EXAMPLE
FROM (( [DATA SET 1] T1
    LEFT JOIN [DATA SET 2] IS_3A ON IS_3A.COURSE='3A' AND T1.NAME=IS_3A.NAME )
    LEFT JOIN [DATA SET 2] IS_3B ON IS_3B.COURSE='3B' AND T1.NAME=IS_3B.NAME )
    LEFT JOIN [DATA SET 2] IS_3A_CL ON IS_3A_CL.COURSE='3A-CL' AND T1.NAME=IS_3A_CL.NAME

发布
问题

分享
好友

手机
浏览

扫码手机浏览