Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Keeping a column if Missing value (Access2003)

    I have that has Regions, Cities and Descriptions.
    1 Region might have 4 Descriptions - 1,2,3,4

    Regions Cities Descriptions
    East NY 1
    East NY 2
    East NY 3
    East NY 4

    I made Crosstab that showing how many cities in each Region by description

    East 1 2 3 4

    1 1 1 1 Total 4

    What happens sometimes I do not have let say Description 4, only have 1,2,3.
    So my Crosstab comes up 1 field less. It is always the same field - 3.
    I am creating a table with all 4 Descriptions so I can Append Crosstab result inot the table and where is Description 3 - I will have empty column.
    However I can not do this.
    When I go Append

    Regions 1 2 3 4
    to
    Regions 1 2 3 4

    I need to keep 3 IF absent from the table - as a null value column but I can't lose it.

    I hope it is not too confusing.
    Thanks

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

    Re: Keeping a column if Missing value (Access2003)

    If you know the possible descriptions in advance, you can specify them in the Column Headings property of the crosstab query:
    - Open the crosstab query in design view.
    - Click in an empty part of the upper half of the query window.
    - Select View | Properties to activate the Properties window.
    - Enter the list of column headers in the Column Headings property, in the order that you want them, surrounded by quotes and separated by commas.
    - In your example, it would be:

    "1", "2", "3", "4"

    - Save the crosstab query.
    - It will now display the specified column headers whether there are data for them or not.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping a column if Missing value (Access2003)

    WOW! Cool solution! Thanks!

Posting Permissions

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