Creating a query with dynamic fields

[GrantM]

Hypothetically, let's say I have a table with 26 fields, A-Z. I want to
create a query that will select fields "A", "B", and "C", and filter records
based on a fourth field. However, I want this fourth field to be dynamic,
such that it can be any one of the fields D-Z, to be chosen by a control on a
form (i.e. a combo box). Once the field is selected, I want to be able to
filter records such that only those records with a value greater than a
certain number (specified on the same form as the field) are returned.

Is it possible to design a query such that one of the fields chosen for that
query can vary depending on the value of a control on a form? I have
another, brute-force idea how to do this, but that would require creating a
different query for all the fields D-Z, and I have to think that there is a
better way of doing this.

Thanks for any help!

-G

 

[Ken Sheridan]

You can change the SQL property of the query in code in the form's module,
e.g. in the Click event procedure of a button on the for:

Dim dbs as DAO.Database, qdf As DAO.QueryDef
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("YourQuery")

strSQL = "SELECT A, B, C " & _
"FROM YourTable " & _
"WHERE " & Me.cboField & " > " & _
Me.txtNumber

qdf.SQL = strSQL

DoCmd.OpenQuery "YourQuery"

However, the fact that you have to resort to this does make me wonder
whether your table should be decomposed into two related tables, so for each
row in the current table you'd have multiple rows in the new referencing
table. I suspect you might currently be doing what's known as 'encoding data
as column headings', whereas in a relational database data should only be
stored as values at column positions in rows in tables.

Ken Sheridan
Stafford, England

 

[GrantM]

Thanks for the reply, Ken. I'll give it a shot.

As for the table design, you're probably right, but I'm working on this
database for a colleague, and I believe that the table needs to be in this
format as that is how the data is received from an external source.

Thanks again!