How to count unique records in report

[ozean]

I have a form with a subform. At the form I have checkboxes and comboboxes that filters the data in the subform (query based on at table). At the report I count all the records that has been filtered, which is working fine, but some of the records are grouped together and I only want to count the group.

These are the fields:

Field "Signal designation" is different signals
Field "Alarm type" is different alarm types
Field "RTU" contains a "X" or "G". "G" is used for grouped records and "X" for single records.
Field "RTU_GROUP_NO" is filled in with a number if field RTU has a "G". This number will separate the grouped signals.

Lets say that we have eight (8) signals where three (3) of them is grouped together.
_____________________________________________
|Signal designation|Alarm type|RTU|RTU_GROUP_NO|
---------------------------------------------------------------
|Signal 1 |Status | X | |
|Signal 2 |Trip | G | 1 |
|Signal 3 |Trip | G | 1 |
|Signal 4 |Trip | G | 2 |
|Signal 5 |Trip | G | 2 |
|Signal 6 |Trip | G | 2 |
|Signal 7 |Trip | G | 3 |
|Signal 8 |Trip | G | 3 |


In the report I will have a count of eight (8), but I want report to count only four (4) as three of them is grouped.

Any ideas?

Regards
Stefan

 

[dhookom]

You can create a sorting and grouping level of TRU_GROUP_NO and display the group header. Add a text box to the group header:
[tt][blue]
Name: txtCountGroup
Control Source: =1
Running Sum: Over All
Visible: No
[/blue][/tt]
Then add a text box to the Report Footer:
[tt][blue]
Control Source: =txtCountGroup
[/blue][/tt]
If you can't create this group header then create group yb and totals query that result in a single record with your count. You can add the one record query to your report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ozean]

I am using this code today to return the total records in page header which works fine. The problem I have is that some of my signals are grouped and I want Access to count the group and not all signals in the group. Please, let me know if I misunderstood you.

Thanks
Stefan

 

[dhookom]

There is no code involved in my solutions. Did you try create a group header or footer as I suggested? Did you try create the grouping queries to arrive at a single count?



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ozean]

I have tried a grouping query (QryRTUgrouped)that is based on the table which returns correct number, but my problem is that the count is not based on my filtered data in the form.

I have put a textbox at report header with
control source: =DCount("RTU_GROUP_NO";"QryRTUgrouped") but this returns the same number independent of how I have filtered my data.

Thanks for taking your time
S

 

[dhookom]

Can't you filter your grouping query by the same filter as your report? You haven't suggested how your controls filter the report.

Did you try to group by RTU_GROUP_NO and use the running sum solution?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]