Count Unique Values with sorting and filtering by pop-up form

[Guest]

Hi, I downloaded the sample database "RptSmp97.mdb" and extracted the code I needed. Everything is working fine and I even achieved to mix both Sorting and Filtering in the same pop-up form. Now I try to count unique values for one of the value on the report. The only way I found is to activate Group/level options and add a textbox with the Control source properties set to: =1 .In Doing so it makes the sorting to go wrong. Instead of sorting all the records on the report together, each group sorts separately.

I am sure there is a way to count unique values without group/level

Any Ideas

 

[Duane Hookom]

You can create a totals query that groups by your significant field(s) and
counts the number of records. Add this query to your report's record source
and join the significant field(s). You can then add the CountOf... to the
query grid.

--
Duane Hookom
MS Access MVP
--

Hi, I downloaded the sample database "RptSmp97.mdb" and extracted the code

I needed. Everything is working fine and I even achieved to mix both Sorting
and Filtering in the same pop-up form. Now I try to count unique values for
one of the value on the report. The only way I found is to activate
Group/level options and add a textbox with the Control source properties set
to: =1 .In Doing so it makes the sorting to go wrong. Instead of sorting all
the records on the report together, each group sorts separately.

 

[Guest]

I already tried to add a query but it did not work.(the way I have done it) Here's the problem : The report is loaded by the pop-pop form and the textbox that is supose to get the unique count result is located in the report footer. Everytime I filter the report (dynamicely), the report data change and the unique count must be recalculated. The only way I found to do that is to put reference in the query to the textbox(used to filter) on the pop-up form. But, the unique count textbox (locaded in the report footer) is updated before the pop-up windows is completly loaded (by the ReportFooter Format envent) than cause a other pop-up windows asking me to enter manualy what I put in the querie as reference.

Is there a other way to use a querie in this case

----- Duane Hookom wrote: ----

You can create a totals query that groups by your significant field(s) an
counts the number of records. Add this query to your report's record sourc
and join the significant field(s). You can then add the CountOf... to th
query grid

--
Duane Hooko
MS Access MV
-

Hi, I downloaded the sample database "RptSmp97.mdb" and extracted the cod

I needed. Everything is working fine and I even achieved to mix both Sortin
and Filtering in the same pop-up form. Now I try to count unique values fo
one of the value on the report. The only way I found is to activat
Group/level options and add a textbox with the Control source properties se
to: =1 .In Doing so it makes the sorting to go wrong. Instead of sorting al
the records on the report together, each group sorts separately

 

[Duane Hookom]

You might be able to use a subquery in your report's record source. The
subquery could count and return the number of matching records. I don't
quite understand why your first attempt didn't work unless you didn't want
separate groups.

--
Duane Hookom
MS Access MVP
--

I already tried to add a query but it did not work.(the way I have done

it) Here's the problem : The report is loaded by the pop-pop form and the
textbox that is supose to get the unique count result is located in the
report footer. Everytime I filter the report (dynamicely), the report data
change and the unique count must be recalculated. The only way I found to do
that is to put reference in the query to the textbox(used to filter) on the
pop-up form. But, the unique count textbox (locaded in the report footer) is
updated before the pop-up windows is completly loaded (by the ReportFooter
Format envent) than cause a other pop-up windows asking me to enter manualy
what I put in the querie as reference.

 

[Duane Hookom]

I don't see in your example where the count of unique P/Ns is located. Also,
the sort looks much the same in both example lists. I have never relied on
setting the OrderBy property. You could filter a group by query so that you
could count unique P/Ns. I don't know how you are building your filter on
your main report.

--
Duane Hookom
MS Access MVP

I, in fact, don't want separate groups because it causes my sorting to go

wrong. My report's record source is the Table it self because I wanted to
create a report that open with all the records on it and than give you the
choice to filter and sort dynamically what you want on it by the pop-up form
(to give extreme flexibility). The filter/sort are done by building SQL
expression (in VB) and affect them to the report's filter/orderby
properties. Once it is filter/sort the way I want, I need to put the number
of unique record in a summery located in the report footer section.

Here's an example of what I want to do once it is filter:

P/N Date Quantity Selling Price ...
C6WM4567-1 2004-05-17 3 100$
C6WM4567-1 2004-05-05 5 100$
C6FM7694-12 2004-05-06 2 50$

I want to know how many different P/N we received. If I use group Level, I

will be easily able to know how many unique P/N there are (by counting the
number of group) but than if I sort the report (dynamically) here is the
result:

P/N Date Quantity Selling Price ...
C6WM4567-1 2004-05-05 5 100$
C6WM4567-1 2004-05-17 3 100$
C6FM7694-12 2004-05-06 2 50$

The sort is done for each group separately.

So, here I am right now. I only know access since 3 months. I am pretty

strong in VB but don't know the Access/SQL mechanic very well. I am sure
there is an easy way to do what I want but unfortunately we cannot put SQL
expression in a textbox's record source properties.

 

[Guest]

Actually, maybe I express my-self wrong, I am sorry. I don't want to count unique P/N but how many different P/N we received, which sound very similar to me. In the last example, the sort is done by date (I forgot to mention it). Here is what the result is suppose to look like:

P/N Date Quantity Selling Price ...
C6WM4567-1 2004-05-05 5 100$
C6FM7694-12 2004-05-06 2 50$
C6WM4567-1 2004-05-17 3 100$

Now, here is a sample of code I use to apply filter

Private Sub SetFilter_Click()
Dim strSQL As String
Dim intCounter As Integer

For intCounter = 1 To 12
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & _
Chr(34) & " And "
End If
Next

If strSQL <> "" Then
strSQL = Left(strSQL, (Len(strSQL) - 5))
Reports![RPT-PNDynamic].Filter = strSQL
Reports![RPT-PNDynamic].FilterOn = True
End If
End Sub

The running sum and calculated fields cannot be done with a query because the report's contain change dynamically without reload it. Besides, the report is loaded by the pop-up form so if I base the report on a query the query criteria will be valid only when the report is loaded. Then, by filter dynamically, the criteria change but the query remains the same. Unfortunately, if I want to place reference to the pop-up form's controls in the query it is not working because the query is loaded before the pop-up form is appeared. Then, I am prompting to enter manually what the query is supposed to get from the pop-up form.

Hopping again those details will help you figured out what I am trying to do.

Thank you.

----- Duane Hookom wrote: -----

I don't see in your example where the count of unique P/Ns is located. Also,
the sort looks much the same in both example lists. I have never relied on
setting the OrderBy property. You could filter a group by query so that you
could count unique P/Ns. I don't know how you are building your filter on
your main report.

--
Duane Hookom
MS Access MVP

I, in fact, don't want separate groups because it causes my sorting to go

wrong. My report's record source is the Table it self because I wanted to
create a report that open with all the records on it and than give you the
choice to filter and sort dynamically what you want on it by the pop-up form
(to give extreme flexibility). The filter/sort are done by building SQL
expression (in VB) and affect them to the report's filter/orderby
properties. Once it is filter/sort the way I want, I need to put the number
of unique record in a summery located in the report footer section.

Selling Price ...
C6WM4567-1 2004-05-17 3 100$
C6WM4567-1 2004-05-05 5 100$
C6FM7694-12 2004-05-06 2 50$

will be easily able to know how many unique P/N there are (by counting the
number of group) but than if I sort the report (dynamically) here is the
result:

Selling Price ...
C6WM4567-1 2004-05-05 5 100$
C6WM4567-1 2004-05-17 3 100$
C6FM7694-12 2004-05-06 2 50$

strong in VB but don't know the Access/SQL mechanic very well. I am sure
there is an easy way to do what I want but unfortunately we cannot put SQL
expression in a textbox's record source properties.

 

[Duane Hookom]

You could use your "where clause" to modify the SQL of a saved query.
Currentdb.QueryDefs("qgrpPNCount").SQL = "SELECT [PN] FROM tblA WHERE " &
strSQL & " GROUP BY [PN];"
Then add a text box to your report:
=DCount("*","qgrpPNCount")

--
Duane Hookom
MS Access MVP
--

Actually, maybe I express my-self wrong, I am sorry. I don't want to count

unique P/N but how many different P/N we received, which sound very similar
to me. In the last example, the sort is done by date (I forgot to mention
it). Here is what the result is suppose to look like:

P/N Date Quantity Selling Price ...
C6WM4567-1 2004-05-05 5 100$
C6FM7694-12 2004-05-06 2 50$
C6WM4567-1 2004-05-17 3 100$

Now, here is a sample of code I use to apply filter

Private Sub SetFilter_Click()
Dim strSQL As String
Dim intCounter As Integer

For intCounter = 1 To 12
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " &

" = " & Chr(34) & Me("Filter" & intCounter) & _

Chr(34) & " And "
End If
Next

If strSQL <> "" Then
strSQL = Left(strSQL, (Len(strSQL) - 5))
Reports![RPT-PNDynamic].Filter = strSQL
Reports![RPT-PNDynamic].FilterOn = True
End If
End Sub

The running sum and calculated fields cannot be done with a query because

the report's contain change dynamically without reload it. Besides, the
report is loaded by the pop-up form so if I base the report on a query the
query criteria will be valid only when the report is loaded. Then, by filter
dynamically, the criteria change but the query remains the same.
Unfortunately, if I want to place reference to the pop-up form's controls in
the query it is not working because the query is loaded before the pop-up
form is appeared. Then, I am prompting to enter manually what the query is
supposed to get from the pop-up form.

 

[Guest]

It is working perfectly, thank you very much, it even gived me some ideas

----- Duane Hookom wrote: ----

You could use your "where clause" to modify the SQL of a saved query
Currentdb.QueryDefs("qgrpPNCount").SQL = "SELECT [PN] FROM tblA WHERE "
strSQL & " GROUP BY [PN];
Then add a text box to your report
=DCount("*","qgrpPNCount"

--
Duane Hooko
MS Access MV
-

Actually, maybe I express my-self wrong, I am sorry. I don't want to coun

unique P/N but how many different P/N we received, which sound very simila
to me. In the last example, the sort is done by date (I forgot to mentio
it). Here is what the result is suppose to look like

P/N Date Quantit

Selling Price ..
C6WM4567-1 2004-05-05 100
C6FM7694-12 2004-05-06 50
C6WM4567-1 2004-05-17 100

Now, here is a sample of code I use to apply filte
Private Sub SetFilter_Click(

Dim strSQL As Strin
Dim intCounter As Intege

For intCounter = 1 To 1

If Me("Filter" & intCounter) <> "" The
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] "

" = " & Chr(34) & Me("Filter" & intCounter) &

Chr(34) & " And
End I
Nex

If strSQL <> "" The

strSQL = Left(strSQL, (Len(strSQL) - 5)
Reports![RPT-PNDynamic].Filter = strSQ
Reports![RPT-PNDynamic].FilterOn = Tru
End I
End Su

The running sum and calculated fields cannot be done with a query becaus

the report's contain change dynamically without reload it. Besides, th
report is loaded by the pop-up form so if I base the report on a query th
query criteria will be valid only when the report is loaded. Then, by filte
dynamically, the criteria change but the query remains the same
Unfortunately, if I want to place reference to the pop-up form's controls i
the query it is not working because the query is loaded before the pop-u
form is appeared. Then, I am prompting to enter manually what the query i
supposed to get from the pop-up form