Query a column based on date ranges in another table (cross-tab?) 1

[Hinfer]

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

 

[PCLewis]

This is an interesting problem which I want to reply to in full but I am called away for now. Whilst gone, can you verify the gap in the Position table between Jun.06/03 and Oct.20/04? I assume there was no employment during that period yet Also the various gaps (big one Dec.21/01 to Oct.31/04) in Assignment table I assume were due to the Employee being unassigned. It only makes a difference because my preliminary query results in:

Employee  Store	StartDate	EndDate	    Position
1234	  AAA	1/01/2000	1/02/2000   Clerk-L1
1234	  BBB	14/02/2000	1/06/2000	
1234		2/03/2001	6/06/2003   Clerk-L2
1234	  CCC	6/10/2001	21/12/2001	
1234		20/10/2004		    Manager-L1
1234	  BBB	31/10/2004	20/11/2004	
1234	  CCC	20/11/2004

More later

 

[PCLewis]

Well, it's a bit of a behemoth query and it does use DMax but it's a single SQL statement to do what you want. I did not make it match your expected output precisely because of the gaps. I could just remove the Null Stores like you have but maybe you will prefer seeing it this way:

SELECT AssignmentPositions.Employee, DMax("Store","Assignment","[StartDate]<=#" & Format([StartDate],"d-mmm-yyyy") & "# and ([EndDate]>=#" & Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & "# or[EndDate] IS NULL)") AS Store, AssignmentPositions.StartDate, AssignmentPositions.EndDate, DMax("Position","Position","[StartDate]<=#" & Format([StartDate],"d-mmm-yyyy") & "# and ([EndDate]>=#" & Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & "# or[EndDate] IS NULL)") AS [Position]
FROM [SELECT Q1.Employee, Q1.StartDate, Min(Q1.EndDate) AS EndDate
FROM (SELECT Position.Employee as Employee,  Position.StartDate as StartDate, Position.EndDate as EndDate
FROM [Position] 
UNION ALL
SELECT Assignment.Employee as Employee, Assignment.StartDate as StartDate, Assignment.EndDate as EndDate
FROM Assignment) AS Q1
GROUP BY Q1.Employee, Q1.StartDate
ORDER BY Q1.StartDate]. AS AssignmentPositions;

Our results agree on the lines i marked with "*".

Employee  Store   StartDate   EndDate   Position  
1234      AAA     Jan.01/00   Feb.01/00 Clerk-L1    *
1234      BBB     Feb.14/00   Jun.01/00 Clerk-L1    *
1234              Mar.02/01   Jun.06/03 Clerk-L2    
1234      CCC     Oct.06/01   Dec.21/01 Clerk-L2    *
1234              Oct.20/04             Manager-L1
1234      BBB     Oct.31/04   Nov.20/04 Manager-L1  *
1234      CCC     Nov.20/04             Manager-L1  *

The extra lines I have seem to be one possible result of the missing data hence my earlier questions. According to one interpretation of the data, The period starting Oct.20/04 saw a change in position and no current assignment (so if this were live data - I would say the person re-joined the company as a Manager after gaining qualifications).

I hope this helps - was fun to ponder and work out a query

 

[Hinfer]

Thanks for PCLewis. Your guesses are correct. The big gap in Position table between Jun.06/03 and Oct.20/04 was intentional. He was temporarily terminated (payroll stopped - due to schooling). Came back as manager.

Regarding your second inquiry, YES, the big gap from Dec.21/01 to Oct.31/04 IS INTENTIONAL. This person was not assigned for work in this time period.

An the one's marked with "*" are exactly the one's I would want to see. The extra lines is a nice to have but for the time being, could you remove the extra line (ie. one's not marked with "*")? I don't want to include extra lines.

A super star for you. You have saved me alot of time...I've been up late at night trying to figure this one out. This would seem like a common query. Thanks again. I can finally start to code this out.

 

[Hinfer]

ANOTHER FOLLOW-UP

I have been thinking about it this solution and I could really use it in another database (Oracle). I think I may try and update some blank fields using this method.

Thanks again PCLewis. I wish I could give you another star!

 

[Hinfer]

I am getting an error when I try this query. I have formatted the query so that I can understand it better. It appears correct but Access is giving me an error "syntax error in union query". Access highlights the left bracket in ") AS Q1" in the union portion of the query. Would you know what is wrong??? So close yet so far...

SELECT 
AssignmentPositions.Employee, 
DMax(
"Store",
"Assignment",
"[StartDate]<=#" & 
Format([StartDate],"d-mmm-yyyy") & 
"# and ([EndDate]>=#" & 
Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") &
"# or[EndDate] IS NULL)"
) AS Store, 
AssignmentPositions.StartDate, 
AssignmentPositions.EndDate, 
DMax
(
"Position",
"Position",
"[StartDate]<=#" & 
Format([StartDate],"d-mmm-yyyy") & 
"# and ([EndDate]>=#" & 
Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & 
"# or[EndDate] IS NULL)"
) AS [Position]

FROM 
	 [
 	 SELECT Q1.Employee, Q1.StartDate, Min(Q1.EndDate) AS EndDate
	 FROM (
	 	  SELECT Position.Employee as Employee,  Position.StartDate as StartDate, Position.EndDate as EndDate
		  FROM [Position] 
	 	  UNION ALL
	 	  SELECT Assignment.Employee as Employee, Assignment.StartDate as StartDate, Assignment.EndDate as EndDate
	 	  FROM Assignment
		  ) AS Q1
	 GROUP BY Q1.Employee, Q1.StartDate
	 ORDER BY Q1.StartDate
	 ]. AS AssignmentPositions

 

[PHV]

Replace this:
FROM
[
By this:
FROM
(
And this:
]. AS
By this:
) AS

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hinfer]

Thanks PHV. This worked.

I have another problem. If I have another employee in the employee and position table, the resulting new tables are:

Position Table
Employee Position StartDate EndDate
1235 Manager Jan.01/00 Mar.01/01

Assignment Table
Employee Store StartDate EndDate
1235 AAA Jan.01/00 Feb.01/00

Employee Store StartDate EndDate Position
1234 AAA Jan.01/00 Feb.01/00 Manager-L3
1235 AAA Jan.01/00 Feb.01/00 Manager-L3 *new
1234 BBB Feb.14/00 Jun.01/00 Manager-L3
1234 Mar.02/01 Jun.06/03 Manager-L3
1234 CCC Oct.06/01 Dec.21/01 Manager-L3
1234 Oct.20/04 Manager-L3
1234 BBB Oct.31/04 Nov.20/04 Manager-L3
1234 CCC Nov.20/04 Manager-L3


I believe since there is something wrong with grouping in PCLewis's code. How can I fix this? Please help ( I will try to troubleshoot this myself but any pointers would help).

 

[Hinfer]

sorry but my assigment table should look like this:

Employee Position StartDate EndDate
1235 Manager-L3 Jan.01/00


No enddate for this person's position. He has been a Manager-L3 ever since he started.

 

[PCLewis]

Well lucky for you I still had the query saved on my sratch file. I ran it with the updated data and it comes out just like your expected results.

You don't help yourself by being unclear as well. YOU posted the table data, then corrected yourself but in doing so, said that the "assignment table should look like this:" when you actually posted the Position table data.

Whatever is wrong with your query, it does not seem to be a result of what I originally posted for you to use since the output form my original query matches yours - well almost. Are you sure you have not modified it ?

If you need the query to identify the fact that an end-date of a position is also significant as the start-date of "no position" then that's a bigger issue. I would start probably have preferred to write code to do this if it were my task because in this case, code would be far more readable and maintainable.

(Please note the output format for dates is my default which is d/m/y)
Employee Store  StartDate    EndDate  Position
1234     AAA    1/01/2000  1/02/2000  Manager-L3
1235     AAA    1/01/2000  1/02/2000  Manager-L3
1234     BBB   14/02/2000  1/06/2000  Manager-L3
1234            2/03/2001  6/06/2003  Manager-L3
1234     CCC    6/10/2001 21/12/2001  Manager-L3
1234           20/10/2004             Manager-L3
1234     BBB   31/10/2004 20/11/2004  Manager-L3
1234     CCC   20/11/2004             Manager-L3

from query:
SELECT AssignmentPositions.Employee, DMax("Store","Assignment","[StartDate]<=#" & Format([StartDate],"d-mmm-yyyy") & "# and ([EndDate]>=#" & Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & "# or[EndDate] IS NULL)") AS Store, AssignmentPositions.StartDate, AssignmentPositions.EndDate, DMax("Position","Position","[StartDate]<=#" & Format([StartDate],"d-mmm-yyyy") & "# and ([EndDate]>=#" & Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & "# or[EndDate] IS NULL)") AS [Position]
FROM (SELECT Q1.Employee, Q1.StartDate, Min(Q1.EndDate) AS EndDate
FROM (SELECT Position.Employee as Employee,  Position.StartDate as StartDate, Position.EndDate as EndDate
FROM [Position] 
UNION ALL
SELECT Assignment.Employee as Employee, Assignment.StartDate as StartDate, Assignment.EndDate as EndDate
FROM Assignment) AS Q1
GROUP BY Q1.Employee, Q1.StartDate
ORDER BY Q1.StartDate) AS AssignmentPositions;

and
Position Table:
Employee Position    StartDate    EndDate	
1234     Clerk-L1    1/01/2000  1/03/2001	
1234     Clerk-L2    2/03/2001  6/06/2003	
1234     Manager-L1 20/10/2004		
1235     Manager-L3  1/01/2000		

Assignment Table:
Employee Store  StartDate    EndDate
1234     AAA    1/01/2000  1/02/2000
1234     BBB   14/02/2000  1/06/2000
1234     CCC    6/10/2001 21/12/2001
1234     BBB   31/10/2004 20/11/2004
1234     CCC   20/11/2004
1235     AAA    1/01/2000  1/02/2000

 

[Hinfer]

Sorry I have been tired from working on this query. Its my last query out of many that I am working. I am actually doing a data migration from an old system to a new system. There are 100,000 records for this one table and I have simplified the problem just to get the logic and learn from the example coding.

So back to the my issue... PCLewis, you are stating that your code is correct? You just posted the above with the resulting query (AssignmentPosition) that gives a position of Manager-L3 for all employees (1234 and 1235). IS THIS CORRECT? THAT IS, YOU THINK ALL EMPLOYEES IN ALL THEIR ASSIGMENTS SHOULD BE MANAGER-L3???

Only employee 1235 (just to clarify, this number is a employee id #) has a position of Manager-L3 from 1/01/2000 and onwards. This person should be the only person that has a Manager-L3 in the resulting assignmentposition table since no other employees (ie. 1234) even have this position in the Position table. Therefore,

Expected AssignmentPosition table:

Employee Store  StartDate    EndDate  Position
1234     AAA    1/01/2000  1/02/2000  Clerk-L1
1235     AAA    1/01/2000  1/02/2000  Manager-L3    *
1234     BBB   14/02/2000  1/06/2000  Clerk-L1
1234            2/03/2001  6/06/2003  Clerk-L2
1234     CCC    6/10/2001 21/12/2001  Clerk-L2
1234           20/10/2004             Manager-L1
1234     BBB   31/10/2004 20/11/2004  Manager-L1
1234     CCC   20/11/2004             Manager-L1

Just to test things further, I will now add one more employee:

Position Table:
Employee Position    StartDate    EndDate    
1234     Clerk-L1    1/01/2000  1/03/2001    
1234     Clerk-L2    2/03/2001  6/06/2003    
1234     Manager-L1 20/10/2004        
1235     Manager-L3  1/01/2000  
2222     Account-L1  1/01/2005  1/06/2005
2222     Account-L2  2/06/2005  

Assignment Table:
Employee Store  StartDate    EndDate
1234     AAA    1/01/2000  1/02/2000
1234     BBB   14/02/2000  1/06/2000
1234     CCC    6/10/2001 21/12/2001
1234     BBB   31/10/2004 20/11/2004
1234     CCC   20/11/2004
1235     AAA    1/01/2000  1/02/2000
2222     DDD    1/01/2005  1/06/2005
2222     FFF    2/06/2004  1/07/2005

Expected AssignmentPosition
Employee Store  StartDate    EndDate  Position
1234     AAA    1/01/2000  1/02/2000  Clerk-L1
1235     AAA    1/01/2000  1/02/2000  Manager-L3   *
1234     BBB   14/02/2000  1/06/2000  Clerk-L1
1234            2/03/2001  6/06/2003  Clerk-L2
1234     CCC    6/10/2001 21/12/2001  Clerk-L2
1234           20/10/2004             Manager-L1
1234     BBB   31/10/2004 20/11/2004  Manager-L1
1234     CCC   20/11/2004             Manager-L1
2222     DDD    1/01/2005  1/06/2005  Account-L1   *
2222     FFF    2/06/2005  1/07/2005  Account-L2   *

Instead the actual result of AssignmentPosition is:

Actual AssignmentPosition table:
Employee Store  StartDate    EndDate  Position
1234     AAA    1/01/2000  1/02/2000  Manager-L3
1235     AAA    1/01/2000  1/02/2000  Manager-L3   *
1234     BBB   14/02/2000  1/06/2000  Manager-L3
1234            2/03/2001  6/06/2003  Manager-L3
1234     CCC    6/10/2001 21/12/2001  Manager-L3
1234           20/10/2004             Manager-L3
1234     BBB   31/10/2004 20/11/2004  Manager-L3
1234     CCC   20/11/2004             Manager-L3
2222     DDD   01/01/2005  1/06/2005  Manager-L3
2222     FFF   02/06/2005  1/07/2005  Manager-L3
2222     DDD    1/01/2005  1/06/2005  Manager-L3   *
2222     FFF    2/06/2005  1/07/2005  Manager-L3   *

I believe that one record in the position table for ONE EMPLOYEE (1235) is negatively affecting the other records. Specifically, the query is not taking into consideration the id. Since this is the earliest date for the positions it is automatically applying this position to all, REGARDLESS OF WHICH EMPLOYEE.

There are 3 fields that distinguish the position in the assigmnetpositions table. It is the id, startdate and enddate. Could the problem be related to grouping?

I hope I am clearer this time around. My apologies.

 

[Hinfer]

Alright! I think I got it. I think I should deserve a star for this one (well, most goes to PCLewis) Need to apply another criteria on the DMAX function to equate the employee ids. Need to add addition to DMAX criteria portion of the function:

... DMax("Store","Assignment","[Employee]="&[Employee]& ...

therefore, new code is as follows

SELECT AssignmentPositions.Employee, DMax("Store","Assignment","[Employee]="&[Employee]&"[StartDate]<=#" & Format([StartDate],"d-mmm-yyyy") & "# and ([EndDate]>=#" & Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & "# or[EndDate] IS NULL)") AS Store, AssignmentPositions.StartDate, AssignmentPositions.EndDate, DMax("Position","Position",","[Employee]="&[Employee]&"[StartDate]<=#" & Format([StartDate],"d-mmm-yyyy") & "# and ([EndDate]>=#" & Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & "# or[EndDate] IS NULL)") AS [Position]
FROM (SELECT Q1.Employee, Q1.StartDate, Min(Q1.EndDate) AS EndDate
FROM (SELECT Position.Employee as Employee,  Position.StartDate as StartDate, Position.EndDate as EndDate
FROM [Position] 
UNION ALL
SELECT Assignment.Employee as Employee, Assignment.StartDate as StartDate, Assignment.EndDate as EndDate
FROM Assignment) AS Q1
GROUP BY Q1.Employee, Q1.StartDate
ORDER BY Q1.StartDate) AS AssignmentPositions;

 

[Hinfer]

oops forgot the "and" afterward...don't worry I have tested it and it works

SELECT AssignmentPositions.Employee, DMax("Store","Assignment","[Employee]="&[Employee]&" and[StartDate]<=#" & Format([StartDate],"d-mmm-yyyy") & "# and ([EndDate]>=#" & Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & "# or[EndDate] IS NULL)") AS Store, AssignmentPositions.StartDate, AssignmentPositions.EndDate, DMax("Position","Position",","[Employee]="&[Employee]&" and[StartDate]<=#" & Format([StartDate],"d-mmm-yyyy") & "# and ([EndDate]>=#" & Format(NZ([EndDate],[StartDate]),"dd-mmm-yyyy") & "# or[EndDate] IS NULL)") AS [Position]
FROM (SELECT Q1.Employee, Q1.StartDate, Min(Q1.EndDate) AS EndDate
FROM (SELECT Position.Employee as Employee,  Position.StartDate as StartDate, Position.EndDate as EndDate
FROM [Position] 
UNION ALL
SELECT Assignment.Employee as Employee, Assignment.StartDate as StartDate, Assignment.EndDate as EndDate
FROM Assignment) AS Q1
GROUP BY Q1.Employee, Q1.StartDate
ORDER BY Q1.StartDate) AS AssignmentPositions;

 

[Hinfer]

OK I have had misinterpreted the end date for the position table. It turns out their is no end date for each position!! In fact, the date I used is a union exit date (which I don't care about).

The end date of the position is defined as one day before the start date of the new position. Therefore table is now this:

Position Table:
Employee Position    StartDate    CalculatedEndDate    
1234     Clerk-L1    1/01/2000    1/03/2001    
1234     Clerk-L2    2/03/2001   19/10/2003    
1234     Manager-L1 20/10/2004        
1235     Manager-L3  1/01/2000  
2222     Account-L1  1/01/2005    1/06/2005
2222     Account-L2  2/06/2005

I was planning to create a make table query so that I can calculate an end date. That way, I can use the query above. Is this the best option or is there a way to modify the exisitng query??

If I should just create a make table query, how do I calculate the end dates?