I've got a mess of a database containing roughly 30 queries based on around 10 different tables. It's a complex routine for a manufacturing database based on an extraction from their MRP system.
The issue I'm running into is that nearly every query refers to a specific field for a certain value, yet the user of this database needs to be able to have every query refer to one of 4 actual fields for the calculations.
So, they might use it the first time needing all calculations based on Field1. The next time they use it they may need the entire thing based on Field2, Field3, or Field4.
My thought was to have a table for input (with one record) where they specify the name of the field they want the queries to reference. I just don't know how to change calculated field values within queries to use the value of a field/variable as the name of the field the calculation should be based on.
I'm hoping for a function to work something like:
ValueOfField("DataTableName", [TableName].[InputValue])
Where DataTableName is the name of the table I need a value from, TableName is the name of my user editable table, and InputValue is the name of the field that would contain the name of the field needed to be referenced from DataTableName.
That make any kind of sense? I guess I could write my own function, but it'd be slow as dirt considering the number of records in every single one of these tables/queries.
The issue I'm running into is that nearly every query refers to a specific field for a certain value, yet the user of this database needs to be able to have every query refer to one of 4 actual fields for the calculations.
So, they might use it the first time needing all calculations based on Field1. The next time they use it they may need the entire thing based on Field2, Field3, or Field4.
My thought was to have a table for input (with one record) where they specify the name of the field they want the queries to reference. I just don't know how to change calculated field values within queries to use the value of a field/variable as the name of the field the calculation should be based on.
I'm hoping for a function to work something like:
ValueOfField("DataTableName", [TableName].[InputValue])
Where DataTableName is the name of the table I need a value from, TableName is the name of my user editable table, and InputValue is the name of the field that would contain the name of the field needed to be referenced from DataTableName.
That make any kind of sense? I guess I could write my own function, but it'd be slow as dirt considering the number of records in every single one of these tables/queries.