Using a calculated field for another calc

[SusanArtman]

I'm using Access 2002.

In the grouping footer I have a =sum(abs([excused]=yes)) calculation that
returns 2 yes and 1 no. (for the below sample)
Now, I want to sum the hours where [excused]=yes and then multiply it by the
product of my previous calculation.
Yes
Ex: 2 x (8+4)
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

How do I do this?
Susan

 

[Jeff Boyce]

Susan

In reports, you'll need to use the underlying raw data/fields in your
calculations. You can't get by with referring to another control's results.

If you need to know the sum of Hours where [excused]=true, take a look at
the DSum() function.

If you need to multiply that answer by the number of [excused]=true, the
ControlSource for your control might look something like:

= Sum(Abs([excused])) * DSum(.....)

where you will look up/fill in the syntax for the DSum() function.

By the way, what kind of math are you doing that requires this product?
(curiosity...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SusanArtman]

Jeff,
I'm confused. I understand about the raw data. Thanks for that.
I don't understand how I use the DSum(...) function. What kind of syntax
goes into the ( )?

The math I'm doing looks like it should be pretty easy. I just want to add
the number of excused and unexcused hours in the grouping footer.
Employee:
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

Grp Ftr:
Excused 2 Hours Excused 12
Unexcused 1 Hours Unex 8

The math looks pretty easy. I'm just struggling with the formula.
Thanks in advance for your help. I can't tell you how much I appreciate your
time!
Susan

Susan

In reports, you'll need to use the underlying raw data/fields in your
calculations. You can't get by with referring to another control's results.

If you need to know the sum of Hours where [excused]=true, take a look at
the DSum() function.

If you need to multiply that answer by the number of [excused]=true, the
ControlSource for your control might look something like:

= Sum(Abs([excused])) * DSum(.....)

where you will look up/fill in the syntax for the DSum() function.

By the way, what kind of math are you doing that requires this product?
(curiosity...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2002.

In the grouping footer I have a =sum(abs([excused]=yes)) calculation that
returns 2 yes and 1 no. (for the below sample)
Now, I want to sum the hours where [excused]=yes and then multiply it by
the
product of my previous calculation.
Yes
Ex: 2 x (8+4)
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

How do I do this?
Susan

 

[Klatuu]

VBA Help can explain the use of the Dsum function.
How is it that yes returns 2 and no returns 1? Are you using just a numeric
field rather than a boolean field?

I would suggest to show the excused, a DCount would do. The DCount and DSum
functions should work on the same recordset you are using for your report.
To get excused:
=DCount("*","MyRecordSet", "excused = 2")

the "excused = 2" part filters the count. If there is any other filtering
used in your report's recordset, you need to include that as well.

Then to get the hours:
=DSum("hours","MyRecordSet", "excused = 2")

--
Dave Hargis, Microsoft Access MVP

Jeff,
I'm confused. I understand about the raw data. Thanks for that.
I don't understand how I use the DSum(...) function. What kind of syntax
goes into the ( )?

The math I'm doing looks like it should be pretty easy. I just want to add
the number of excused and unexcused hours in the grouping footer.
Employee:
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

Grp Ftr:
Excused 2 Hours Excused 12
Unexcused 1 Hours Unex 8

The math looks pretty easy. I'm just struggling with the formula.
Thanks in advance for your help. I can't tell you how much I appreciate your
time!
Susan

Susan

In reports, you'll need to use the underlying raw data/fields in your
calculations. You can't get by with referring to another control's results.

If you need to know the sum of Hours where [excused]=true, take a look at
the DSum() function.

If you need to multiply that answer by the number of [excused]=true, the
ControlSource for your control might look something like:

= Sum(Abs([excused])) * DSum(.....)

where you will look up/fill in the syntax for the DSum() function.

By the way, what kind of math are you doing that requires this product?
(curiosity...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2002.

In the grouping footer I have a =sum(abs([excused]=yes)) calculation that
returns 2 yes and 1 no. (for the below sample)
Now, I want to sum the hours where [excused]=yes and then multiply it by
the
product of my previous calculation.
Yes
Ex: 2 x (8+4)
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

How do I do this?
Susan

 

[SusanArtman]

The 2 is the count of the excused absence and 1 is the count of the unexcused?
Or there are 2 trues and 1 false as it is a yes/no field.

VBA Help can explain the use of the Dsum function.
How is it that yes returns 2 and no returns 1? Are you using just a numeric
field rather than a boolean field?

I would suggest to show the excused, a DCount would do. The DCount and DSum
functions should work on the same recordset you are using for your report.
To get excused:
=DCount("*","MyRecordSet", "excused = 2")

the "excused = 2" part filters the count. If there is any other filtering
used in your report's recordset, you need to include that as well.

Then to get the hours:
=DSum("hours","MyRecordSet", "excused = 2")

--
Dave Hargis, Microsoft Access MVP

Jeff,
I'm confused. I understand about the raw data. Thanks for that.
I don't understand how I use the DSum(...) function. What kind of syntax
goes into the ( )?

The math I'm doing looks like it should be pretty easy. I just want to add
the number of excused and unexcused hours in the grouping footer.
Employee:
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

Grp Ftr:
Excused 2 Hours Excused 12
Unexcused 1 Hours Unex 8

The math looks pretty easy. I'm just struggling with the formula.
Thanks in advance for your help. I can't tell you how much I appreciate your
time!
Susan

Susan

In reports, you'll need to use the underlying raw data/fields in your
calculations. You can't get by with referring to another control's results.

If you need to know the sum of Hours where [excused]=true, take a look at
the DSum() function.

If you need to multiply that answer by the number of [excused]=true, the
ControlSource for your control might look something like:

= Sum(Abs([excused])) * DSum(.....)

where you will look up/fill in the syntax for the DSum() function.

By the way, what kind of math are you doing that requires this product?
(curiosity...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2002.

In the grouping footer I have a =sum(abs([excused]=yes)) calculation that
returns 2 yes and 1 no. (for the below sample)
Now, I want to sum the hours where [excused]=yes and then multiply it by
the
product of my previous calculation.
Yes
Ex: 2 x (8+4)
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

How do I do this?
Susan

 

[Klatuu]

Thanks, I wasn't sure. Doesn't change anything except the syntax
=DCount("*","MyRecordSet", "excused = -1") for yes
=DCount("*","MyRecordSet", "excused = 0") for no

=DSum("hours","MyRecordSet", "excused = -1")
--
Dave Hargis, Microsoft Access MVP

The 2 is the count of the excused absence and 1 is the count of the unexcused?
Or there are 2 trues and 1 false as it is a yes/no field.

VBA Help can explain the use of the Dsum function.
How is it that yes returns 2 and no returns 1? Are you using just a numeric
field rather than a boolean field?

I would suggest to show the excused, a DCount would do. The DCount and DSum
functions should work on the same recordset you are using for your report.
To get excused:
=DCount("*","MyRecordSet", "excused = 2")

the "excused = 2" part filters the count. If there is any other filtering
used in your report's recordset, you need to include that as well.

Then to get the hours:
=DSum("hours","MyRecordSet", "excused = 2")

--
Dave Hargis, Microsoft Access MVP

Jeff,
I'm confused. I understand about the raw data. Thanks for that.
I don't understand how I use the DSum(...) function. What kind of syntax
goes into the ( )?

The math I'm doing looks like it should be pretty easy. I just want to add
the number of excused and unexcused hours in the grouping footer.
Employee:
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

Grp Ftr:
Excused 2 Hours Excused 12
Unexcused 1 Hours Unex 8

The math looks pretty easy. I'm just struggling with the formula.
Thanks in advance for your help. I can't tell you how much I appreciate your
time!
Susan




:

Susan

In reports, you'll need to use the underlying raw data/fields in your
calculations. You can't get by with referring to another control's results.

If you need to know the sum of Hours where [excused]=true, take a look at
the DSum() function.

If you need to multiply that answer by the number of [excused]=true, the
ControlSource for your control might look something like:

= Sum(Abs([excused])) * DSum(.....)

where you will look up/fill in the syntax for the DSum() function.

By the way, what kind of math are you doing that requires this product?
(curiosity...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using Access 2002.

In the grouping footer I have a =sum(abs([excused]=yes)) calculation that
returns 2 yes and 1 no. (for the below sample)
Now, I want to sum the hours where [excused]=yes and then multiply it by
the
product of my previous calculation.
Yes
Ex: 2 x (8+4)
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

How do I do this?
Susan

 

[Marshall Barton]

The math I'm doing looks like it should be pretty easy. I just want to add
the number of excused and unexcused hours in the grouping footer.
Employee:
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

Grp Ftr:
Excused 2 Hours Excused 12
Unexcused 1 Hours Unex 8

It seems like all you need is:

Excused =Abs(Sum(Excused))
Hours Excused =Abs(Sum(Excused * Hours))
Unexcused =Abs(Sum(Not Excused))
Hours Unex =Abs(Sum((Not Excused) * Hours))

 

[KARL DEWEY]

If you do not need the details then you can use this query ---
SELECT Abs(Sum([Excused])) AS [Excused Absence],
Count([Excused])-Abs(Sum([Excused])) AS Unexcused,
Sum(IIf([Excused]=-1,[Hours],0)) AS [Excused Hours],
Sum(IIf([Excused]=0,[Hours],0)) AS [Unexcused Hours]
FROM Employee;

 

[SusanArtman]

Karl,
Thank you. I was able to use the sum(IIf function and everything worked!
I'm so thankful to you! Keep up the good work. Merry Christmas.
Susan

If you do not need the details then you can use this query ---
SELECT Abs(Sum([Excused])) AS [Excused Absence],
Count([Excused])-Abs(Sum([Excused])) AS Unexcused,
Sum(IIf([Excused]=-1,[Hours],0)) AS [Excused Hours],
Sum(IIf([Excused]=0,[Hours],0)) AS [Unexcused Hours]
FROM Employee;

--
KARL DEWEY
Build a little - Test a little

I'm using Access 2002.

In the grouping footer I have a =sum(abs([excused]=yes)) calculation that
returns 2 yes and 1 no. (for the below sample)
Now, I want to sum the hours where [excused]=yes and then multiply it by the
product of my previous calculation.
Yes
Ex: 2 x (8+4)
date excused Hours
12/3/2007 yes 8
12/102007 yes 4
12/20/2007 no 8

How do I do this?
Susan