I need a query to populate a column based on criteria in table. The criteria is based on 2 dates. I have 2 tables: employee position table and an assignment table(where the employee is scheduled to work). I need to add another column to the assignment table that displays the person's positon. The postion is determined by the dates of the assignment cross referenced to the dates in the position table. See tables below. The last table (assignment table with position) is what I need.
How would I do this? If I can even make a query on this first and then I can always create a make-table query afterwards. I am racking my brain on how to create such a table in Access. I am not very familiar with Cross-tab query but is this what I have to do? Any direction would help! If you are suggesting a cross-tab please outline some steps.
Thanking you in advance
Position Table
Employee Position StartDate EndDate
1234 Clerk-L1 Jan.01/00 Mar.01/01
1234 Clerk-L2 Mar.02/01 Jun.06/03
1234 Manager-L1 Oct.20/04 Null
Assignment Table
Employee Store StartDate EndDate
1234 AAA Jan.01/00 Feb.01/00
1234 BBB Feb.14/00 Jun.01/00
1234 CCC Oct.06/01 Dec.21/01
1234 BBB Oct.31/04 Nov.20/04
1234 CCC Nov.20/04 Null
Assignment Table (with Position column)
Employee Store StartDate EndDate Position
1234 AAA Jan.01/00 Feb.01/00 Query=Clerk-L1
1234 BBB Feb.14/00 Jun.01/00 Query=Clerk-L1
1234 CCC Oct.06/01 Dec.21/01 Query=Clerk-L2
1234 BBB Oct.31/04 Nov.20/04 Query=Manager-L1
1234 CCC Nov.20/04 Null Query=Manager-L1
How would I do this? If I can even make a query on this first and then I can always create a make-table query afterwards. I am racking my brain on how to create such a table in Access. I am not very familiar with Cross-tab query but is this what I have to do? Any direction would help! If you are suggesting a cross-tab please outline some steps.
Thanking you in advance
Position Table
Employee Position StartDate EndDate
1234 Clerk-L1 Jan.01/00 Mar.01/01
1234 Clerk-L2 Mar.02/01 Jun.06/03
1234 Manager-L1 Oct.20/04 Null
Assignment Table
Employee Store StartDate EndDate
1234 AAA Jan.01/00 Feb.01/00
1234 BBB Feb.14/00 Jun.01/00
1234 CCC Oct.06/01 Dec.21/01
1234 BBB Oct.31/04 Nov.20/04
1234 CCC Nov.20/04 Null
Assignment Table (with Position column)
Employee Store StartDate EndDate Position
1234 AAA Jan.01/00 Feb.01/00 Query=Clerk-L1
1234 BBB Feb.14/00 Jun.01/00 Query=Clerk-L1
1234 CCC Oct.06/01 Dec.21/01 Query=Clerk-L2
1234 BBB Oct.31/04 Nov.20/04 Query=Manager-L1
1234 CCC Nov.20/04 Null Query=Manager-L1