-
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
-
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
