Take the 2-minute tour ×

I am constructing a pivot table with many columns (300) and I wonder if I this is a bad practice.

Here are some details why I need to contruct this table.

A (very) simplified presentation of my database :

I have a company with diverse glass products, windows for example. Each product is referenced by and ID and has (many) attributes.


ID | Attributes | Value
1  | width      | 10
1  | height     | 15
1  | refraction | 1
1  | thickness  | 0.5
1  | rating     | 10

Now, I would like to add some (computed) attibutes

For example a new attribute surface should be defined by surface=width*height. To do this, I create a pivot table from the Products, from which I create the computed attributes such as width*height AS surface.

With this method, I can make a query depending on the surface.

Is this a bad practice to do so, since the number of columns can be very high (300) in my case ? If there is another way to do this kind of operation, you can let me know.

By the way, this exceeds the maximal number of columns of Access...

share|improve this question
In my experience MS Access can have performance issues and if you are trying to pivot 300 columns you might run into issues. You might want to look at doing this type of conversion in the presentation layer instead of in MS Access. –  bluefeet Dec 17 '13 at 17:12
The computed attibutes, such as the surface will be used in some SQL Query, for example, by selecting all products with a given surface. This is my limitation in the present case. –  motsdujour Dec 17 '13 at 17:17
I would break your query into multiple queries so that you have far less than 300 columns in the output of any given query... as @bluefeet said, Access will have performance issues with a query this complex. –  Max Vernon Dec 18 '13 at 15:25
add comment

Your Answer



Browse other questions tagged or ask your own question.