M
mike_hass
I have inherited a set of complex Access Databases, one for each state
in the US. They are all based on a similar principle and processes,
and have the same goal of calculating a price to charge each customer.
However, each state has slightly different logic to calculate the cost
per customer. I can boil that differences down to the SQL queries and
standardize almost everything else. There are 30 queries (one for each
step in the rating logic) that are different by state. I would like to
have one Setup Model, that can be used to create the state specific
models. Therefore, if I add functionality to 1 model, I don't have to
manually add it to all the other models.
To address the SQL differences, I thought of storing the actual SQL for
the query in a "Setup Table". In the "Setup Table", each state would
be a separate column and each row would represent one of the 30 queries
in the rating logic. Therefore, the intersection of the column (i.e.
State) and row (i.e. Step), would be the right SQL to run. I would
like to pick up the SQL string as text and automatically create the
actual query object in Access. I would like to create the query object
because the end user needs to modify the query at times via Access's
query design view. I am not worried about saving the modified query
back to the "Setup Table". I can do that manually after they have
finalized their model. The SQL queries range from updated and append
queries to deletes and basic selects.
To a lesser extend, I have the same issue with reports and with
enabling/ disabling buttons based on the applicability of the button to
the state.
Any help would be greatly appreciated.
Thanks,
Mike
in the US. They are all based on a similar principle and processes,
and have the same goal of calculating a price to charge each customer.
However, each state has slightly different logic to calculate the cost
per customer. I can boil that differences down to the SQL queries and
standardize almost everything else. There are 30 queries (one for each
step in the rating logic) that are different by state. I would like to
have one Setup Model, that can be used to create the state specific
models. Therefore, if I add functionality to 1 model, I don't have to
manually add it to all the other models.
To address the SQL differences, I thought of storing the actual SQL for
the query in a "Setup Table". In the "Setup Table", each state would
be a separate column and each row would represent one of the 30 queries
in the rating logic. Therefore, the intersection of the column (i.e.
State) and row (i.e. Step), would be the right SQL to run. I would
like to pick up the SQL string as text and automatically create the
actual query object in Access. I would like to create the query object
because the end user needs to modify the query at times via Access's
query design view. I am not worried about saving the modified query
back to the "Setup Table". I can do that manually after they have
finalized their model. The SQL queries range from updated and append
queries to deletes and basic selects.
To a lesser extend, I have the same issue with reports and with
enabling/ disabling buttons based on the applicability of the button to
the state.
Any help would be greatly appreciated.
Thanks,
Mike
