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

 

[ZBC]

I'm not sure how to put an IIF into a calculated field of a query ...
can you give me some example(s)?
Thanks...
Bob

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

-----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 Barnes]

Something like this (in the 1st Row) of the design grid...

TheGroup: IIf(([McnID]=166 Or [McnID]
=167),"First","Second")

Try using the grid. If you've never done a Calculated
field, you might stumble initially, but you'll get better
by practicing that.

HTH - Bob

-----Original Message-----
I'm not sure how to put an IIF into a calculated field of a query ...
can you give me some example(s)?
Thanks...
Bob

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

-----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

 

[ZBC]

Well, I stumbled my fair share ... but I have most of it working!
THANKS for your help!
Bob

Something like this (in the 1st Row) of the design grid...

TheGroup: IIf(([McnID]=166 Or [McnID]
=167),"First","Second")

Try using the grid. If you've never done a Calculated
field, you might stumble initially, but you'll get better
by practicing that.

HTH - Bob

-----Original Message-----
I'm not sure how to put an IIF into a calculated field of

a query ...

can you give me some example(s)?
Thanks...
Bob

Bob Barnes wrote:

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





-----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