creating crosstab or table from list data 2

[ClifCamp]

My data looks like this: John Doe is currently a Mgr IV

Name Job Title Effective Date
------------------------------------------
John Doe Mgr III 2/15/2006
John Doe Mgr II 3/1/2005
John Doe Mgr I 2/28/2004

I want to have one row in a query or table for John Doe that looks like this:

Name Prior I Job Prior I Date Prior 2 Job Prior 2 Date, etc
J. D. Mgr III 2/15/2006 Mgr II 3/1/2005

Can this be done in a crosstab or some other way?
Thanks for any help!

 

[rac2]

Not really.

SQL doesnt work that way.

Use queries to retrieve data and reports to format it for display.

Only a procedure such as a VBA script can do that.

The fundamental problem is that the number of prior jobs is variable. You can write a query which provides a fixed number of prior jobs, but it will always show columns for that same number. For folks with fewer prior jobs some columns will be empty; for folks with more jobs some jobs will be missed.

The query that gives this result can be quite slow unless you need the result only for a specific employee and there is an index on the employee id column in the table with the history.

SELECT a.Name,
       a.JobTitle AS "PriorJob1",
       a.EffectiveDate AS "Date1",
       b.JobTitle AS "PriorJob2",
       b.EffectiveDate AS "Date2",
       c.JobTitle AS "PriorJob2",
       c.EffectiveDate AS "Date2"
FROM PositionHistory a
LEFT JOIN PositionHistory b ON b.Name = a.Name
     AND b.EffectiveDate = (
          SELECT MAX(EffectiveDate)
          FROM PositionHistory 
          WHERE  Name = "John Doe"
            AND EffectiveDate < a.EffectiveDate
         )
LEFT JOIN PositionHistory c ON c.Name = a.Name
     AND c.EffectiveDate = (
          SELECT MAX(EffectiveDate)
          FROM PositionHistory 
          WHERE  Name = "John Doe"
            AND EffectiveDate < b.EffectiveDate
         )
WHERE a.Name = "John Doe"
  AND a.EffectiveDate = (
       SELECT MAX(EffectiveDate) 
       FROM PositionHistory 
       WHERE Name = "John Doe"
         AND EffectiveDate < (
              SELECT MAX(EffectiveDate)
              FROM PositionHistory 
              WHERE Name = "John Doe"
             )
      )

What does that do?
This subquery obtains the effective data of John Doe current position.

              SELECT MAX(EffectiveDate)
              FROM PositionHistory 
              WHERE Name = "John Doe"

This subquery obtains the effective date of his previous position.

       SELECT MAX(EffectiveDate) 
       FROM PositionHistory 
       WHERE Name = "John Doe"
         AND EffectiveDate < (
              SELECT MAX(EffectiveDate)
              FROM PositionHistory 
              WHERE Name = "John Doe"
             )
      )

This one obtains the second previous job. It uses a LEFT JOIN to ensure that we obtain a result in the event that John only had one prior job. The columns PriorJob2 and Date2 will have the value NULL in that case. An IIf() function could be added to clean that up.

SELECT a.Name,
       a.JobTitle AS "PriorJob1",
       a.EffectiveDate AS "Date1",
       b.JobTitle AS "PriorJob2",
       b.EffectiveDate AS "Date2"

FROM PositionHistory a
LEFT JOIN PositionHistory b ON b.Name = a.Name
     AND b.EffectiveDate = (
          SELECT MAX(EffectiveDate)
          FROM PositionHistory 
          WHERE  Name = "John Doe"
            AND EffectiveDate < a.EffectiveDate
         )

WHERE a.Name = "John Doe"
  AND a.EffectiveDate = (
       SELECT MAX(EffectiveDate) 
       FROM PositionHistory 
       WHERE Name = "John Doe"
         AND EffectiveDate < (
              SELECT MAX(EffectiveDate)
              FROM PositionHistory 
              WHERE Name = "John Doe"
             )
      )

 

[dhookom]

Create a table [tblField] with a single text field [FldName]. Add the names of your fields "EffectiveDate" and "EmpName". If your field names are different, change the values and change the SQL below:
Then create a crosstab query with sql of: (change names to match yours)

TRANSFORM First(IIf([FldName]="JobTitle",[JobTitle],[EffectiveDate])) AS Expr2
SELECT EmpName
FROM tblNoNameTable, tblFieldNames
GROUP BY EmpName
ORDER BY EmpName
PIVOT "Job" & DCount("*","tblNoNameTable","EmpName='" & [EmpName] & "' AND EffectiveDate <=#" & [EffectiveDate] & "#") & " " & [FldName];

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]