Need some help please

[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

 

[Dale]

-----Original Message-----
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,

I am not sure if I completely understand you.
First you never want to make a feild in a table for a
calculated field. It is better to calculate each time so
if the data changes so does the calculated field.
In a query start an new field by naming it something and
follow it with a ":" colon. Example Category1:
Follow this with you expression. You may use only SQL
Syntax and statementsin queries. If{test} than {action} in
VBA = IIF((test,{if passes test},{else or not passes test})
To do math in a query is simple. example
Category1:[fieldname1]+[fieldname2]+[fieldname....]

Rather than an iif statement you might try this in your
criteria area of the expenses field - > Level3 AND <=Level4
or use "Between Level13 and Level14" Use caution when
using multiple criteria in a query. You need to be very
experenced in SQL to know for sure what you are getting is
what you really want.

Hope this helps a little.
Dale