Calculated fields with unrelated tables

G

Guest

In Access 2003, I'm trying to build a calculated field in a report based on
two unrelated tables. For example, in tblEmployees, the fields include
FirstName, LastName, Dept. and Salary. In tblAssumptions (which stores tax
rates that are modified periodically), I have fields for Name and Percentage.
Entries in this table are as follows:

NAME PERCENTAGE
Federal .08
State .05
Social Security .062
Medicare .0145

(The purpose of the Assumptions table is to allow our Accounting department
the ability to forecast budgets based on changing tax rate scenarios. They
want to be able to open an Assumptions form, change any tax rate as desired,
then re-run the report based on the updated assumptions.)

In the report, which will be grouped by Dept., I would like to list each
employee and their salary. Along side that, I will have columns for each of
the taxes, with each tax rate multiplied by the salary. I'm familiar with
creating calculated fields, but I'm struggling with the correct syntax for
multiplying the salary by the appropriate tax rates. I do have familiarity
with VBA if that's necessary, so if anyone can point me in the right
direction, it would be much appreciated!
 
P

Pieter Wijnen

I Would do the Calculations in The Detail Section
Assumes calculated Controls to be named CalcTax"Name"

Global Declares ' So You can Close the Rs in The Report Close Event

Dim Db As DAO.Database
Dim Rs AS DAO.Recordset


Private Sub Detail_Format(....)

Set Db = Access.CurrentDb
If Rs Is Nothing Then
Set Rs = Db.OpenRecordset("SELECT Percentage, Name FROM Assumptions Group
By Name", DAO.DbOpenSnapshot)
Else
Rs.MoveFirst
End If

While Not Rs.EOF
Me.Controls("CalcTax" & Rs.Fields(1).Value).Value = Rs.Fields(0).Value *
Me.Salary.Value
Rs.MoveNext
Wend

End Sub

HtH

Pieter
 
G

Guest

Pieter,

Thank you for your response. I should have qualified my knowledge of VBA is
advanced in Excel and Word, with Access lagging far behind. Could you help
an Access VBA novice with a few details?

First, what event will trigger this calculation - the opening of the report?
Second, in the line Private Sub Detail_Format(....), what goes in the
parenthesis? Thanks again for your help.
 
P

Pieter Wijnen

If You invoke the [Event Procedure] Builder, you'll se for your self <g>

But that aside

Private Sub Detail_Format(FormatCount As Integer, Cancel as Integer)

HtH

Pieter
 
P

Pieter Wijnen

you have to have [Event Procedure] in The Property Sheet for the code to
work anyway.
Usually Access does this for you if you paste the code into the Reports
module, but better safe than sorry

Pieter
 
G

Guest

Thank you for the quick follow-up. I will study your code and see if I can
apply it. Your assistance is much appreciated!
--
Steve C


Pieter Wijnen said:
you have to have [Event Procedure] in The Property Sheet for the code to
work anyway.
Usually Access does this for you if you paste the code into the Reports
module, but better safe than sorry

Pieter
 
G

Guest

Pieter,

Success! I have been able to make the report calculate the tax rates
correctly based on the salaries.

One more challenge: Because the report is grouped by the department the
employee is in, I would like to create subtotals for each of the taxes for
each department. I tried unsuccessfully creating a calculated field for each
tax based on the formula =Sum([CalcTaxFederal]), etc., but I'm sure it will
require code to build that formula. Could you give me some ideas on that?
Thanks much for your help!
--
Steve C


Pieter Wijnen said:
you have to have [Event Procedure] in The Property Sheet for the code to
work anyway.
Usually Access does this for you if you paste the code into the Reports
module, but better safe than sorry

Pieter
 
P

Pieter Wijnen

you don't need more code, just make (hidden) controls to "sum over group" &
make controls in the Group footer referring to them
ie
HiddenCalcTaxFederal =[CalcTaxFederal] (Sum Over Group)
GrpCalcTaxFederal =[HiddenCalcTaxFederal]

Glad I could help.

Pieter

Steve C said:
Pieter,

Success! I have been able to make the report calculate the tax rates
correctly based on the salaries.

One more challenge: Because the report is grouped by the department the
employee is in, I would like to create subtotals for each of the taxes for
each department. I tried unsuccessfully creating a calculated field for
each
tax based on the formula =Sum([CalcTaxFederal]), etc., but I'm sure it
will
require code to build that formula. Could you give me some ideas on that?
Thanks much for your help!
--
Steve C


Pieter Wijnen said:
you have to have [Event Procedure] in The Property Sheet for the code to
work anyway.
Usually Access does this for you if you paste the code into the Reports
module, but better safe than sorry

Pieter


Steve C said:
Pieter,

Thank you for your response. I should have qualified my knowledge of
VBA
is
advanced in Excel and Word, with Access lagging far behind. Could you
help
an Access VBA novice with a few details?

First, what event will trigger this calculation - the opening of the
report?
Second, in the line Private Sub Detail_Format(....), what goes in the
parenthesis? Thanks again for your help.
--
Steve C


:

I Would do the Calculations in The Detail Section
Assumes calculated Controls to be named CalcTax"Name"

Global Declares ' So You can Close the Rs in The Report Close Event

Dim Db As DAO.Database
Dim Rs AS DAO.Recordset


Private Sub Detail_Format(....)

Set Db = Access.CurrentDb
If Rs Is Nothing Then
Set Rs = Db.OpenRecordset("SELECT Percentage, Name FROM Assumptions
Group
By Name", DAO.DbOpenSnapshot)
Else
Rs.MoveFirst
End If

While Not Rs.EOF
Me.Controls("CalcTax" & Rs.Fields(1).Value).Value =
Rs.Fields(0).Value
*
Me.Salary.Value
Rs.MoveNext
Wend

End Sub

HtH

Pieter



In Access 2003, I'm trying to build a calculated field in a report
based
on
two unrelated tables. For example, in tblEmployees, the fields
include
FirstName, LastName, Dept. and Salary. In tblAssumptions (which
stores
tax
rates that are modified periodically), I have fields for Name and
Percentage.
Entries in this table are as follows:

NAME PERCENTAGE
Federal .08
State .05
Social Security .062
Medicare .0145

(The purpose of the Assumptions table is to allow our Accounting
department
the ability to forecast budgets based on changing tax rate
scenarios.
They
want to be able to open an Assumptions form, change any tax rate as
desired,
then re-run the report based on the updated assumptions.)

In the report, which will be grouped by Dept., I would like to list
each
employee and their salary. Along side that, I will have columns for
each
of
the taxes, with each tax rate multiplied by the salary. I'm
familiar
with
creating calculated fields, but I'm struggling with the correct
syntax
for
multiplying the salary by the appropriate tax rates. I do have
familiarity
with VBA if that's necessary, so if anyone can point me in the right
direction, it would be much appreciated!
 
G

Guest

Pieter,

I was able to make your suggestion work. I can't say thanks enough for all
the help you provided. And I learned a lot!
--
Steve C


Pieter Wijnen said:
you don't need more code, just make (hidden) controls to "sum over group" &
make controls in the Group footer referring to them
ie
HiddenCalcTaxFederal =[CalcTaxFederal] (Sum Over Group)
GrpCalcTaxFederal =[HiddenCalcTaxFederal]

Glad I could help.

Pieter

Steve C said:
Pieter,

Success! I have been able to make the report calculate the tax rates
correctly based on the salaries.

One more challenge: Because the report is grouped by the department the
employee is in, I would like to create subtotals for each of the taxes for
each department. I tried unsuccessfully creating a calculated field for
each
tax based on the formula =Sum([CalcTaxFederal]), etc., but I'm sure it
will
require code to build that formula. Could you give me some ideas on that?
Thanks much for your help!
--
Steve C


Pieter Wijnen said:
you have to have [Event Procedure] in The Property Sheet for the code to
work anyway.
Usually Access does this for you if you paste the code into the Reports
module, but better safe than sorry

Pieter


Pieter,

Thank you for your response. I should have qualified my knowledge of
VBA
is
advanced in Excel and Word, with Access lagging far behind. Could you
help
an Access VBA novice with a few details?

First, what event will trigger this calculation - the opening of the
report?
Second, in the line Private Sub Detail_Format(....), what goes in the
parenthesis? Thanks again for your help.
--
Steve C


:

I Would do the Calculations in The Detail Section
Assumes calculated Controls to be named CalcTax"Name"

Global Declares ' So You can Close the Rs in The Report Close Event

Dim Db As DAO.Database
Dim Rs AS DAO.Recordset


Private Sub Detail_Format(....)

Set Db = Access.CurrentDb
If Rs Is Nothing Then
Set Rs = Db.OpenRecordset("SELECT Percentage, Name FROM Assumptions
Group
By Name", DAO.DbOpenSnapshot)
Else
Rs.MoveFirst
End If

While Not Rs.EOF
Me.Controls("CalcTax" & Rs.Fields(1).Value).Value =
Rs.Fields(0).Value
*
Me.Salary.Value
Rs.MoveNext
Wend

End Sub

HtH

Pieter



In Access 2003, I'm trying to build a calculated field in a report
based
on
two unrelated tables. For example, in tblEmployees, the fields
include
FirstName, LastName, Dept. and Salary. In tblAssumptions (which
stores
tax
rates that are modified periodically), I have fields for Name and
Percentage.
Entries in this table are as follows:

NAME PERCENTAGE
Federal .08
State .05
Social Security .062
Medicare .0145

(The purpose of the Assumptions table is to allow our Accounting
department
the ability to forecast budgets based on changing tax rate
scenarios.
They
want to be able to open an Assumptions form, change any tax rate as
desired,
then re-run the report based on the updated assumptions.)

In the report, which will be grouped by Dept., I would like to list
each
employee and their salary. Along side that, I will have columns for
each
of
the taxes, with each tax rate multiplied by the salary. I'm
familiar
with
creating calculated fields, but I'm struggling with the correct
syntax
for
multiplying the salary by the appropriate tax rates. I do have
familiarity
with VBA if that's necessary, so if anyone can point me in the right
direction, it would be much appreciated!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top