How to use variable as column selector in Access Query / SQL

Question

I have stumbled upon an annoying thing in access. I have a table with 20-30 columns and 20 rows. The columns are materials (types of metal), and the rows are plate thicknesses available in different countries.

We would like the user to select country followed by material in two combo boxes. Then use a query to show only the column with the thicknesses (rows) and the one column with the selected material.

I have tried this without luck:

SELECT [Forms]![Form1]![ComboBox] AS ThisCol
FROM Table1;

It just fills all cells with the value from the combo box (material selection). However, using the switch function works:

SELECT Switch([Forms]![Form1]![ComboBox]="Col1",[Col1]) AS ThisCol
FROM Table1;

This gives a table with the one column selected in the ComboBox. That is a solution, but i dont like it, since i have to add every 30 single switch cases, and sometimes it may not be me adding materials.

Can this be done in a smarter way?

I just copied the SQL generated by access. The Select statement is made as the first line in the access query. — Emil Olsen
– Emil Olsen, Commented Mar 30, 2015 at 11:57
I'm not understanding. After the user makes a selection on the 2nd combobox, why can't you just use that in your query? I don't even see a WHERE statement, and that's essentially what you're referring to.. — Mark C.
– Mark C., Commented Mar 30, 2015 at 12:43
@Invent-Animate There are no WHERE statement because there are no criteria. The "criteria" is that i only want to show 1 of 20 columns, with all rows in there. I actually have a WHERE, that says "Is Not Null" because i dont want empty lines. But it is not essential for the question. — Emil Olsen
– Emil Olsen, Commented Mar 30, 2015 at 12:45
I'm sorry. I'm finding your question difficult to follow. I hope you find a solution. — Mark C.
– Mark C., Commented Mar 30, 2015 at 12:49

Tarik · Accepted Answer · 2015-03-30 12:15:10Z

2

I do not believe you can dynamically specify a column name within a Access Query object. Now, you have several options:

1) Normalize your database so as to not have to add a column for each new material type. You would have the following table design:

Country Table
CountryID CountryName
1         France
2         Sweden
3         Russia

Material Table
MaterialID MaterialName
1          Steel
2          Aluminium
3          Coper

MaterialThickness Table
CountryID MaterialID Thickness
1         1          20
1         2          10
2         1          20
2         1          30
2         2          10
2         3          15
3         1          10

Use a crosstab table to show the materials as columns instead of rows.

2) If you want to stick to your current design, you have to ressort to VBA and create a recorset using a dynamic SQL statement that you build using the content of your combobox. Alternatively, you can create or modify a querydef using VBA.

answered Mar 30, 2015 at 12:15

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Emil Olsen Over a year ago

Hi Tarik. That is the exact approach i went for in the meanwhile. Anyway, i will keep this open as this did not solve the original question, although it is a good alternative, i will give it an up :) There might not be a solution. It is just confusing to me that you can use the switch function and get the desired result - That must mean that it can be done in another way as well?

Collectives™ on Stack Overflow

How to use variable as column selector in Access Query / SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related