Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Question (2000 (9.0.4402 SR-1))

    I've created a crosstab query to total production numbers by week (columns) for a series of work areas (rows). Each record in the source table specifies a work area, work units completed, and date performed. There are some weeks when no work was done in any of the work areas but I'd still like this week to show up in the crosstab query results (a column with all zeros). I understand why the column is missing (no records in the source table for that date), but is there a way to "fool" Access into including the zero-production week columns? I've thought of adding some "dummy" zero production records to the source table (one for each week), but does anyone know of a "slicker" way to do this without monkeying with the source table?

    Thanks.

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

    Re: Crosstab Question (2000 (9.0.4402 SR-1))

    You can specify the Column Headings property to force Access to display a fixed set of columns, regardless of whether there are data for that column.
    In SQL, this is expressed as PIVOT fieldname IN (value1, value2, ...) - you could use this to set the column headings in code, if necessary.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Question (2000 (9.0.4402 SR-1))

    Good idea, Hans!

    Prior to seeing your response, I wrote a little code to create a dummy table with some zero production numbers for every week in the range of the dates for the 'real' data, for a work area that has some production (e.g., the work area specified in the first record of the 'real' data table). I then created a UNION query to combine these two tables. I now use the UNION query as the source data for the crosstab query.

    This works fine, but I like your idea better. I was hoping to find out a way to do it through the query design grid, but I can certainly create the SQL statement in code as easily as creating the dummy table (still using the range of dates from the real data to figure out which dates to include in the PIVOT clause), and with your way I don't have that otherwise useless table hanging around.

    Thanks!

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Crosstab Question (2000 (9.0.4402 SR-1))

    The only problem I see with the PIVOT fields is that they are fixed, you would have to change them to what you wanted depending on the week range.
    This is no biggy as you can setup queries "on the fly" anyway.

    I guess either way requires code to set them up.

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Question (2000 (9.0.4402 SR-1))

    "I was hoping to find out a way to do it through the query design grid, "....

    In the query design grid, right click and the second column is "Column Headings". Set your static column values here.

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Question (2000 (9.0.4402 SR-1))

    Sorry ! I meant to say "Right click, SELECT PROPERTIES, and the second line is for column headings"

Posting Permissions

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