Building a Custom Calc in Access

[Guest]

Team,

I am trying to do the following. I want a user to be able to set up a
calculation template and then reuse this template with live data. Note that I
am not talking about a programmer designing the database. I want to design a
database which would be able to perform the custom calcs based on a certain
user-defined input. This will be something similar to users building their
own formulas in Excel.

For example - a user will provide the following calculation template in a
three column table in Access; the columns are separated by a comma for your
convenience:
Gross Receipts, (INPUT), No Formula
Exempt Sales, (INPUT), No Formula
Taxable Receipts, (FUNCTION), [Gross Receipts] - [Exempt Sales]
State Sales Tax, (FUNCTION), [Taxable Receipts] * 0.06.

After this template is set up, I want to pass the so-called "FUNCTIONs" from
above to a table which will allow the user to manually input the amounts for
Gross Receipts and for Exempt Sales, and then Access calculate the remaining
two fileds - Taxable Receipts and State Sales Tax - based on the formulas
provided by the user in the first step, and save the latter two values in the
same data table with Gross Receipts and Exempt Sales.

Another Example: I want a user to be ultimately able to type-in a formula in
a template of something like: (Federal Taxable Income + Additions -
Subtractions) * State Apportionment Factor * State Income Tax Rate - Tax
Credits = Total State Tax Due. And then have a user provide all necessary
values for the input fields and have Access calculate Total State Tax Due and
save input and calculated fileds in a table in Access.

Think of this as building a "Report Writer" such as Crystal Reports for
example in Access and then using the calculation templates written in the
report writer to calculate data fields and store them in Access.

Thanks for your help in advance.

 

[Jeff Boyce]

First, it is rarely necessary or desireable to store calculated values in a
relational database. If something changes (e.g., correcting a typo in the
underlying data), how do you keep the "stored calculated values" in sync?

It sounds like you are re-creating capabilities found in Access' queries and
reports, and in Excel.

Access has a fairly steep learning curve, and what you are proposing is
fairly sophisticated ... and it sounds like it is redundant.

Have you considered spending less time/energy and simply training the users
how to create queries and reports? ... or using Excel instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

This request is only a small part of a much bigger database. We have over
300 legal entities for which we prepare tax returns, make estimated payments
for, etc. We used to do all of the tracking in Excel, but that is no longer
an option due to the complexity of the company structure. We use Access to
track everything that is currently being filed, amended, etc in a multi-user
environment.

We have been handling everything quite fine so far in just one Access file
(the tables are stored on a SQL server). However, a number of our users have
asked if they can build some "custom" calcs without going back to the
database window. That's where the fun began.

I looked at some tools (such as an Access report writer) designed by
databasecreations.com and some others. Their tools do exactly what we need
but they offer everything including a kitchen sink. We just don't need all
the functionality they offer for the money. That's why I was wondering if
there is a simpler solution to this problem.

First, it is rarely necessary or desireable to store calculated values in a
relational database. If something changes (e.g., correcting a typo in the
underlying data), how do you keep the "stored calculated values" in sync?

It sounds like you are re-creating capabilities found in Access' queries and
reports, and in Excel.

Access has a fairly steep learning curve, and what you are proposing is
fairly sophisticated ... and it sounds like it is redundant.

Have you considered spending less time/energy and simply training the users
how to create queries and reports? ... or using Excel instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Team,

I am trying to do the following. I want a user to be able to set up a
calculation template and then reuse this template with live data. Note
that I
am not talking about a programmer designing the database. I want to design
a
database which would be able to perform the custom calcs based on a
certain
user-defined input. This will be something similar to users building their
own formulas in Excel.

For example - a user will provide the following calculation template in a
three column table in Access; the columns are separated by a comma for
your
convenience:
Gross Receipts, (INPUT), No Formula
Exempt Sales, (INPUT), No Formula
Taxable Receipts, (FUNCTION), [Gross Receipts] - [Exempt Sales]
State Sales Tax, (FUNCTION), [Taxable Receipts] * 0.06.

After this template is set up, I want to pass the so-called "FUNCTIONs"
from
above to a table which will allow the user to manually input the amounts
for
Gross Receipts and for Exempt Sales, and then Access calculate the
remaining
two fileds - Taxable Receipts and State Sales Tax - based on the formulas
provided by the user in the first step, and save the latter two values in
the
same data table with Gross Receipts and Exempt Sales.

Another Example: I want a user to be ultimately able to type-in a formula
in
a template of something like: (Federal Taxable Income + Additions -
Subtractions) * State Apportionment Factor * State Income Tax Rate - Tax
Credits = Total State Tax Due. And then have a user provide all necessary
values for the input fields and have Access calculate Total State Tax Due
and
save input and calculated fileds in a table in Access.

Think of this as building a "Report Writer" such as Crystal Reports for
example in Access and then using the calculation templates written in the
report writer to calculate data fields and store them in Access.

Thanks for your help in advance.

 

[Jeff Boyce]

Have you looked into having the users run queries/reports to get the raw
data, then export the data to Excel and doing their calculations there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

This request is only a small part of a much bigger database. We have over
300 legal entities for which we prepare tax returns, make estimated
payments
for, etc. We used to do all of the tracking in Excel, but that is no
longer
an option due to the complexity of the company structure. We use Access to
track everything that is currently being filed, amended, etc in a
multi-user
environment.

We have been handling everything quite fine so far in just one Access file
(the tables are stored on a SQL server). However, a number of our users
have
asked if they can build some "custom" calcs without going back to the
database window. That's where the fun began.

I looked at some tools (such as an Access report writer) designed by
databasecreations.com and some others. Their tools do exactly what we need
but they offer everything including a kitchen sink. We just don't need all
the functionality they offer for the money. That's why I was wondering if
there is a simpler solution to this problem.

First, it is rarely necessary or desireable to store calculated values in
a
relational database. If something changes (e.g., correcting a typo in
the
underlying data), how do you keep the "stored calculated values" in sync?

It sounds like you are re-creating capabilities found in Access' queries
and
reports, and in Excel.

Access has a fairly steep learning curve, and what you are proposing is
fairly sophisticated ... and it sounds like it is redundant.

Have you considered spending less time/energy and simply training the
users
how to create queries and reports? ... or using Excel instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Team,

I am trying to do the following. I want a user to be able to set up a
calculation template and then reuse this template with live data. Note
that I
am not talking about a programmer designing the database. I want to
design
a
database which would be able to perform the custom calcs based on a
certain
user-defined input. This will be something similar to users building
their
own formulas in Excel.

For example - a user will provide the following calculation template in
a
three column table in Access; the columns are separated by a comma for
your
convenience:
Gross Receipts, (INPUT), No Formula
Exempt Sales, (INPUT), No Formula
Taxable Receipts, (FUNCTION), [Gross Receipts] - [Exempt Sales]
State Sales Tax, (FUNCTION), [Taxable Receipts] * 0.06.

After this template is set up, I want to pass the so-called "FUNCTIONs"
from
above to a table which will allow the user to manually input the
amounts
for
Gross Receipts and for Exempt Sales, and then Access calculate the
remaining
two fileds - Taxable Receipts and State Sales Tax - based on the
formulas
provided by the user in the first step, and save the latter two values
in
the
same data table with Gross Receipts and Exempt Sales.

Another Example: I want a user to be ultimately able to type-in a
formula
in
a template of something like: (Federal Taxable Income + Additions -
Subtractions) * State Apportionment Factor * State Income Tax Rate -
Tax
Credits = Total State Tax Due. And then have a user provide all
necessary
values for the input fields and have Access calculate Total State Tax
Due
and
save input and calculated fileds in a table in Access.

Think of this as building a "Report Writer" such as Crystal Reports for
example in Access and then using the calculation templates written in
the
report writer to calculate data fields and store them in Access.

Thanks for your help in advance.

 

[Guest]

That's not a bad idea. I can certainly try that.

V

Have you looked into having the users run queries/reports to get the raw
data, then export the data to Excel and doing their calculations there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

This request is only a small part of a much bigger database. We have over
300 legal entities for which we prepare tax returns, make estimated
payments
for, etc. We used to do all of the tracking in Excel, but that is no
longer
an option due to the complexity of the company structure. We use Access to
track everything that is currently being filed, amended, etc in a
multi-user
environment.

We have been handling everything quite fine so far in just one Access file
(the tables are stored on a SQL server). However, a number of our users
have
asked if they can build some "custom" calcs without going back to the
database window. That's where the fun began.

I looked at some tools (such as an Access report writer) designed by
databasecreations.com and some others. Their tools do exactly what we need
but they offer everything including a kitchen sink. We just don't need all
the functionality they offer for the money. That's why I was wondering if
there is a simpler solution to this problem.

First, it is rarely necessary or desireable to store calculated values in
a
relational database. If something changes (e.g., correcting a typo in
the
underlying data), how do you keep the "stored calculated values" in sync?

It sounds like you are re-creating capabilities found in Access' queries
and
reports, and in Excel.

Access has a fairly steep learning curve, and what you are proposing is
fairly sophisticated ... and it sounds like it is redundant.

Have you considered spending less time/energy and simply training the
users
how to create queries and reports? ... or using Excel instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Team,

I am trying to do the following. I want a user to be able to set up a
calculation template and then reuse this template with live data. Note
that I
am not talking about a programmer designing the database. I want to
design
a
database which would be able to perform the custom calcs based on a
certain
user-defined input. This will be something similar to users building
their
own formulas in Excel.

For example - a user will provide the following calculation template in
a
three column table in Access; the columns are separated by a comma for
your
convenience:
Gross Receipts, (INPUT), No Formula
Exempt Sales, (INPUT), No Formula
Taxable Receipts, (FUNCTION), [Gross Receipts] - [Exempt Sales]
State Sales Tax, (FUNCTION), [Taxable Receipts] * 0.06.

After this template is set up, I want to pass the so-called "FUNCTIONs"
from
above to a table which will allow the user to manually input the
amounts
for
Gross Receipts and for Exempt Sales, and then Access calculate the
remaining
two fileds - Taxable Receipts and State Sales Tax - based on the
formulas
provided by the user in the first step, and save the latter two values
in
the
same data table with Gross Receipts and Exempt Sales.

Another Example: I want a user to be ultimately able to type-in a
formula
in
a template of something like: (Federal Taxable Income + Additions -
Subtractions) * State Apportionment Factor * State Income Tax Rate -
Tax
Credits = Total State Tax Due. And then have a user provide all
necessary
values for the input fields and have Access calculate Total State Tax
Due
and
save input and calculated fileds in a table in Access.

Think of this as building a "Report Writer" such as Crystal Reports for
example in Access and then using the calculation templates written in
the
report writer to calculate data fields and store them in Access.

Thanks for your help in advance.