counting a record only once 3

[circles]

I work for an insurance company and I am trying to build a claims data base. The problem is with Liability claims. These claims have two parts medical and indemnity payments and have the same claim number. For example a policy holder may have two liability claims but because of the two parts my query currently shows the policy holder to have four claims and these are not duplicate records.


What I am trying to do is set a criteria that will count the claim only once by using an Iif statement. Such as: if the claim number and date of loss are the same Then only count as one claim for the policy holder. Any help is greatly appreciated

 

[Katerine]

I usually solve this problem by grouping by fields. In the query, select View | Totals so that you can see the Totals column (if you couldn't already). Then group by claim number and claim part (and any other higher-level groupings you may have, such as claim type), then set any lower-level groupings (details - such as amounts, etc.) to "First" or "Last" or some other non-tallying item.

Hope that helps! Please let me know if you have any questions.

 

[ghubbell]

Hi Circles!
I think you'll have to try playing with the Unique values or unique records properties of the query: Double click anywhere on the query background to pop up the queries properties. Try flipping "Unique Values" to Yes and see if you get the results your after..If not, there are 3 combinations available between unique values and records...Still no sucess? Write back and we'll play with sorting & grouping! Gord
ghubbell@total.net

 

[link9]

I think what you are looking for is the DISTINCT keyword...

SELECT DISTINCT claimNumber FROM yourTable;

If there are five of one claim number, it will only return one...

hope it helps!
Paul Prewett

 

[circles]

I have it working somewhat to what i need i will continue to work on it. Thank you for your assistance