I am trying to determine what courses each user has remaining based on certain criteria. I receive 2 data sets. I would like to use MS Access 2013 to process the data.
Data Set 1: Master list of all users
Data Set 2: Course completion list for each user
Depending which category a user falls under determines which courses they need to complete. The data set is not entirely clean nor is it perfect.
Data Set 1
NAME CATEGORY
TOASTER, OVEN 3
MICRO, WAVE 2
WHITE, BOARD 1
MOUSE, PAD 1
KEY, BOARD 4
PHONE, CHARGER 2
WATER, BOTTLE 2
Data Set 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
DESIRED RESULTS
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
Based on the 2 data sets, I would like the desired results to show 2 additional columns to Data Set 1.
Logic:
If CATEGORY = 3, REQUIREMENT = ADVANCED.
If CATEGORY = 2, REQUIREMENT = INTERMEDIATE.
If CATEGORY = 1, REQUIREMENT = INTRODUCTORY.
This logic, I can perform.
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!
What is the
ISNULL(IS_3A.DATE) AS EXISTS_3A_EXAMPLE, NOT (ISNULL(IS_3A.DATE) OR ISNULL(IS_3B.DATE)) AS EXISTS_3A_AND_3B_EXAMPLEpiece of the SQL doing? The 3rd row in your SQL is checking if theDATEfield in [DATA SET 2] exists (is null)? Then the 4th row is checking if both 3A or 3B exist in the course completion, with theNOT(ISNULL(...)function. Please correct me if I am wrong on either of those. I don't quite understand theLEFT JOINlogic there as well. What isIS_3A,IS_3B, andIS_3A_CLin your SQL? I understand theNAMEportion, but no the course.The third and fourth line are examples of how you can use the various linked copies of your second table to run your logic. What the
LEFT JOINdoes is that it creates a situation where if you have a record with that course (for example3Aon the first join, then the fields of thecorresponding ALIASEDcopy of the table (in this caseIS_3A) will have the values of that record. If you do not have a3Afor that person, then they will be null. ThereforeISNULLon any of these fields (I choseDATEbut any would work) tells you if you have a record for thatNAME-COURSEcombination.So, my 4th line of code essentially does this from your requirement: "check Data Set 2 to see if course 3A AND 3B exist"
Got it! I realized those were aliases when I woke up this morning, whoops! I've been trying to implement your solution and starting off small first. I am trying to identify only one type of training at a time. Since [DATA SET 1] has a unique record set for each user and [DATA SET 2] has potential duplicate values for each user, my left join duplicates some of the values as well so I end up with more records than I anticipated. I use
DISTINCTand the number of records is correct. Also, all the values are ending up as not null (TRUE).Here is the SQL I am using in case my syntax is wrong. Everything is showing up as
0when I am expecting quite a few1values orTRUE.SELECT DISTINCT R.*, IsNull(A.[Training Level]) AS [ADVANCED - DL] FROM qryRequired AS R LEFT JOIN qry_CES_Bucket AS A ON ((A.[Training Level]="ADVANCED - DL") AND (R.[Name Pers] = A.[Name Pers]));