Can I do this with a query?

[ZBC]

I'm looking for help on solving this ...
What I need is the equivalent of some additional fields in a table, but
they would be calculated fields ... I don't think I want them to be part
of the actual tables. They could be part of a table, but I would be
duplicating information (even though my tables are less that 2000 records.

Basically, I need to take an expense field and create (divide up it's
value) based on it's value.
In the end:
Category1 + Category2 + Category3 + Category4 = Expenses
I created some IF statements (syntax may be wrong for VBA) to reflect
what I think I need to do.

If(Expenses <= Level1 THEN
Category1 = Expenses
EndIF

IIF((Expenses > Level1 AND Expenses <=Level2) THEN
Category1 = Level1
Category2 = Expenses-Level1
EndIF

IIF((Expenses > Level2 AND Expenses <=Level3)
Category1 = Level1
Category2 = Level2
Category3 = Expenses-(Level1+Level2)
EndIF

IIF((Expenses > Level3 AND Expenses <=Level4)
Category1 = Level1
Category2 = Level2
Category3 = Level3
Category4 = Expenses-(Level1+Level2+Level3)

I want to use the values of Category1,Category2, etc. in reports as
though they were fields.

Can I do this with a query?
Bob

 

[SteveS]

What is your table name and structure? Do you have fields named
Expenses, Level1, Level2, Level3 and Level4?

Steve

 

[ZBC]

SteveS,
Thanks for your response. I will try to explain it a bit clearer ...

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 currently use this query as a basis of
printing one reports.

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). The values of Level1,
Level2, etc. come from these two additional tables (tblReten and tblMemded).

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

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)

Bob

 

[SteveS]

It *would* help to know the names of the fields in the tables or the
query (what I asked), but in general this is how to create a calculated
field in a query.

In The query, in empty columns in the grid of the FIELD row, enter

Category1: IIf(Expenses <= Level1,Expenses,Level1)

' each of the following lines should be on one line
Category2: IIf(Expenses > Level1 AND Expenses <= Level2,
Expenses-Level1,Level2)

Category3: IIf(Expenses > Level2 AND Expenses <= Level3,
Expenses-(Level1+Level2),Level3)

Category4: IIF(Expenses > Level3 AND Expenses <=Level4,
Expenses-(Level1+Level2+Level3),0)


so you end up with 4 new (calculated) columns. Run the query.

Note: Category 1 -4 will change on a per record basis just as if they
were 'real' fields in a table.


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

SteveS,
Thanks for your response. I will try to explain it a bit clearer ...

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 currently use this query as a basis of
printing one reports.

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). The values of Level1,
Level2, etc. come from these two additional tables (tblReten and
tblMemded).

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

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)

Bob

 

[ZBC]

Steve,

I'm sorry, I misunderstood what you were asking!

I believe I understand what you are saying ...
If I can't get it to work; I will post back this evening.
I THANK YOU!

Bob
[... and I often believe people who respond of not reading my questions
.... I will do better next time ... I do very much appreciate your
help!]

It *would* help to know the names of the fields in the tables or the
query (what I asked), but in general this is how to create a
calculated field in a query.

In The query, in empty columns in the grid of the FIELD row, enter

Category1: IIf(Expenses <= Level1,Expenses,Level1)

' each of the following lines should be on one line
Category2: IIf(Expenses > Level1 AND Expenses <= Level2,
Expenses-Level1,Level2)

Category3: IIf(Expenses > Level2 AND Expenses <= Level3,
Expenses-(Level1+Level2),Level3)

Category4: IIF(Expenses > Level3 AND Expenses <=Level4,
Expenses-(Level1+Level2+Level3),0)


so you end up with 4 new (calculated) columns. Run the query.

Note: Category 1 -4 will change on a per record basis just as if they
were 'real' fields in a table.


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

SteveS,
Thanks for your response. I will try to explain it a bit clearer ...

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 currently use this query as
a basis of printing one reports.

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). The values of Level1,
Level2, etc. come from these two additional tables (tblReten and
tblMemded).

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

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)

Bob

What is your table name and structure? Do you have fields named
Expenses, Level1, Level2, Level3 and Level4?

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ZBC wrote:

I'm looking for help on solving this ...
What I need is the equivalent of some additional fields in a table,
but they would be calculated fields ... I don't think I want them
to be part of the actual tables. They could be part of a table,
but I would be duplicating information (even though my tables are
less that 2000 records.

Basically, I need to take an expense field and create (divide up
it's value) based on it's value.
In the end:
Category1 + Category2 + Category3 + Category4 = Expenses
I created some IF statements (syntax may be wrong for VBA) to
reflect what I think I need to do.

If(Expenses <= Level1 THEN
Category1 = Expenses
EndIF

IIF((Expenses > Level1 AND Expenses <=Level2) THEN Category1 =
Level1
Category2 = Expenses-Level1
EndIF

IIF((Expenses > Level2 AND Expenses <=Level3)
Category1 = Level1
Category2 = Level2 Category3 = Expenses-(Level1+Level2)
EndIF

IIF((Expenses > Level3 AND Expenses <=Level4)
Category1 = Level1
Category2 = Level2
Category3 = Level3
Category4 = Expenses-(Level1+Level2+Level3)

I want to use the values of Category1,Category2, etc. in reports as
though they were fields.

Can I do this with a query?
Bob