A Really Hard One

[Gary.Lowe]

I have a database Table with the following fields

Result-Text
HBL-Boolean
HLD-Boolean
HELDRD-Boolean
Area-Text
Account Number-text

This is the problem, I need to create a report with the following fields
---------------------------
Area

Result - HBL - HLD - HELDRD - Total
---------------------------

The HBL, HLD & HELDRD are counts of the results

i.e.
Result = Account Query, HBL = True, HLD = False, HELDRD = True

I need to group the results together so the report displays only one of each (DISTINCT) WHICH I have done.

Next I need to be able to show a count of the HBL, HLD & HELDRD.

The problem is that on one account HBL, HLD & HELDRD could be marked true. And I want to show the counts of only one account.

i.e.
Result = Account Query * 200
HBL = True * 100
HLD = True *50
HELDRD= True * 75

The total would be 200
but the count of the other fields would bring this up to 225

There are 25 HELDRD which are also classed as either HBL & HLD

This knocks the count out by 25


There is a priority HLD, HBL, HELDRD
e.g

One account Has HLD & HELDRD = True
HLD would overide HELDRD and count that as a HLD only and not HELDRD as well.


I don't know if this is possible and hope I have explained it well enough.

If any can do this I will be well impressed.

Thanks

[HunterMcCray]

I think this will work, since true <> 0 and false =0 if you were to create a query of the table that added a sum field then you could simply have the query display records where the sum of HBL, HLD & HELDRD <> 0. You could also use boolean operators, but this approch would require a more complex query.

Hunter