Crosstab Question (2000 (9.0.4402 SR-1))

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

“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.

Reply | Quote

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

Reply | Quote