Calculate with results from 2 queries

[Guest]

I have a rather complex question. A bit of background first... I have a
table containing fields for InsuranceType, IssueDate, PremiumAmount, and
CancellationDate. Users add/edit information via a form. What I need to do
is calculate total monthly premiums for each type of insurance. Sounds easy,
but I need to add premium amounts for insurance sold and subtract premium
amounts for any records that contain a cancellation date. The query I have
thus far is:

InsuranceTypeID
tblInsuranceType
Group By
Criteria: "1"

CancellationDate
tblInsurance
Group By

Expr1:
Sum(IIf([CancellationDate]=True,[tblInsurance]![PremiumAmount]*-1,[tblInsurance]![PremiumAmount]))
Expression

The result I get is a list of premiums for that insurance type with
cancelled premiums shown as negatives, i.e. ($123.45). Where do I go from
here? Is there an easier way to get to the result I'm looking for? I'm
rather new to Access and am using Access 2000. Any guidance will be greatly
appreciated.

Thank you,
Jen

 

[Steve Schapel]

Jen,

I am pretty sure I know what you mean. If so, remove the
CancellationDate field from the query, and change the Expr1 expression
like this...
Sum(IIf(IsNull([CancellationDate]),[PremiumAmount]*-1,[PremiumAmount]))

I don't understand what the criteria "1" in the InsuranceTypeID field is
all about. I think you would need to remove this if you want the totals
for each type of insurance.

If this is not what you need, can you please post back with some
specific examples of the output you expect from the query? Thanks.

 

[Guest]

Steve,
Thank you so much for your help! The new expression worked nearly
perfectly. The only change I made was to move the *-1 to the end of the
expression so that cancelled premiums are subtracted.

Expr1: Sum(IIf(IsNull([CancellationDate]),[PremiumAmount],[PremiumAmount]*-1))

Nothing is so beautiful as a query that finally works after you've fretted
over it for hours!! Thank you!

The criteria "1" was the type of insurance sold. I'll recreate the query
for two other types of coverage as well so that reports can be generated for
individual insurance types.

Thank you again for your help! I truly appreciate it!

Jen

Jen,

I am pretty sure I know what you mean. If so, remove the
CancellationDate field from the query, and change the Expr1 expression
like this...
Sum(IIf(IsNull([CancellationDate]),[PremiumAmount]*-1,[PremiumAmount]))

I don't understand what the criteria "1" in the InsuranceTypeID field is
all about. I think you would need to remove this if you want the totals
for each type of insurance.

If this is not what you need, can you please post back with some
specific examples of the output you expect from the query? Thanks.

--
Steve Schapel, Microsoft Access MVP

I have a rather complex question. A bit of background first... I have a
table containing fields for InsuranceType, IssueDate, PremiumAmount, and
CancellationDate. Users add/edit information via a form. What I need to do
is calculate total monthly premiums for each type of insurance. Sounds easy,
but I need to add premium amounts for insurance sold and subtract premium
amounts for any records that contain a cancellation date. The query I have
thus far is:

InsuranceTypeID
tblInsuranceType
Group By
Criteria: "1"

CancellationDate
tblInsurance
Group By

Expr1:
Sum(IIf([CancellationDate]=True,[tblInsurance]![PremiumAmount]*-1,[tblInsurance]![PremiumAmount]))
Expression

The result I get is a list of premiums for that insurance type with
cancelled premiums shown as negatives, i.e. ($123.45). Where do I go from
here? Is there an easier way to get to the result I'm looking for? I'm
rather new to Access and am using Access 2000. Any guidance will be greatly
appreciated.

Thank you,
Jen

 

[Steve Schapel]

Jen,

I am not sure if this is what you meant... But there are better ways
than making a separate query and report for each of the insurance type
categories.