IIF statement needed

[nydia]

I have a substance abuse client database that keeps track
of clients that attend meeting. The form has date of
meeting, time etc. There is a field for a billing code
that the data entry person has to enter. A report is then
generated and sent to accounting so they can bill. I need
to put in the report an IIF statement that will see what
billing code was entered for that particular meeting and
then generate a level. there are about 7 billing codes for
level 1 and 7 for level 2

ex.
billing code 010101, 010102, 010103 are all level 1
billing codes

billing code 010122, 010123, 010124 are all level 2

so if 010101 was entered into the billing code i need a 1
to appear as the level.

any help is greatly appreciated.

 

[Duane Hookom]

Add a field to your table of unique billing codes that describes the billing
level.

 

[nydia]

If i do this, the data entry person would have to know the
which is level 1 or 2. Once the billing code is entered,
i want it to automatically generate the level. Is there a
way to do this???

 

[Duane Hookom]

I thought each unique billing code would always be either 1 or 2. Do you
have a table of unique billing codes?

 

[nydia]

ooppss, i think i posted my respond as a new post. SORRY

no i dont. maybe i'm not understanding what you mean. i
know what billing code is what level, but the data entry
person doesn't. that is why i want it to automatically
generate. I don't want the data entry person to have to
look up the billing code to see what level it is.

can't an iif statment be used in the report to do this?
..

FYI: there are 7 billing codes to 1 level

 

[Duane Hookom]

I like to build my database applications to use data rather than IIf()
statements with complex expressions. Consider creating a table (since you
don't have one) of each unique billing code as one field and the Level as
the other field. You can then join this table to other tables so the Level
is available.

For instance, consider adding a "DiscountPct" field to the Customer table in
northwind. You could then use the value from this field in an invoice report
to calculate a final invoice amount minus the discount.

 

[nydia]

ahhhhhhhh, i think i know what you mean. but, if you use
the billing code as the primary key, you won't be able to
duplicate it? one person could have the same billing code
3x's in a week.

 

[Duane Hookom]

Does the Level change for the same billing code?

 

[nydia]

no, if the billing code is 010101 then the level will
alway be 1, but a client could have that billing code more
than once and another client will also have that billing
code.

 

[Duane Hookom]

Then, you only need to store the level once for each billing code. That's
how relational databases work. The Level is an attribute of the billing
code.
For instance, you might have an employee table with a field like HomePhone.
You would not store the home phone in every table that had your employee ID
field in it. You only store the employee ID and if you need the home phone
to display, you add the employee table to the report.

 

[nydia]

ok, i think i got it. Thanks soooo much for all your
help. It's great to have someone with patients answer your
question. Youve been a great help.