Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    534
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Empty field in query

    I have a crosstab query (Query 1) for a monthly report in which I use the Column Heading to list numbers of occurrences of values in a field that can contain the values A, B or C. This normally creates three columns with headings "A", "B" and "C"; e.g., as follows.

    Name, A, B, C
    Tom, 2, 6, 4
    Dick, 4, 0, 2
    Harry, 0, 2, 0

    I then use a second query (Query 2) to convert the numbers in the crosstab query to percentages of the total number of records (which is counted in another field named Total); e.g., as follows.

    Name, %A, %B, %C
    Tom, 10, 30, 20
    Dick, 20, 0, 10
    Harry, 0, 10, 0

    The fields in the second query are [A]/Total, [B]/Total, and [C]/Total. However, if there happen to be no occurrences of a particular value in a particular month, then when I run Query 2, Access says: 'The MS database engine does not recognize " as a valid field name or expression'. I can see that this is because Query 1 does not generate a column for a value that has no occurrences, and so Query 2 is looking for a field in Query 1 that does not exist for that particular month.

    Is there a way to handle this with something like Iif/Isnull or Nz in Query 2 for cases where a field does not exist in Query 1?

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,497
    Thanks
    3
    Thanked 42 Times in 42 Posts
    There is, and it involves setting fixed columns headings for the crosstab. See Specify Column Headings by Allen Browne. It also includes several other helpful tips in working with crosstab queries.
    Wendell

  4. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    534
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thanks for your reply. That works fine.

    In Query 2, in datasheet view, I also want to show the zero value for the empty field from Query 1 as "0.0" not just blank. (I know a report would be better for proper formatting, but I only need to use the query for now.) Is there a way to do this?

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,497
    Thanks
    3
    Thanked 42 Times in 42 Posts
    Then in Query 2 you will want to use the Nz() function to convert the empty crosstab values to zero before you format the data as a percentage. See http://office.microsoft.com/en-us/ac...001228890.aspx for the syntax on the Nz() function.
    Wendell

  6. The Following User Says Thank You to WendellB For This Useful Post:

    Murgatroyd (2012-11-05)

  7. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Assuming you know all the fields, you can set-up your crosstab to handle missing values.

    Right now, your query probably ends with something like:
    ...PIVOT [some field]

    You need to change it to:
    ...PIVOT [some field] IN ("A", "B", "C")

    This will then make sure you have columns for A, B, and C, and in that order.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    534
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thanks for your reply. I found that entering the column headings in the Column properties as suggested above has the result of changing the code for the PIVOT parameter as suggested here.

Posting Permissions

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