Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing Values in Crosstab (A2K)

    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:
    <pre>
    [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
    </pre>


    The crosstab should look like this:
    <pre>
    [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
    </pre>


    The crosstab actually looks like this:
    <pre>
    [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
    </pre>


    If I take out Certification, the crosstab looks like this:
    <pre>
    [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
    </pre>


    If I take out Education Requirements also, the crosstab looks like this:
    <pre>
    [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
    </pre>


    If I take out Job Summary, Education Requirements and Certification, the crosstab looks like this (i.e. it has all the crosstab data) :
    <pre>
    [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
    </pre>



    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing Values in Crosstab (A2K)

    Your post is extremely difficult to read - even on a 1280 by 1024 resolution I had to keep scrolling.

    There must be something else you haven't told us. I created small demo tables with the structure you describe, and entered a few records. Then I copied the SQL from your post into a query, without any modification. It displays the values the way you want - see attached picture. I didn't have to take out any field to get this. Do you have links to other tables in your database?
    Attached Images Attached Images
    • File Type: png x.png (1.4 KB, 1 views)

  3. #3
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Values in Crosstab (A2K)

    Hi Hans,

    I really apologize about the length and format of the post, but I wasn't sure how else to get across what the problem is.

    I wish there was something I hadn't told you about this. The problem appears to be related to specific data in the Job Summary, Education Requirements and Certification fields -- depending on what's in them, certain random (as far as I can tell) values don't show up in the crosstab grid.

    I've attached a database that shows the problem. The problem query is qryJobProfilesX. The other two queries show my workaround.

    This seems to be an entirely illogical problem...

    Brent
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing Values in Crosstab (A2K)

    The problem is the memo field Job Summary. Your query does a Group By on it. A Group By sorts the field, but Access truncates memo fields to 255 characters when sorting. How this influences the query exactly, I don't know, but as you have found out, it messes up the data.
    In fact, you don't need to Group By on Job Summary (and most other fields, except Job ID and Factor Number). If you set the Totals option for Job Summary to First, the results seem to be OK. To avoid "FirstOfJob Summary" as column header, you can set an alias or a caption for this field.

  5. #5
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Values in Crosstab (A2K)

    Thanks Hans. Changing the row heading fields from Group By to First does resolve the problem, although it isn't just a problem with memo fields. To completely make it go away, I have to do it to the Education Specialization field as well (look at 54 and 97 if you just change Job Summary).

    What I'm concerned about is that the problem really shouldn't happen in the first place. This seems to be a bug in Access. I converted the database to Access 97 to test it out and found that Access 97 doesn't allow grouping on a Memo field, but if I change just the memo fields to First, the problem still occurs (with different missing values than the identical query in Access 2000). I haven't tested this in Access 2002 or Access 11/2003 because I don't have either of them installed, or with non-Jet databases, but it's seems likely that it's just a Jet problem. My MSJET40.DLL is version 4.00.4431.3 and my MSJET35.DLL (on the machine where I'm running A97) is version 3.51.2723.0.

    I'd appreciate it if other people would test this out on other setups and versions. Does anyone know how to go about reporting a bug to Microsoft (without having to call their tech support and pay for the privilege)?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing Values in Crosstab (A2K)

    I'm afraid I have no further ideas.

    Just for the record, I tested using Access 2002 SP2 (Dutch) under Windows XP Home SP1 (Dutch). The version of MSJet is 4.00.6218.0.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Missing Values in Crosstab (A2K)

    I ran tests on my system which is running Access 2002 SP1, and got the same results. But I think the real problem with this is a not well documented limit on the number of Group By fields you can have in an aggregate query, which crosstabs really are. Regular GroupBy queries are limited to having 10 fields with GroupBy or other Aggregate functions, while yours has 13 if I counted correctly. That may well explain why you have to delete 3 fields to get it to correctly calculate - note that the Total value does seem to be complete. Bottom line - don't report this to Microsoft as a bug. They will tell you it's by design. So use the approach that Hans suggested that works - it's much more efficient anyhow.
    Wendell

  8. #8
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Values in Crosstab (A2K)

    Hi Wendell, that's definitely a reasonable explanation about why it's happening. Although, I don't have to delete fields to get rid of the problem, just change them to something like First as opposed to Group By. So it may be an issue specifically with the number of Group By's, and 8 seems to be the magic number. Thanks for the insight.

    I still consider this a bug, even if MS considers it to be by design. If there actually is a limit, then the query design grid should prevent you from going over that limit (at least when you try to run it), kind of like Access 97 (but not A2K+) prevents you from using Group By on a memo field. Second, the limit should be documented -- if it is, I can't find it in the help file, or in the MS Knowledge Base. Third, if it's by design, it shouldn't cause random and unexpected results with data -- that's definitely NOT the proper design for a database engine.

    There are certainly good workarounds for this problem, like Hans', which should be a more efficient way to design the query, but I have a problem with the fact that the default behavior of Access (Group By for row headings in a crosstab query) can lead someone to an unexpected and undocumented problem.

    Brent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •