Hi All,

Here is a somewhat easy to understand task, but I am unable to figure out a way to do it. I have always been using the "total on" method in DBase3 to do this. Yes. DBase3 is a VERY old software! :-) This is the reason I am trying to convert all my report to web-based.

Anyhow, I have a table with 6 columns:

Health_ID|Ded_Amt|Employee_ID|Admin_amt|Employee_a mt|Employer_amt
01FEE 10.00 11111
01FEA 5.00 11111
01FER 20.00 11111

Here is what I want to do:

I want to run a report where the three empty fields(Admin_amt, Employee_amt, and Employer_amt) values be replaced with Ded_Amt accordingly. In other words, If the last digit of the Health_ID is "E," then Employee_Amt will have the value of "10.00." If the digit is "A," then Admin_amt will be "5.00." You get my point?

So, if I were to write an "If..." statment it would look something like this(but I don't know how to do it using SQL):
If right(Health_ID,1)="E" then Employee_amt=Ded_amt?

Hence, the end result will look like this:

Health_ID|Ded_Amt|Employee_ID|Admin_amt|Employee_a mt|Employer_amt
01FEE 10.00 11111 5.00 10.00 20.00

Is there any possible way to do this using SQL server query string?
If I can do this, then I can use the "Group By All" method in SQL to combine the three records altogether.

Please Help! Thank you.

Leon

How about that 20.00? under what circumstances does it come or what is the logic for getting that value?

_________________________
-Vijay G
Strive for Perfection

Sorry, i was just being lazy to type out all 3 columns. If the last digit of Health_ID is "R," then "Employer_amt" will be $20.00. I think the values got shifted when I post this topic.

In other words, here is what the last 3 columns should look like:

Admin_amt|Employee_amt|Employer_amt
5.00 10.00 20.00

The reason I am creating this report is that the records I get from the database will have 3 records per employee. We have 3 different costs. The employee's cost, administrative fee, and Employer's cost.

By the way Vijay, how do you put in edits in the postings? I notice you can color texts, put in horizontal bars, etc.

Thank you.

Leon

Nevermind the last question about the edits. I figured out. It was right in front of my eyes ~ "Forum Code". Sorry.

Are you trying to do this from ASP or directly at the backend(your BD server)?

Regarding the formatting of Posts, when you reply, just above the edit box you can alway see some TOOL BUTTONs, try playing with that, you would master in that too.

Actually (quote)text(/quote) was the one I used in my previous post.
PS: Replace () with [], I used () because, it might again get formatted when I post this.

b for bold
red for red color
It is all there.
Also on the left of the EDIT box, when you reply to a post, you can see the FORMAT code link, which has all that.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

Quote: Are you trying to do this from ASP or directly at the backend(your BD server)?

Which ever way is easier. I know IF I can do it with SQL, then that will make things a lot easier b/c then I can use the "Group By All" in my SQL statement. I would prefer to use my DB server.