Hi everyone, here’s a bizarre problem I’ve run into. Sorry in advance about the long post, but this is an interesting and unexpected problem and this was the easiest way to show you what was going on.
I have a simple crosstab query that “loses” values depending on the fields selected for the row heading. If I don’t have some of the fields in there, all of the values show up, but if I put them back in, they disappear. The other odd part is that the sum of the values (which is a row heading) is correct.
Some background on the data and the query: This is a to take a set of factors and other information about a job and show them in a grid. The database is normalized, and there’s three tables involved (by the way, I’m simplifying some of this to make it easier to explain, so trust me that the structure makes sense even if it doesn’t sound like it). One table, tblJobInfo, has information about the jobs, including the Job ID (Long), Job Code (text), Job Summary (memo), Education Requirements (text), Certification (text), Employee Name (text), Phone Number (text), and Org Unit Name (text), as well as other fields that aren’t included in the crosstab query. The second table, tblJobFactors, has the things I want to run the crosstab on. It’s a set of factor numbers (1 through 14, or whatever) and then the level the job is rated on that factor (levels can be 1 through 3 or 1 through 15 or whatever). The table has 3 fields: Job ID (long), Factor Number (double), and Level (long), with Job ID and Factor Number constituting the primary key. The third table (tblJobTitles) has the titles of the jobs, and the only two fields used are Job Code (text) and Title (text).
Here’s the SQL for the query:
TRANSFORM Sum(tblJobFactors.Level) AS SumOfLevel
SELECT tblJobInfo.[Job ID], tblJobInfo.[Job Code], tblJobTitles.Title, tblJobInfo.[Employee Name], tblJobInfo.[Phone Number], tblJobInfo.[Org Unit Name], tblJobInfo.[Job Summary], tblJobInfo.[Education Requirements], tblJobInfo.[Certification], Sum(tblJobFactors.Level) AS Total
FROM (tblJobInfo INNER JOIN tblJobFactors ON tblJobInfo.[Job ID] = tblJobFactors.[Job ID]) LEFT JOIN tblJobTitles ON tblJobInfo.[Job Code] = tblJobTitles.[Job Code]
GROUP BY tblJobInfo.[Job ID], tblJobInfo.[Job Code], tblJobTitles.Title, tblJobInfo.[Employee Name], tblJobInfo.[Phone Number], tblJobInfo.[Org Unit Name], tblJobInfo.[Job Summary], tblJobInfo.[Education Requirements], tblJobInfo.[Certification]
ORDER BY tblJobInfo.[Job ID], tblJobInfo.[Job Code]
PIVOT tblJobFactors.[Factor Number];
Note the left join between tblJobTitles and tblJobInfo (which translates to “Include all records from tblJobInfo and only those records from tblJobTitles where the joined fields are equal.”), and the inner join between tblJobInfo and tblJobFactors (where there’s a one-to-many relationship). Also, there’s only one value for Level for each [Job ID]/[Factor Number] combination, so the value should be identical regardless of which “total” calculation I use to get the Value field for the query (excluding, of course, Count, StDev and Var) — I tried it with Avg, Min, First and Sum, and none of them changed the results. Count had the same effect, except that it showed 1’s and blanks instead of the level numbers and blanks.
The tblJobFactors data looks like this:
[Job ID] [Factor Number] [Level] 1 1 3 1 2 1 1 3 5 2 1 6 2 2 4 2 3 4 3 1 2 3 2 5 3 3 1
The crosstab should look like this:
[Job ID] [Job Code] [Title] [Job Summary] [Education Requirements] [Certification] [Employee Name] [Phone Number] [Org Unit Name] [Total] [1] [2] [3]
1 Job Code1 Title1 Job Summary1 Education Requirement1 Certification1 Employee Name1 Phone Number1 Org Unit Name1 9 3 1 5
2 Job Code2 Title2 Job Summary2 Education Requirement2 Certification2 Employee Name2 Phone Number2 Org Unit Name2 14 6 4 4
3 Job Code3 Title3 Job Summary3 Education Requirement3 Certification3 Employee Name3 Phone Number3 Org Unit Name3 8 2 5 1
The crosstab actually looks like this:
[Job ID] [Job Code] [Title] [Job Summary] [Education Requirements] [Certification] [Employee Name] [Phone Number] [Org Unit Name] [Total] [1] [2] [3]
1 Job Code1 Title1 Job Summary1 Education Requirement1 Certification1 Employee Name1 Phone Number1 Org Unit Name1 9 3
2 Job Code2 Title2 Job Summary2 Education Requirement2 Certification2 Employee Name2 Phone Number2 Org Unit Name2 14 4
3 Job Code3 Title3 Job Summary3 Education Requirement3 Certification3 Employee Name3 Phone Number3 Org Unit Name3 8 2 5 1
If I take out Certification, the crosstab looks like this:
[Job ID] [Job Code] [Title] [Job Summary] [Education Requirements] [Employee Name] [Phone Number] [Org Unit Name] [Total] [1] [2] [3]
1 Job Code1 Title1 Job Summary1 Education Requirement1 Employee Name1 Phone Number1 Org Unit Name1 9 3 1
2 Job Code2 Title2 Job Summary2 Education Requirement2 Employee Name2 Phone Number2 Org Unit Name2 14 4
3 Job Code3 Title3 Job Summary3 Education Requirement3 Employee Name3 Phone Number3 Org Unit Name3 8 2 5 1
If I take out Education Requirements also, the crosstab looks like this:
[Job ID] [Job Code] [Title] [Job Summary] [Employee Name] [Phone Number] [Org Unit Name] [Total] [1] [2] [3]
1 Job Code1 Title1 Job Summary1 Employee Name1 Phone Number1 Org Unit Name1 9 3 1
2 Job Code2 Title2 Job Summary2 Employee Name2 Phone Number2 Org Unit Name2 14 6 4
3 Job Code3 Title3 Job Summary3 Employee Name3 Phone Number3 Org Unit Name3 8 2 5 1
If I take out Job Summary, Education Requirements and Certification, the crosstab looks like this (i.e. it has all the crosstab data) :
[Job ID] [Job Code] [Title] [Employee Name] [Phone Number] [Org Unit Name] [Total] [1] [2] [3]
1 Job Code1 Title1 Employee Name1 Phone Number1 Org Unit Name1 9 3 1 5
2 Job Code2 Title2 Employee Name2 Phone Number2 Org Unit Name2 14 6 4 4
3 Job Code3 Title3 Employee Name3 Phone Number3 Org Unit Name3 8 2 5 1
As you can see from above, the problem fields are Job Summary, Education Requirements and Certification, which are Memo, Text(255) and Text(255) respectively. Taking out any one of them doesn’t solve the problem — I have to take out all three.
I’ve found a workaround for this (create a query that just runs the crosstab on the tblJobFactors table, and then incorporate that query in a simple select query that adds the other fields, if that makes sense to you), but this is totally unexpected behavior, and I’d rather know why it’s happening that have to work around it. Does anyone have any clue what’s causing this?
Thanks,
Brent
