Hello all,
I'm having an issue right now linking up multiple tables and I can't seem to find a solution
I have the following tables
- Date Table - Used as master date table to link all tables. Used in slicers.
- Person Details Table - This table has a record for each employee for each day and a few attributes about themselves
- Work Details Table - This table has a record for each day an employee worked
- Absent Details Table - This table has a record for each day an employee worked
Right now I have all working properly besides the Absent table. I just added this and I expected to create a relationship with the Date Table and Person Details Table but the application is stoping me. Here are the current relationships
- Date Table to Work Details Table - On Date - Many to One
- Work Details Table to Person Details Table - On ID - Many to Many
The plan was to add the absent table, make a relationship to Date Table (On Date) and Person Details Table (On ID) that way when I filter use the Date Table I'll get the correct data for that day in the Personnel Details table as well as any Work Details/Absent Details associated with that date as well.
Here some sample tables.
Date Table
| Date |
| 3/1/2020 |
| 3/2/2020 |
| 3/3/2020 |
| 3/4/2020 |
| 3/5/2020 |
| 3/6/2020 |
| 3/7/2020 |
| 3/8/2020 |
| 3/9/2020 |
| 3/10/2020 |
| 3/11/2020 |
| 3/12/2020 |
| 3/13/2020 |
Personnel Details Table
| date | id | name | age |
| 3/1/2020 | a | john | 50 |
| 3/1/2020 | b | james | 50 |
| 3/1/2020 | c | joe | 50 |
| 3/2/2020 | a | john | 50 |
| 3/2/2020 | b | james | 51 |
| 3/2/2020 | c | joe | 50 |
| 3/3/2020 | a | john | 50 |
| 3/3/2020 | b | james | 51 |
| 3/3/2020 | c | joe | 50 |
| 3/4/2020 | a | john | 50 |
| 3/4/2020 | b | james | 51 |
| 3/4/2020 | c | joe | 50 |
Work Details Table
| date | id | job type |
| 3/1/2020 | a | chef |
| 3/1/2020 | b | mechanic |
| 3/1/2020 | c | security |
| 3/2/2020 | a | chef |
| 3/2/2020 | b | mechanic |
| 3/2/2020 | c | security |
| 3/3/2020 | a | chef |
| 3/3/2020 | b | security |
| 3/3/2020 | c | gardner |
Absent Details Table
| date | id | reason |
| 3/4/2020 | a | scheduled day off |
| 3/4/2020 | b | scheduled day off |
| 3/4/2020 | c | scheduled day off |
| 3/5/2020 | b | vacation |
| 3/5/2020 | c | vacation |
Any help would be appreciated!
