I'm looking for help on how to do this ...

[ZBC]

I'm looking for help on how to do this ...
I have a query (qryClaimSummary) based on two existing tables (tblClaim
and tblPayments) that summarizes my records based on a grouping of one
of the fields [claim]. I use this query as a basis of printing a report.

I now need to some additional reports, but I need to add additional
equivalent of 'calculated fields'.
The additional 'calculated fields' would be based on the existing
summary query (qryClaimSummary) as well as information from two
additional tables (tblReten and tblMemded).
I am trying to avoid creating a separate table or adding fields to the
existing table in that I would not be 'adding information' ... although
I am willing to do so if that makes it simpler? (I have less that 2000
records.)

I do not know how to create the calculated fields using another query,
in that they require several logical calculations,

Basically, I need to take the 5 summary fields from my existing query
(qryClaimSummary) along with information from two additional tables
(tblReten and tblMemded) and create six 'calculated fields' for my reports.

I have created some logical IF statements to show the type of calculated
fields: (syntax may be wrong for VBA)
[note, the values of Level1, Level2, etc. come from the two additional
tables (tblReten and tblMemded)

IF Type = "GEN" and Value =< 4000 THEN
IF Expense <= Level1 THEN
Category1 = Expenses
End IF
IF((Expenses > Level1 AND Expenses <=Level2) THEN
Category1 = Level1
Category2 = Expenses-Level1
End IF
IF((Expenses > Level2 AND Expenses <=Level3)
Category1 = Level1
Category2 = Level2
Category3 = Expenses-(Level1+Level2)
End IF
IF((Expenses > Level3 AND Expenses <=Level4)
Category1 = Level1
Category2 = Level2
Category3 = Level3
Category4 = Expenses-(Level1+Level2+Level3)
End If
Expense1 = Category1 + Category2 + Category3 + Category4
End If
I want to use the values of Category1,Category2, etc. in reports as
though they were fields.
I am not a seasoned programmer of any kind so I would appreciate
examples, details and suggestions.

Bob

 

[Bob Barnes]

When you have all the needed fields in your Query Design,
try writing a Calculated field (ie, your "Category1"),
using IIF. It might get complex, but it should work.

HTH - Bob