Afternoon all,
I have a situation where I am building a flow of training information. The training information comes from two tables that I am currently UNION together to make one table. The issue that I am having is that each table has a course status which is dis-aggregating the results of my union and causing two lines where I want one.
Table of Due Dates:
| Student ID | Course ID | Due Date | Course Status |
| 156724 | Math 101 | 7/1/2021 | Course Due |
| 156724 | Reading 101 | 9/17/2021 | Course Due |
| 156724 | Writing 101 | 3/15/2021 | Course Due |
Table of Completion Dates:
| Student ID | Course ID | Completion Date | Course Status |
| 156724 | Math 101 | 6/30/2021 | Course Pass |
| 156724 | Reading 101 | 9/3/2021 | Course Pass |
| 156724 | Writing 101 | 3/3/2021 | Course Pass |
Results of my Union:
| Student ID | Course ID | Course Date | Course Status |
| 156724 | Math 101 | 7/1/2021 | Course Due |
| 156724 | Math 101 | 6/30/2021 | Course Pass |
| 156724 | Reading 101 | 9/17/2021 | Course Due |
| 156724 | Reading 101 | 9/3/2021 | Course Pass |
| 156724 | Writing 101 | 3/15/2021 | Course Due |
| 156724 | Writing 101 | 3/3/2021 | Course Pass |
What I am looking for:
| Student ID | Course ID | Due Date | Completion Date | Course Status |
| 156724 | Math 101 | 7/1/2021 | 6/30/2021 | Course Pass |
| 156724 | Reading 101 | 9/17/2021 | 9/3/2021 | Course Pass |
| 156724 | Writing 101 | 3/15/2021 | 3/3/2021 | Course Pass |
I have attached a spreadsheet with sample data.
