Need help with query

[Attila Fust]

I need to develop the following query.

Table (Claims)
CustNO claim_code #_of_claims Fee
1000 1 2 26.95
1001 2 3 300.00
1002 3 2 560.00

Table (Claim_Codes)
claim_code fee
1 1
2 125.00
3 340.00

*****tables linked on claim code

In the query I need to do the following:

1) Select CustNO and #_of_claims
2) Build an expression to do the following (for a third
field in the query)

i)If claim_code > 1, set the value to "#_of_claims" in
Claims table * "fee" in Claim_Codes table (eg. for custNO
1002 the value would be set to 680.00 ie. 2 * 340.00)

ii)If claim_code is 1, set the value to the actual value
in Claims table (eg. in example above the value would be
set to 26.95 for custNO 1000)

Can this be done with a simple query?

Thanks in advance.

Attila Fust

 

[John Vinson]

I need to develop the following query.

Table (Claims)
CustNO claim_code #_of_claims Fee
1000 1 2 26.95
1001 2 3 300.00
1002 3 2 560.00

Table (Claim_Codes)
claim_code fee
1 1
2 125.00
3 340.00

*****tables linked on claim code

In the query I need to do the following:

1) Select CustNO and #_of_claims
2) Build an expression to do the following (for a third
field in the query)

i)If claim_code > 1, set the value to "#_of_claims" in
Claims table * "fee" in Claim_Codes table (eg. for custNO
1002 the value would be set to 680.00 ie. 2 * 340.00)

ii)If claim_code is 1, set the value to the actual value
in Claims table (eg. in example above the value would be
set to 26.95 for custNO 1000)

You can use the IIF() function for this purpose. The need to do this
suggests that your table isn't properly normalized (the fact that the
field Fee has a different meaning for claim_code 1 and other
claim_codes is a red flag), but sometimes that's life. Try:

SELECT [CustNo], [#_of_claims], IIF([Claim_Code] = 1, [Claims].[Fee],
[Claim_Codes].[Fee]
FROM Claims INNER JOIN Claim_Codes
ON Claims.Claim_Code = Claim_Codes.Claim_code;