Value of field where field name is a string

Mythics

New member
Local time
Yesterday, 17:34
Joined
Sep 16, 2009
Messages
2
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.
 
Your database structure is the real problem.

Where you had four fields and one record you should have two fields and four records. One field indicates the ValueTypeID (formerly the fieldname) and the other holds the actual value.

The table should be related to any other fields from the original records in another table as a one to many relationship. Use a Number (byte) format for the ValueTypeID. Index the key fields on both tables.

So instead of having to change the fieldname to suit the particular implementation of the query you use:
WHERE ValueTypeID = whatever

The value of Whatever can be read from a control on the form.

Despite the extra records it will be fast because it uses only sql on joined tables.
 

Users who are viewing this thread

Back
Top Bottom