Using parameters to select fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table which has twelve fields -- one for each month. The field
names are the same with the exception of the last two characters: SAL01,
SAL02, SAL03 ... SAL12. I want to set up a parameter where the user enters
the month and the respective field is queried -- something like "SAL"&[Enter
Month] for the field name. Is this possible?

Thanks,
Patti
 
Patti said:
I have a table which has twelve fields -- one for each month. The field
names are the same with the exception of the last two characters: SAL01,
SAL02, SAL03 ... SAL12. I want to set up a parameter where the user enters
the month and the respective field is queried -- something like "SAL"&[Enter
Month] for the field name. Is this possible?
Click to expand...


Not without using a VBA procedure to construct the query's
SQL statement.

The reason is that a query parameter can only represent a
value in an expression. It can not be used to provide
syntactic elements such as names or operators.
 
Dear Patti:

As I can see it, there is a normalization problem. If you had only 1 column
instead of 12, with a separate column for the values 1-12, then you'd be
well fixed. This can be done with a Normalizing UNION Query. From that,
you can then filter to any month, since the month number is a column of your
data.

If this sounds interesting, please let me know. I'll be back in the morning
and see about going into that further. I would need a layout of all the
other columns in this table to do that well.

Tom Ellison
 
Possible? Yes. Advisable, No.

If you are absolutely stuck you can use something like:

Field: SomeMonth: Choose([Enter month
number],Sal01,Sal02,Sal03,Sal04,...,Sal12)
Criteria: ?????

Or you can use a normalizing UNION query- which I see others have suggested.

You would be much better off with a redesign of your table structure so that
you had no repeating fields as above. You should move Sal to another table
along with some other identifying (relationship) information.

EmpSal (TableName)
EmpID (Relating field)
MonthNum (1 to 12)
Sal (Whatever value you are now storing in the corresponding Sal fields)
 
Thanks guys. The table isn't mine to mess with so I'll just have the users
choose the field for each month.

John Spencer said:
Possible? Yes. Advisable, No.

If you are absolutely stuck you can use something like:

Field: SomeMonth: Choose([Enter month
number],Sal01,Sal02,Sal03,Sal04,...,Sal12)
Criteria: ?????

Or you can use a normalizing UNION query- which I see others have suggested.

You would be much better off with a redesign of your table structure so that
you had no repeating fields as above. You should move Sal to another table
along with some other identifying (relationship) information.

EmpSal (TableName)
EmpID (Relating field)
MonthNum (1 to 12)
Sal (Whatever value you are now storing in the corresponding Sal fields)



Patti said:
I have a table which has twelve fields -- one for each month. The field
names are the same with the exception of the last two characters: SAL01,
SAL02, SAL03 ... SAL12. I want to set up a parameter where the user
enters
the month and the respective field is queried -- something like
"SAL"&[Enter
Month] for the field name. Is this possible?

Thanks,
Patti
Click to expand...
Click to expand...
 
Back
Top